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

Update: As soon as every left for the day and stopped using the program that updates these files, my program ran quickly. Note that this is the program structure I'm using:

Open tables SHARED if no one else is using them exclusively

Execute the SQL statement 500 times  <== This runs slow while others are updating the tables.

Close files

Maybe this is another reason to make a COPY of the files and use the copies.