MS SQL 2000 MSDE Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kountree - 08 Jun 2009 19:26 GMT

Here is my background. I am using the MSDE 2000 MS SQL. I have a table with
1,189,032 records in it. When I connect to the SQL server, and run a simple
query, select * from testRecs where startdate >= '6/7/2009' and enddate <=
'6/8/2009', runnin the execution plan, it takes approximately 42-45 seconds.
Now, if I run this again, it takes 2 seconds.

My problem is why on the initial time, does it take such a long time? Here
is what I tried.

1) Optimized the query
select t.field1, t.field2 from testRecs t where t.startdate >= '6/7/2009'
and t.enddate <= '6/8/2009'
*The results in the Execution Plan yielded the same results, 40-45 seconds.

2) Ran an index with the optimized query
* Again, The results in the Execution Plan yielded the same results, 40-45
seconds.

Thanks

Reply to this Message

Andrea Montanari - 09 Jun 2009 15:22 GMT

hi,

> Here is my background. I am using the MSDE 2000 MS SQL. I have a
> table with 1,189,032 records in it. When I connect to the SQL
[quoted text clipped - 15 lines]
> * Again, The results in the Execution Plan yielded the same results,
> 40-45 seconds.

chances are you are engaging a SQL Server related issue and an MSDE related
one..
they both are "issues" and not problems as they are related to the normal
behaviour..
the SQL Server engine related one is based on the actual cache.. the very
first time a statement/batch/procedure/.. is esecuted, the whole execution
plan must be compiled (and this usually is not a problem, in your case as
well), and the data (data pages and eventual index pages) must be fetched
from the disk... the second time you execute it the plan could be already
avaiable for use in the procedure cache, and so does the data, so that no
additional I/O operation(s) is required and the result is returned very
quickly...
the MSDE related issue is about a database setting which is usually set for
MSDE created database.., it's the "auto close" database setting, used for
this edition of SQL Server in order to automatically shut down in a clean
state unused databases (meaning unreferenced databases in the open and live
connections list) to prevent accidental damages to the db themselves..
I'd argue the very first one issue is your problem, so that warm cache (the
second time the query is executed) makes the successive execution speed up
results, but the second issue could be related as well..
regards

Signature

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz
DbaMgr2k ver 0.21.1 - DbaMgr ver 0.65.1 and further SQL Tools
http://www.hotelsole.com - http://www.hotelsolericcione.de
--------- remove DMO to reply

Reply to this Message