Query Speed

Results 1 to 7 of 7

Thread: Query Speed

  1. #1
    Steve Cimino Guest

    Default Query Speed

    I have to ask this question in here, since it seems my DBA is a bit cranky today (I know, hard to believe).<BR><BR>I have a query that searches through around 250,000 records. I&#039m using a stored procedure, and the field being searched is indexed.<BR><BR>Now, for some reason, if you&#039re the first one of the day doing this search, the query takes around 25 seconds to execute. Subsequent searches are around 3 seconds. It seems that the server is "asleep", and needs to wake up to do the first query.<BR><BR>I think the query resides in the cache after the inital search. Is this true? Does anybody know of a way to keep the speed the same throughout the day?

  2. #2
    Markkk Guest

    Default RE: Query Speed

    The first query takes 25 seconds to run. All subsequent queries take only 2 seconds.<BR><BR>It sound likes your first query needs additional time to build a database connection. All your subsequent queries may be acting on an already established (pooled, or other) database connection. The subsequent queries may not be bothered with the overhead of creating a database connection; one is already established!<BR><BR>When you destroy your database connection object in your ASP Script, a database connection is not necessari;ly destroyed by MTS. MTS may pool the connection, or keep it active or idle for a pre-set number of minutes.<BR><BR>What do you think?

  3. #3
    Steve Cimino Guest

    Default RE: Query Speed

    It&#039s not just happening from the same machine. User X&#039s first query takes 25 seconds to execute. User Y comes along 10 minutes later from a different machine and his same exact query takes 3 seconds.<BR><BR>I&#039m not running it in MTS -- just a straight stored procedure call from an ASP page.<BR><BR>Are you suggesting that the connection thread is being held in the cache by ASP, which is why subsequent queries -- regardless of user/machine -- is faster?

  4. #4
    Markkk Guest

    Default RE: Query Speed

    Yes (at least I think so).<BR><BR>Exactly, how are you connecting to the database (ODBC Driver? OLE DB Provider?)<BR><BR>Many OLE DB Providers (and some ODBC Drivers) integrate nicely with the pooling and threading features of MTS (others do not). It is quite possible that once ASP makes the initial database connection, all subsequent database queries are performed through the same connection, regardless of the web client (client machine).<BR><BR>Depending on the ODBC Driver or OLE DB Provider you are using, and on the MTS settings, MTS can keep a database connection open/idle in reserve for the next anticipated ASP request, even though your ASP script specifically set the connection object to NOTHING.<BR><BR>Briefly, show me your ASP connection script. Also, tell me exactly which version of ODBC Driver or OLE Provider you are using to connect to the database.<BR><BR>You can monitor the database connections made by MTS. You shoulkd be able to determine if a database connection is kept in reserve to support future rtequests. In MTS somewhere (perhaps in the ODBC Driver itelf), a setting exists to tell MTS how long to keep a database connection idle. Usually, when this pre-determined time period elapses, MTS will then destroy the connection.<BR><BR>We might be barking up the wrong tree....however, my gut feelings tell me that it is a database connection/pooling/threading issue that is causing your second, third, fourth, etc. queries to run faster than your first query.

  5. #5
    Garth Guest

    Default RE: Query Speed

    Good question. SQL Server accesses *all* data via cache. If the needed data (actually data page) is not in cache when the request is made, SQL Server must get it from the hard disk. After you reboot your server you will see that initial data access is slow, but subsequent requests are fast. In your case, however, it sounds like the data pages are simply being removed from cache.<BR><BR>SQL Server uses an algorithm to determine when a data page is removed from cache. It&#039s based on the cost of the query and blah, blah, blah...boring details go here. Adding more memory to the server is a good way to help with this type of problem. You can also schedule a stored procedure to do a SELECT * on the table periodically, but I would be VERY hesitant to use this approach if this is a system that has a lot of users.<BR><BR>Garth<BR>www.SQLBook.com

  6. #6
    Steve Cimino Guest

    Default Problem solved

    I have two servers that are included in this query.. the web server and the server that SQL resides on. Thing is, I turned off the "sleep after x minutes" (powersave) on the web server, but not the SQL server. I guess what was happening was that the extra time was the hard disk having to wake up.<BR><BR>Although I have learned a few things here about SQL Server that I didn&#039t know before.<BR><BR>Thanks Mark and Garth!

  7. #7
    Steve Cimino Guest

    Default RE: Query Speed

    What I found out (answered below) was that the query would take that long AGAIN after about 30 minutes. Just seems that the NT Server SQL lived on had to be resurrected after an elapsed powersave mode.<BR><BR>Based on what you said, does ASP always make a connection to MTS? I stated before I&#039m not using any components in MTS, but does IIS inherintely use MTS?<BR><BR>Thanks again for your help.

Posting Permissions

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