>>>Hi All:
>>>
>>>The following SQL takes 5 seconds to run. Is there any way to speed it up?
>>>
>>>I should mention that on my local machine it runs very quickly. The problem is when I run this on the user's network.
>>>
>>>Note that line_close is a field in the poline table.
>>>
>>>

>>>SELECT 'P' sqcType, reqrd_date sqdDate, ;
>>>	ord_qty - recv_qty sqnQty, 0000000 sqnRunning,  ;
>>>	' ' sqcIgnore ;
>>>	FROM a_poline pl ;
>>>	WHERE Item_no = tcItem ;
>>>	AND pl.ord_qty > pl.recv_qty ;
>>>	AND !line_close ;
>>>	UNION ALL ;
>>>	SELECT 'S' sqcType, sh.canc_date sqdDate, ;
>>>	qty * -1 sqnQty, 0000000 sqnRunning,  ;
>>>	' ' sqcIgnore ;
>>>	FROM a_lineitm li ;
>>>	INNER JOIN a_sohead sh ;
>>>	ON li.so_no = sh.so_no ;
>>>	WHERE Item_no = tcItem ;
>>>	AND (sh.STATUS = 'S' OR sh.STATUS = 'b') ;
>>>	ORDER BY 2, 1 ;        &&Date
>>>INTO CURSOR curRunning READWRITE
>>>

>>>
>>>Thanks,
>>>
>>>Yossi
>>
>>You can move last condition on sh status table into the JOIN, e.g.
>>ON a_sohead sh ON li.so_no = sh.so_no and sh.Status IN ('S','b')
>>
>>I don't think it'll speed it up. Do you have indexes on JOIN conditions and on item_no column?
>
>I think you are referring to Rushmore. How do I know that my code has been optimized (i.e. that the proper indexes have been set.
>
>Also, I had another idea. This SQL gets executed 500 times. So every time do we have to go across the network? Maybe it'd speed it up if I copy all 3 files to 'local' version and use those? Note that right now, my .exe file is on the same network drive as the data.

Basically every condition "Item_no = tcItem" needs a corresponding index on the table a_poline to optimize. By the way, in the query, shouldn't that be pl.Item_no? Do not add the table name or alias to the index command.

select a_poline
index on item_no tag xitem_no

Optimizing examines the indexes which are 1 - smaller than the tables and 2 - sorted and 3 use algorithms to quickly find the index entry and thereby the record.

If item_no gets the records fastest, you might not need to optimize all of the conditions. Try adding an index on item_no alone. See if that helps, if not, delete it and try another condition. Then try two conditions, then three. etc.

Why 500 times? That does mean a lot of index data is flowing across the wire. Build a table with the 500 items in it. Join with that table, and possibly get it all in one shot.