Hi Nadya,
or should I say ....
Howdy, all y'all

I want to thank you and all the rest for your wonderful insights!

Here's what I ended up with

SELECT	Orderitems.order_id, ;
	Orders.order_date, ;
	Orderitems.closed, ;
	Orders.ofaxclnum, ;
<B>	Orders.last_name-(", "+Orders.first_name) AS subject, ;</B>
	Orderitems.report_nam, ;
	Orders.order_po, ;
	Supplier.code, ;
	Orderitems.descriptio, ;
	Orderitems.city, ;
	Orderitems.state, ;
	Orderitems.county, ;
	Orderitems.reply, ;
	Orderitems.summary, ;
	Orderitems.status, ;
	Orderitems.user_id, ;
	Orderitems.report_num, ;
<B>	PADL(ALLTRIM(Orderitems.report_num),10,"0") AS rpt_num ;</B>
  FROM  ofax!orders INNER JOIN ofax!orderitems ;
	INNER JOIN ofax!supplier ;
		ON  Orderitems.sup_id = Supplier.sup_id ;
	   ON  Orders.order_id = Orderitems.order_id

You'll notice the "-" used for the concatenation
and "10" instead of the LEN()
and the removal of the "ORDER BY", which it turned out I didn't need. :-)

My time is now down to just under a minute, and true to American culture, that's good enough.....not for me personally, but the user said it's good enough and that's what counts.

I also tested the removal of the Deleted tag from the indexes of the tables but the difference was not significant....probably network traffic.

And FWIW I don't use filters in my indexes....never liked that idea.

As for the question
>>Is that the amount of time for the SELECT statement the view is based on, or the amount of time it takes for the form/grid to display the results? I ask because i had a view that took 0.02 seconds to load/refresh, but it took 8-10 seconds for the grid to be redisplayed due to the .dynamicBackColor settings.

I started out timing the time to a usable interface, and ended up timing the select statement execution....I was losing maybe 2-5 seconds for the interface...I think.

Yes, the biggest performance killer was the ORDER BY clause....by far! I'm glad I realized I don't need it.

Rick

------------------------------------

>Hi Michael,
>
>First of all, why are you asking me? :) I'm not the originator of this thread. The originator is Richard Liebespach, his name is displayed in bold.
>
>Secondly, you're right about Is len(OrderItems.report_num) being calculated for every record? Maybe you could assign a variable before the select and use the variable instead - it might not have a noticeable affect though
>Ed Rauh already proposed modified version of this SQL, which uses chrtran(exp,' ','0') instead. He also suggested to use Lastname-(', '+FirstName) && BTW, what if firstname='', may be ',' should be conditional, IOW,
>lastname - (iif(''=FirstName,'',', ')+FirstName).
>
>Also it's a known fact (from Help), if indexes have a filter, they are not used in Rushmore optimization.
>
>
>>>>>This view is currently taking about 2.5 minutes to load.
>>>>>Can anyone help me speed up this select/view?
>>
>>Is that the amount of time for the SELECT statement the view is based on, or the amount of time it takes for the form/grid to display the results? I ask because i had a view that took 0.02 seconds to load/refresh, but it took 8-10 seconds for the grid to be redisplayed due to the .dynamicBackColor settings.
>>
>>>>>It's composed of 3 tables:
>>>>> Orders (9851 reocrds)
>>>>> OrderItems (31385 records)
>>>>> Supplier (10 records)
>>>>>
>>
>>These aren't especially large tables. Have you compared your selection with the speed of a simple select (Select * from OrderItems Order by report_num)
>>If you have a slow network the times you are seeing should be consistent for similarly sized data requests. (you can't do much coding to work around this problem)
>>
>>>>>Orders has an index on Order_id
>>>>>OrderItems has an index on Order_id and an index on Sup_id
>>>>>Supplier has an index on Sup_id
>>
>>do these indexes have filters? [index on Order_ID FOR NOT DELETED() ]
>>I don't know exactly what impact that might have, but i thought i'd ask.
>>
>>>>>SELECT Orderitems.order_id, ;
>>Orders.order_date, Orderitems.closed, Orders.ofaxclnum, ;
>>ALLTRIM(Orders.last_name)+", "+ALLTRIM(Orders.first_name) AS subject, ;
>>Orderitems.report_nam, Orders.order_po, Supplier.code, ;
>>Orderitems.descriptio, Orderitems.city, Orderitems.state, ;
>>Orderitems.county, Orderitems.reply, Orderitems.summary, ;
>>Orderitems.status, Orderitems.user_id, Orderitems.report_num, ;
>>PADL(ALLTRIM(Orderitems.report_num),LEN(Orderitems.report_num),"0") AS rpt_num ;
>>FROM ofax!orders INNER JOIN ofax!orderitems INNER JOIN ofax!supplier ;
>>ON Orderitems.sup_id=Supplier.sup_id ON Orders.order_id=orderitems.order_id ;
>>ORDER BY Orders.ofaxclnum, 18, Orderitems.report_num
>>
>>Is len(OrderItems.report_num) being calculated for every record? Maybe you could assign a variable before the select and use the variable instead - it might not have a noticeable affect though.
>>
>>Probably the biggest performance killer is the ORDER BY clause. I can't offer much help, since you probably need it as a display order in the view...