Access ADP query query timeout (Access 2K, SQL Server 2000)

Topic

New Reply

I have an SQL Server 2K database that currently contains just over 30M records. I am attempting to run a query to aggregate a subset of this data. My problem is that the query is timing out after running for only 30 seconds and I cannot figure out how to significantly raise this value. I tried running it on the SQL Server itself and I’m running into the same issue. The SQL I’m using is:

SELECT TOP 100 PERCENT dbo.USER_NAMES.USER_FULL_NAME, dbo.INCOMING.URL, COUNT(dbo.INCOMING.URL) AS [Hit Count]
FROM dbo.INCOMING INNER JOIN
dbo.CATEGORY ON dbo.INCOMING.CATEGORY = dbo.CATEGORY.CATEGORY INNER JOIN
dbo.USER_NAMES ON dbo.INCOMING.USER_ID = dbo.USER_NAMES.USER_ID
WHERE (dbo.INCOMING.DATE_TIME >= CONVERT(DATETIME, ‘2002-01-01 00:00:00′, 102)) AND (dbo.CATEGORY.CHILD_NAME = N’SEX’)
GROUP BY dbo.INCOMING.URL, dbo.USER_NAMES.USER_FULL_NAME
HAVING (dbo.USER_NAMES.USER_FULL_NAME N’Default User’) AND (COUNT(dbo.INCOMING.URL) >= 100)

Reply | Quote

Replies

Interesting problem, Mike! The 30 second time-out may be telling; in the ADP, choose Tools => Options, and head to the Advanced tab. The OLE/DDE timeout is defaulted at 30 seconds. Assuming that solves the problem in your ADP, or at least creates another one, it doesn’t explain why running the SQL statement on the server directly, through Query Analyzer?, gives you the same issue.

30M records is a bunch. You may want to check that you have efficient indexes set on the underlying tables to help the query engine, and you might consider getting that SQL into a stored procedure. The execution plan for a stored proc is compiled and will generally yield results faster than the same SQL run as a view. Good luck!

Reply | Quote

Thank you Shane (and Patt). I raised the timeout to 300 seconds. The query completed successfully after just over 1 minute.

Is there anything I should be doing with the SQL Server with this many records in it (if you know SQL Server)? It is likely to double in size in the next year.

Reply | Quote

Adding more RAM to the box never hurts!

Without taking it too much off topic for the Access forum, if you can, archive some of those records in another SQL database dedicated to reporting. This would let you create indexes on the tables that will speed up queries; indexes of that nature might well be improper to have in a database that’s seeing 100k new records added each day, i.e. the difference between an OLTP and and OLAP database. If you can’t archive some records, see what you can do about creating/modifying indexes on the tables, or as I mentioned before, perhaps you can move that query into a stored procedure.

Finally, you might fire up SQL Query Analyzer. Paste in your SQL, and check out the execution plan. This will tell you which bits are consuming the most resources, and might point you to some trouble spots you can smooth out by reworking the query or the structure/indexes/etc. of the underlying tables.

Good luck, Mike!

Reply | Quote

Adding to Shane’s comments you may find you will have to update the queries ODBC timeout as well.
I have used Access to query Oracle databases and on numerous occasions had to increase this timeout up to 1500 (25 minutes).
Pat

Reply | Quote