Performance issues with queries

[rws at csc]

I have run accross a performance issue and looking to see if another knows why it happening. First of all I am using an ODBC connection to an AS\400 system. I am quering a table with roughly 1,000,000 records. If I hard code the criteria for a particular field, the query completes in less and 5 seconds. If I set the criteria to be a field in a local table, the same query runs for over 1 and a half minutes. Any ideas.

 

[aleb]

I am not sure but this is probably depends on where you query is processed ... on the AS/400 or on your PC.
If you sending request directly to AS/400 it is processed a looooooot quicker.
What are using ? a linked table ? or pathrough query ?

 

[nad]

aleb is correct.

The query will run on the client PC and will generate lots of traffic from the remote SQL server and your PC.

When dealing with large amounts of data using ODBC linked tables you will nearly always get better performance by writing the query as a pass-thru.

Regards
NAD