August 14, 2009 at 2:13 am
Hi,
I have a strange Stored Procedure behavior, when ever my stored procedure returns more than 8 tables in the result set, execution time raises from 220 ms to 350 ms about 1/3 performance raise. This i no matter if the table that return is empty, it's a temp table or static values (select 1 as col1, 2 as col2,... ect.) and no matter what table I remove. This behavior makes no sense to me?!?! is there a limit for good performance vs number of tables returned??? Thx in advance for your help and advice.
/Simon
August 14, 2009 at 2:37 am
Shouldn't be a hard number. Can you post the execution plans of the fast and slow queries please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2009 at 4:59 am
Thx for the quick reply, sorry I could not meet the standard 🙂
/Simon
August 14, 2009 at 5:37 am
It's not about a 'standard'. It's just that it's awfully hard to talk meaningfully about the reasons for a query's performance without having the exec plans available.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 14, 2009 at 7:08 am
By standard I was just reffering to the quick response time :), sry for the misunderstanding.
August 14, 2009 at 9:03 am
Are those user defined functions multi-statement UDF's by any chance? I'd say that's where your problem is. Multi-statement UDF's don't have statistics. When you join them against data like this, they're treated as one row tables. That's the first issue I see. Using lots of functions to join together your data is about the same level of problem as using lots of views to join the data together. As these things nest and interact, the execution plans get bad and performance degrades quite a lot.
It's also possible your statistics are out of date (although what I'm seeing could be an artifact from the UDF's) because a lot of the estimates are not in line with actuals. That's going to lead to bad choices by the optimizer.
It doesn't have anything to do with referencing 8 or 9 tables. 15-20 tables can be queried in a single select statement just fine as long as the structure of the query works with the indexes in a way that doesn't cause problems.
And all those inline functions on the columns, that's why you're getting all those constant scans with compute scalars that have to be joined to the data from the indexes & tables... All very expensive operations. Each one being run against all rows in the system, almost like having a cursor running on your query.
There may be more, but that's a good place to start.
"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
August 14, 2009 at 1:17 pm
Thx for the tips. The strange thing is my case is that the decrease in performance occurs when I add either of the following:
--
SELECT col1, col2, col3, col4 FROM dbo.Empty_Table
--
SELECT 1 AS col1, '' AS col2, NULL AS col3
--
SELECT col1, col2, col3, col4 FROM TableValued-Function()
--
A simplyfied version of my SP looks like the following:
Select col1, col2, col3, col4 FROM Table1;
Select col1, col2, col3, col4 FROM Table2;
Select col1, col2, col3, col4 FROM Table3;
Select col1, col2, col3, col4 FROM Table4;
Select col1, col2, col3, col4 FROM Table5;
Select col1, col2, col3, col4 FROM Table6;
Select col1, col2, col3, col4 FROM Table7;
Select col1, col2, col3, col4 FROM Table8;
Select col1, col2, col3, col4 FROM Table9;
Whenever a 9'th table result is applied I get the performance behavior.
August 18, 2009 at 1:11 am
Hi all
Thx for your posts. Since this is a behavior no one has encountered before it must be SP or setup specific.
If I find out anything interesting, including bad scripting 🙂 I'll let you all know.
Best regards
Simon
August 18, 2009 at 1:57 am
Im confused as how your question relates to the query plans.
The query plans ( which are identical between the fast and slow) are a single query but you say that adding another select statement causes it to run slow. In any case as you are using multiple udfs the query plan is pretty much irrelevant as it doesnt show the cost of udfs.
Please post the actual fast and slow query.
That aside to re-iterate what Grant has already stated -
If the udfs are not inline udfs then they will have a big impact on performance.
Spend some time on removing them and replacing with standard joins.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply