June 10, 2009 at 4:34 am
We recently upgraded from Server 2000 to 2008 and have been getting some performance issues with some update states.
The statement
DECLARE @threshold FLOAT
SET @threshold = 0.3
DECLARE @logPeriod INT
SET @logPeriod = 5
update [OPCSQL]..[Pontymoile_25_Mile_Pound.AI.History.TA3] set Quality = dbo.setQualityFlag(Quality,3,'-'),Validated=1,valid=1
where (isnull(validated, 0)=0)
and (datetime not in (select distinct top 100 percent a.[datetime]
from [OPCSQL]..[Pontymoile_25_Mile_Pound.AI.History.TA3] as a inner join [OPCSQL]..[Pontymoile_25_Mile_Pound.AI.History.TA3] as b
on a.datetime=dateadd(mi, @logPeriod, b.[datetime])
where abs(abs(a.value)-abs(b.value)) >= @threshold
and a.[datetime] >= (select min(datetime) from [OPCSQL]..[Pontymoile_25_Mile_Pound.AI.History.TA3] where isnull(validated, 0)=0)
order by a.[datetime] asc))
Execute in about 8 seconds on our 2000 instance but takes 53 seconds to perform the same job on 2008 (using a test set of data)
However replacing the @logPeriod variable in the join with the value itself resulted in the update taking 8 seconds again (on Server 2008).
Looking at the execution plans the biggest difference between the two was the update of the following index (around 40% of the execution time when using the @logPeriod variable)
CREATE NONCLUSTERED INDEX [TEST_INSERT_MISSING_INDEX] ON [dbo].[Pontymoile_25_Mile_Pound.AI.History.TA3]
(
[Validated] ASC
)
INCLUDE ( [DateTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
As the script is dynamicly generated (so it can be run on multiple tables and with differnent values for the variables) its easy to switch it to use the actual value rather than casting it as a variable first, but it would be nice if some one could tell why there is such a big difference in execution time between the two? Just for future refernce.
June 10, 2009 at 7:01 am
It sounds like parameter sniffing (do a search on that phrase for volumes of information).
But, statements like this in your WHERE clause
abs(abs(a.value)-abs(b.value)) >= @threshold
Are going to prevent index use in the query. Functions run against columns requires SQL Server to scan either the table or the index. It's going to kill your performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 10, 2009 at 7:28 am
Thanks for the reply.
I did a quick search for parameter sniffing and I don't think that its the issue as these variables are all declared locally (well they were while I was testing anyway). Will do some more reading as this is likely something I need to know about.
Also thanks for the heads up about using functions in WHERE clauses - will look into that a bit more as well. I'm pretty sure the WHERE in question could be re-written in a much better way, it took me quite a while to work out what the auther was even trying to achieve.
June 10, 2009 at 7:44 am
When you're testing, take a look at the estimated rows for the actual values and the variables. I suspect you'll see substantial differences. For the variables you'll see an average number of rows for the index used, but for the actual values you'll see something closer to the actual value you passed. It's just how the optimizer interprates things.
Another note. When trying to figure out performance issues like this, it's always good to defrag the indexes and get a full scan update of the statistics (which you get if you rebuild the indexes too). That way you eliminate as many variables as possible while you're testing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply