Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

speed up ACCESS 2003 query from ODBC connection

Posted on 2014-02-13
9
Medium Priority
?
828 Views
Last Modified: 2014-03-04
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
0
Comment
Question by:eyes59
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1002 total points
ID: 39857473
Apply the filter on the original field, not the expression with DateValue which kills any use of a index.

/gustav
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39857494
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39857521
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.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 39

Assisted Solution

by:PatHartman
PatHartman earned 498 total points
ID: 39857523
The only time you have to worry about extracting the date from a date/time field is if you need to use the date to join to another table.  For the where clause, you can use Between or >= and <= to get the data you need.  So if you want records with only a specific date, and the date field may contain time then:

YourDate>= VariableDate AND < VariableDate + 1
0
 

Author Comment

by:eyes59
ID: 39857524
I moved the date criteria >#12/31/2013 to the datetime field and it is still running very slowly.
0
 

Author Comment

by:eyes59
ID: 39857556
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#));
0
 

Author Comment

by:eyes59
ID: 39857607
Changelyd the criteria to be between dates still runs slow
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39857831
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?
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1002 total points
ID: 39858289
> I moved the date criteria >#12/31/2013 to the datetime field and
> it is still running very slowly.

Then you need to create an index on that field.

/gustav
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question