SQL 2000 Query Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL 2000 Query Performance

Author

Topic

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-05 : 14:03:19

I am perplexed by the performance change between the following 2 queries:

This executes in 0:00:01 (1 second)


SELECT '1/4/2011' As datestamp, text, count(text) as [count] 
FROM report 
WHERE TYPE='LAUNCH'
  AND datestamp >= '1/4/2011'
  AND datestamp < getdate() 
GROUP BY text

This executes in 0:14:54 (15 minutes)


DECLARE @data datetime
Set @data = '1/4/2011'
SELECT '1/4/2011' As datestamp, text, count(text) as [count] 
FROM report 
WHERE TYPE='LAUNCH'
  AND datestamp >= @data
  AND datestamp < getdate() 
GROUP BY text

What is causing the dramatic difference in performance and how can I speed this up?

I need to automate this as part of a larger query, but any time i calculate or try to pass the date as a variable It delays the script exponentially.

Any suggestions are appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-05 : 14:23:15

This phenomenon is known as parameter sniffing. Please search the forums here as well as googling for "parameter sniffing" as it's a widely discussed topic.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-05 : 15:00:37

Thanks tkizer...

If I understand it correctly, parameter sniffing occurs when MSSQL attempts to calculate the execution plan on a query, but doesn't take into account the actual values of the variables.

An article I read ([url]http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm[/url]) states that you can prevent this by explicitly disabling parameter sniffing by declaring the variables, which I have done here (The query I posted was exactly as I executed via the Query Analyzer. One takes 1 second consistently and the other runs for 14 - 20 minutes.

Looking at the execution plans (which I'm not that familiar with) I get the following:

15+ Min Query:


  SELECT (0%) <-- Compute Scalar (0%) <-- Stream Aggregat (1%) <-- Sort (16%) <-- report.PK_repor (84%)

1 Sec Query:


  SELECT (0%) <-- Compute Scalar (0%) <-- Stream Aggregat (0%) <-- Sort (47%) <-- Filter(0%) <-- Bookmark Lookup (26%) <-- report.IX_repor (27%)

The delay also occurs when I replace the date or variable with a the following calculation to come to yesterday's date again. (I have not checked the exec plan on this variant)


  AND datestamp >= DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE())-1,'20000101')
  AND datestamp < getdate()

Would anybody know of a good references to help explain or optimize the query, or can offer some recommendations how I could do so?

Thanks again

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-05 : 15:08:14

Is datestamp declared as datetime, smalldatetime, or varchar?

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-05 : 15:21:19

In the report table, datestamp is a datetime field.

Here's the REPORT table:


id        int
datestamp datetime
type      char(10)
text      varchar(255)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-05 : 15:23:35

One workaround I've see for this issue is this:

DECLARE @d1 datetime, @d2 datetime

SET @d1 = '01/14/11'

SET @d2 = @d1

YourQuery
WHERE datestamp >= @d2

Give that a shot and let us know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-05 : 15:45:48

I actually tried something very similar but it didn't seem to make any difference. I just executed the query as follows and it ran for 0:18:18 ...


  DECLARE @d1 datetime, @d2 datetime
  Set @d1 = '1/4/2011'
  Set @d2 = @d1

  SELECT '1/4/2011' As datestamp, text, count(text) as [count] 
  FROM report 
  WHERE TYPE='LAUNCH'
    AND datestamp >= @d2
    AND datestamp < getdate() 
  GROUP BY text

The execution plan shows the same as above:


  SELECT (0%) <-- Compute Scalar (0%) <-- Stream Aggregat (1%) <-- Sort (16%) <-- report.PK_repor (84%)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-05 : 15:47:05

What does SELECT @@VERSION show (in case there's a bug fix for 2000 that you don't have applied yet)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-05 : 16:03:02

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

It's on an old development server (probably needs to be upgraded)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-05 : 16:06:38

Yikes, 194 is RTM version. That means no service packs or hotfixes have been applied. There have been literally thousands of bug fixes and feature requests since the RTM version. Before troubleshooting this problem any further, please install SP4 and whatever the latest hotfix is (it's like two years old as 2000 isn't supported anymore). Your build number should be 2100+.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-05 : 16:12:42

I'm glad you caught that. Yes, The machine has been here for a while (read years), and I [made an ass..and so on] assumed that it was kept up to date by the individuals prior to myself. I havent had any issues with it over the past few months, so I'll have to back up the existing dbs (since not all are mine) and get the okay to update it (It shouldn't be too difficult).

Thanks again. I'll let you know if the updates fail me :) (Maybe I can talk them into a fresh copy of SQL 2008)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-05 : 16:15:37

+1,000,000 to the SQL 2008 idea.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jaypoc
Yak Posting Veteran

58 Posts

Posted - 2011-01-07 : 14:04:43

As a temporary test, I took an old workstation (P4/3Ghz/1GB) and created a new dev/test box, installed Win2k, installed MSSQL 2k, Updated both. Now running:


Microsoft SQL Server  2000 - 8.00.2055 (Intel X86)   Dec 16 2008 19:46:53   Copyright (c) 1988-2003 Microsoft Corporation  Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

(surprisingly, Windows Update did not identify the need to update SQL Server from RTM to SP4, but I did the upgrade manually.)

It still takes a lot longer to process the code below where it takes only one second when I use the actual the values.


DECLARE @d1 datetime, @d2 datetime
Set @d1 = '1/4/2011'
Set @d2 = @d1
SELECT '1/4/2011' AS datestamp, text, count(text) as [count]
FROM report
WHERE TYPE='LAUNCH'
  AND datestamp >= @d2
  AND datestamp < '1/5/2011'
GROUP BY text

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-01-07 : 14:40:43

If none of the solutions are working when searching for parameter sniffing in Google, then it's very likely you'll need to upgrade to 2005 or 2008 to get around this issue. Normally I'd recommend contacting MS for help, but 2000 is no longer even supported by MS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources