Query Via ODBC Taking long time

[Older Fella]

I have a SQL database on a server somewhere in the USA.



I am using Access as a local tool to send data to and from it as is my co-workers in other countries. Hence the need for SQL etc.

I am wanting to bring across the jobs completed by site for the last 8 years. This is not an inordinate amount of records with perhaps 250 being the maximum.

Currently I can see the query is working, but I can actually see it working as in it is filling in a line at a time and then goes back through refreshing. I recall reading somewhere this is something about the way the query engine works it is sending the query to the SQL server and then getting the data back one line at a time or something like that. The outcome is if the query is written badly it polls the SQL Server multiple times leading to very slow loading.

The only things I can think of are restricting the data to just the last 10 jobs or something. I just have a thought there is something about server side settings or something...

SELECT tblJob.Job, tblJob.Order, tblJob.Department, tblSite.ID, tblJob.[Select frmSite], tblJob.[Job Description], tblJob.[Booking Date], tblJob.Quoted, tblJob.QuoteAmount, tblJob.Office, tblJob.Invoiced, tblJob.LabourCost, tblJob.ChemicalCost, tblJob.OtherCost, tblJob.InvoiceValue, tblJob.Reference, tblJob.ReferenceContact, tblJob.ReferenceNumber
FROM tblJob LEFT JOIN tblSite ON tblJob.[Select frmSite] = tblSite.[Site Name];

I don't think this is ideal. But I am new to this type of set up and don't know how to go about optimising a query to work in this way. Can someone point me to a resource or simply show me how to optimise the query for this type of task?

[rpeare]

Why not set up a view on the SQL side then just append the contents of the view to your local table?

Views are way more efficient than trying to run a query on the Access side and they appear, to access as though they are a table and might be faster than what you're currently trying.

[CJ_London]

re optimising are the fields in your join indexed? doesn't sound like a lot of records, but might make a difference. Also are both tables in sql server? if not that can have a big impact on performance. The ODBC driver translates the query into the destination structure (same in this case) and only passes back what can't be translated (such as domain functions and UDF's, crosstabs and the like). It may just be that your connection is slow in which case the answer is to minimise the amount of data to bring back - both rows and columns. But not always possible of course