asked on

speed up ACCESS 2003 query from ODBC connection

I have an ACCESS 2003 query where I am pulling data from a SQL table. the query runs very fast - a few seconds. I run another query off of the initial query to parse out the date from the DateTime field using DateValue function. The second query using the DateValue runs fast - a few seconds. Then I write another query to filter the date field. I want records after 12/31/2013 so in the date field the criteria is >#12/31/2013#. This query takes over 30 minutes to run. Is there a way to filter records more efficiently to return records after 12/31/2013? The file has records from 2000.

THANKS

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kelvin Sparks

If you're using ODBC to SQL Server, use a "Pass Though Query" and then the filter will happen at the server rather than passing all Records from SQL Server. You will need to write the query in SQL Server syntax = eg. SELECT * FROM mytable Where Datefield > '01-01-2013'

Kelvin

Dale Fye

Can you post the SQL of the third query? My quess is that you are using a VBA function call or a call to a user defined function that SQL Server is unable to process. Because of that, JET is forced to drag all of the information back to Access to be processed locally.

As Kelvin mentioned, a pass-through query might work, but you cannot use VBA function calls in a pass-through query, or functions that you have written either. I would be willing to bet that if you created a query to take the results of the 2nd query, and pushed it into a temporary table on your local machine (I generally prefer to put these temp tables in a temp.accdb file using a function I wrote a while back, see my article on using temp tables), and then ran the query that is taking 30 minutes against that temp table, it would run quickly.

Another alternative would be to create a stored procedure on the SQL Server, pass it the appropriate parameters, and return a recordset. Since SQL Server would be doing all of the processing, it would likely run relatively quickly.

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eyes59

ASKER

I moved the date criteria >#12/31/2013 to the datetime field and it is still running very slowly.

eyes59

ASKER

here is the SQL

SELECT qryWork_2.LogDate, qryWork_2.LogTime, qryWork_2.LogHour, qryWork_2.area, qryWork_2.fk_code, qryWork_2.recid, qryWork_2.processor, qryWork_2.fk_item
FROM qryWork_2
WHERE (((qryWork_2.LogDate)>#12/31/2013#));

eyes59

ASKER

Changelyd the criteria to be between dates still runs slow

Dale Fye

But you say that when you simply run:

SELECT * FROM qryWork_2

it returns the results in seconds?

1. What happens if you change the WHERE clause to:

WHERE cDate(qryWork_2.LogDate) > #12/31/2013#

2. Does qryWork_2 use the results of qryWork_1? If so, can you post the SQL from each of those as well?

3. Is there a reason you cannot move the WHERE LogDate > #12/31/2013# into qryWork_2?

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial