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...