December 27, 2011 at 4:27 am
Just thought I'd add this as a warning to anyone adding indexes or writing procs.
What happened was a proc had a function on a table column in the WHERE clause.
When running without the index, a table scan was very quick in applying the function to the entire column.
With the index, timeouts occurred in the application due to an index scan per element passed into the function.
The solution was to build the proc to include the function query directly (as a subquery) and then rewrite it as a join.
Since SQL is far better at bulk processing than unitary processing, the original 8 second query now ran in 0.9seconds.
This now allowed me to add the index that was desperately needed by about 25 other procs.
My guess is that SQL did not take into account the impact of the function since they are always zero cost and the function cost actually rose when the index was added.
December 27, 2011 at 4:56 am
You might be using Scalar Function. It behaves like cursor & degrades performance. So what you have done (adding function query in procedure) is right approach. We can suggest even better if you post your procedure script, DDLs, sample data & execution plan of the procedure.
However the thread title is slight misleading. The faced performance issues because of scalar function, not because of Clustered Index. A well defined Clustered Index increases the performance.
December 27, 2011 at 5:32 am
Index added was non clustered index. Table was already clustered on auto increment ID as int.
The function in the WHERE clause was returning an integer based on if the record was not found in another table for that column value.
optimiser was at first doing a clustered index scan/table scan.
It then did a nonclustered index scan after that index was added.
12 million logical reads when the proc was executed. Can't recall the IO before.
Apologies for misleading title. Not sure what would have been a better title.
December 27, 2011 at 8:37 am
I've seen this before.
The index was better for the rest of the query, and since SQL Server can't estimate into a scalar UDF, it decided to use that index. Using better code (as you did) or using a query hint (very inferior option) are the solutions.
It's one of those things that can seriously perplex many devs, where you actually have to know a bit about how execution plans get built in order to figure it out.
Edit: Actually, "can't estimate into a scalar UDF" should be replaced with "can't estimate into scalar or multi-table UDFs". The only UDFs SQL Server can work out reasonably correctly are inline table valued UDFs. Scalar UDFs that perform a simple, isolated string or math function on an input, and which don't access any data (tables) work okay, because they don't need to be estimated in the more complex fashions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 28, 2011 at 8:34 am
Get a copy of the SQL Server MVP Deep Dives 2 book and read my chapter titled "Death by UDF" for some details on how devastatingly bad UDFs can be!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 28, 2011 at 9:20 am
January 3, 2012 at 7:28 am
I suspect there are a bunch of tuning opportunities in the query, including the possibility of adding indexes. Knowing nothing else about the situation the one thing I'd look at as in indicator of where you are is the SELECT properties to see if the optimizer completed it's processes or if it timed out. If it timed out, you're dealing with basically a junk plan, so all bets are off if your indexes are good, bad, or indifferent.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply