Performance difference on same db''s is huge
-
Hi all,
We have 2 databases on the same server. One is production, the other is for development. They have identical tables, stored procedures and functions. There are slight differences in the amount of data in each, but nothing drastic.
There was this one stored procedure that was running slowly on production, but really fast on test. The sp was not very complex, just a select and a function call:
ALTER procedure [dbo].[getTablePrefs]
@resourceHash uniqueIdentifier
as
select enabled, ',' as c
from reqTablePref
where person_id = [dbo].[getRegisIDFromHash](@resourceHash)
ORDER BY columnNum
FOR XML AUTO, ELEMENTS
The slow down appeared to be the function call. If we substituted that with a subquery, it ran almost instantly. With the function, up to 19 seconds. When I tested the function independently it ran instantly. And like I said, in the test database on the same server the sp ran instantly with no problems.
There was only one difference between the two dbs. The table reqTablePref didn't have a primary key set in production. I fixed that, but it made no difference to the execution of the sp.
Right now we have changed the sp in our production db to use a sub-query. Anyone got any ideas on what we are missing?
Thanks!
John
-
Update statistics reqTablePref WITH FULLSCAN,ALL.
Then recompile your stored procedure
Good luck!
* Noel -
No joy
Still takes 16-18 seconds to execute.
3 of us have looked at this db just to make sure we haven't missed anything. It makes no sense that the function will run instantly on it's own, that the stored procedure will run instantly using a sub-query, but will take so long when put together.
Thanks again for any ideas,
John
fyi, here is the sp with the sub-query:
ALTER procedure [dbo].[getTablePrefs]
@resourceHash uniqueIdentifier
as
select enabled, ',' as c
from reqTablePref
--where person_id = [dbo].[getRegisIDFromHash](@resourceHash)
where person_id =
(
select person_id from person where hash = @resourceHash
)
ORDER BY columnNum
FOR XML AUTO, ELEMENTS
-
Firstly, get rid of the nested select. Functions and nested selects are inherently slow.
Rather use another way such as a join, case statement etc to suite.
This however does not explain your problem.
What are the two query plans? Identical? How about the plan of the function?
A function may run instantly when used in isolation but with a function or nested select as you have there, it is executed for every row returned from that portion of the select. So, if your first DB is doing an index seek, it will be quicker than the second doing an index scan..
Also, you mention a difference in rows. How large is the difference?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thats the issue with inline function. SQL will not be able to make estimates and assume 1 row which can make plan bad. How many rows are there in person table? My guess is - "many rows"
-
Are you getting the same query plan on both servers?
-
Didn't know functions were slow, thanks for the info!
As for differences in rows between test and production: The Person tables both have the same (128000), and the reqTablePref tables are 450 in production, 31 in test.
Is the query plan the same as the execution plan? I have never had to delve this far into SQL server before, so some things are new to me (Yet I am the closest thing we have to a dba.. Scary, huh?). I checked the actual execution plans if that's what you mean.
The execution plans on both production and test for the sp and the function are identical.
(an aside question: Does anyone know why trying to display an actual execution plan in SQL Server Management studio causes it to crash? I used enterprise manager to get these plans)
Thanks for the help!
John
EDIT: Both the production and test databases are on the same server.
-
Functions used in a select will slow a query down as the contents of the function is run for each and every row. but I said that already
Weird though that the DB's are on the same server and not performing the same.
You updated stats in the DB?
What are the execution plans? Any table or index scans?
Try removing the function and changing the query slightly.
Other than that, I'm rather lost...
As to your other question, remove the FOR XML when showing the plan. it's an annoying bug. Cannot show plan with XML results...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I updated stats on person and reqTablePref, recompiled the sp and function.
On the execution plans, they start with a clustered index scan on reqTablePref which is 78% (77% on test), then a filter 0%, sort 22% (23% on test), then a select 0%.
With the function removed and a sub query put in instead, it runs as fast as the original sp on test. When the function is put back in, it slows to a crawl. Changed the fields to be queried, no change. Deleted and recreated the sp. No change.
It's totally mystifying.
There is one thing I did notice.. I was looking at all the options on the execution plans, and when I did "Manage Statistics" I noticed that on the production db plan, there were 4 columns for reqTablePref, but on the test db plan there were only 2. Would that make a difference?
Thanks for the all the help though (and the show plan fix!).
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply