>>>>>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.
>
>Rather than making a COPY, which could fail due to others sharing the file etc. I'd just use cursors instead:
>

>SELECT * FROM NetworkTable1 INTO CURSOR Cursor1 NOFILTER
>SELECT * FROM NetworkTable2 INTO CURSOR Cursor2 NOFILTER
>* etc.
>
>* Run your SQL, based on these cursors rather than the existing network table names
>* If you're using local aliases in the SQL query you only have to change the name in the FROM clauses
>
>* Close the cursors:
>USE IN Cursor1
>USE IN Cursor2
>* etc.
>

That will pull the data from the network location once to your local temp files folder, then use that for the subsequent queries.

Thanks, Al