Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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)

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

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

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Adding more RAM to the box never hurts! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    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!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •