June 17, 2012 at 5:26 am
Hi,
I was given a task to optimize this scalar valued UDF which was written terribly with bunch of cursors and no proper indexes on the underlying tables and was part of a select statement(bad design).
For a set of predicates the function returns about 160000 rows which used to take about 5 minutes 40 seconds to execute.
After optimization and rewriting the function(to get rid of cursors and other redundant stuff) and implementing indexes the same function executes in about 25 seconds.
Now after implementation of the indexes I found out that the original function(with cursors) executes in about 20-21 seconds.So maybe it is using the indexes which I had newly added as part of my optimization effort.BUT the page reads for the cursor function is about 62000 logical reads while my function has an approximate page reads of 5000.
So my question is , though the execution time is less why are the page reads high ?Also which function should I put on production ? The one with less execution time or the one with less page reads..
Thanks
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 17, 2012 at 2:04 pm
Hmmm, is your test environment representative of the prod environment in terms of CPU, memory and IO subsystem? I would also look hard at the CPU time (SET STATISTICS TIME ON). From what you said I would venture a guess that the version that uses cursors uses a ton more CPU that the newer version. Depending on the size of data the UDF needs and the amount of memory you have it's possible all the data fits in the buffer pool so your reads will all be from memory for the duration of the UDF call, once its loaded initially of course. However, the difference in the number of reads plus the fact that one uses cursors and one does not could be enough to compel me to move on from the older version, despite the slight difference in processing times. It would also depend on how often the object is called, many times per second? A few times per day?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 18, 2012 at 5:33 am
Have you looked at the execution plan for the query? That will tell you what it's doing and will help you determine if there are further tuning opportunities available for the query. You'll also know for sure if the indexes you have in place are being used and are, in fact, helpful.
"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
June 19, 2012 at 7:39 am
Sachin Nandanwar (6/17/2012)
Hi,I was given a task to optimize this scalar valued UDF which was written terribly with bunch of cursors and no proper indexes on the underlying tables and was part of a select statement(bad design).
For a set of predicates the function returns about 160000 rows which used to take about 5 minutes 40 seconds to execute.
After optimization and rewriting the function(to get rid of cursors and other redundant stuff) and implementing indexes the same function executes in about 25 seconds.
Now after implementation of the indexes I found out that the original function(with cursors) executes in about 20-21 seconds.So maybe it is using the indexes which I had newly added as part of my optimization effort.BUT the page reads for the cursor function is about 62000 logical reads while my function has an approximate page reads of 5000.
So my question is , though the execution time is less why are the page reads high ?Also which function should I put on production ? The one with less execution time or the one with less page reads..
Thanks
We cannot tell you which of these is best for your environment. LOTS of things come into play such as memory usage, latching, locking, CPU use, etc. The thing that is most important to optimize for your environment could be vastly different from someone elses.
What I will say is that almost always the BEST solution is to refactor the scalar UDF out of the equation completely. Integrate the code into the select, even if it takes an interim object to accomplish that. Sometimes this cannot be done, but that is the exception rather than the rule in my experience.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 19, 2012 at 8:08 am
Thanks for the replies.
@Grant
Yes the whole optimization effort was done based on the readings of the execution plan.I have achieved significant gain in terms of the execution time(screenshot attached).
@opc.three
I did as suggested by you.I took a copy of the testing database which in turn is a copy of prod which is a month old.I restored it on to the mirror server(for testing purposes for this query) as the mirror server has the same memory settings as prod.The test server was having only 8GB memory while mirror and prod each has available memory of about 48 GB.Then I took a copy of preprod database and restored it on the mirror server and the preprod has all those indexes added by me.
1.png contains screenshot of the original query currently running on the prod but a copy of it was tested on the mirror server restored from the test database which does not have the indexes and runs in a cursor.
2.png contains the optimized function and runs on the same mirror server but the db has all the indexes which were added by me for optimization and has a set based approach.
3.png contains the old function currently running on the prod but was tested on the same database as 2.png on the mirror server and the database were having all the indexes which were added by me.BUT the function text is using the cursor based approach and has a lot more page reads than the set based approach in ratio of about 1:12 .
As you can clearly see the clear winner in this is the cursor based approach which executes almost 15 secs faster than the set based approach but has penalty in terms of page reads.
The row count returned by 1.png is a bit less as this is a copy of prod which is a month old while the other one is a week old.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2012 at 8:20 am
TheSQLGuru (6/19/2012)
We cannot tell you which of these is best for your environment. LOTS of things come into play such as memory usage, latching, locking, CPU use, etc. The thing that is most important to optimize for your environment could be vastly different from someone elses.
What I will say is that almost always the BEST solution is to refactor the scalar UDF out of the equation completely. Integrate the code into the select, even if it takes an interim object to accomplish that. Sometimes this cannot be done, but that is the exception rather than the rule in my experience.
I absolutely agree on what all you said.
But the UDF has about 150 line of code querying 7 to 8 tables with seemingly endless sets of conditions and refactoring that for the time being seems to be out of the scope and will definitely involve a bit of schema changes.So as of now we have to be content with whats on the plate and get the best out of it.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2012 at 9:43 am
Sachin Nandanwar (6/19/2012)
Thanks for the replies.@Grant
Yes the whole optimization effort was done based on the readings of the execution plan.I have achieved significant gain in terms of the execution time(screenshot attached).
@opc.three
I did as suggested by you.I took a copy of the testing database which in turn is a copy of prod which is a month old.I restored it on to the mirror server(for testing purposes for this query) as the mirror server has the same memory settings as prod.The test server was having only 8GB memory while mirror and prod each has available memory of about 48 GB.Then I took a copy of preprod database and restored it on the mirror server and the preprod has all those indexes added by me.
1.png contains screenshot of the original query currently running on the prod but a copy of it was tested on the mirror server restored from the test database which does not have the indexes and runs in a cursor.
2.png contains the optimized function and runs on the same mirror server but the db has all the indexes which were added by me for optimization and has a set based approach.
3.png contains the old function currently running on the prod but was tested on the same database as 2.png on the mirror server and the database were having all the indexes which were added by me.BUT the function text is using the cursor based approach and has a lot more page reads than the set based approach in ratio of about 1:12 .
As you can clearly see the clear winner in this is the cursor based approach which executes almost 15 secs faster than the set based approach but has penalty in terms of page reads.
The row count returned by 1.png is a bit less as this is a copy of prod which is a month old while the other one is a week old.
Measuring performance using the query time in SSMS after returning the results from the server can prove to be problematic. See, when you return the resultset you are involving a lot of variables that can skew your results. I find it better to pipe the resultset into a temp table using a SELECT INTO. This way I remove a lot of the variables outside the server, e.g. network traffic and the performance of the workstation consuming the results at the time the query was run. If you change your query to write the results to a temp table and SET STATISTICS TIME ON before running the query I think you'll have a closer comparison between queries. When you run a query with STATISTIC TIME ON you'll see results like this in the Messages windows in SSMS:
SQL Server Execution Times:
CPU time = n ms, elapsed time = n ms.
Comparing the CPU time between queries is very telling about how much resources your query is actually using. Is the end of the story? No. But it tells you a lot about which query is more expensive. You also didn't tell us how often this UDF is used, and on what volume of data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 10:28 am
Sachin Nandanwar (6/19/2012)
TheSQLGuru (6/19/2012)
We cannot tell you which of these is best for your environment. LOTS of things come into play such as memory usage, latching, locking, CPU use, etc. The thing that is most important to optimize for your environment could be vastly different from someone elses.
What I will say is that almost always the BEST solution is to refactor the scalar UDF out of the equation completely. Integrate the code into the select, even if it takes an interim object to accomplish that. Sometimes this cannot be done, but that is the exception rather than the rule in my experience.
I absolutely agree on what all you said.
But the UDF has about 150 line of code querying 7 to 8 tables with seemingly endless sets of conditions and refactoring that for the time being seems to be out of the scope and will definitely involve a bit of schema changes.So as of now we have to be content with whats on the plate and get the best out of it.
Roger that. I feel your pain! :w00t:
BTW, how does CPU compare between the two? Do you have issues with CPU usage being too high (spinning those 12X extra logical reads through CPUs could be painful)? If you don't, I would "usually" pick duration in a scenario such as yours all other things being equal. That if nothing else can a) make the user feel better about responsiveness and b) hopefully reduce any latch/lock duration issues. Even the 4-5 seconds diff here (20% reduction or so) can sometimes be important.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 19, 2012 at 8:28 pm
Just a recommendation. I've found that using SET STATISTICS when a scalar or multi-line table valued UDF is involved will cause the UDF to look much worse than it actually is. Setup a proper profiler trace if you want to see closer to the truth. Inline Table Valued Functions aren't so affected.
Sachin Nandanwar (6/17/2012)
Hi,I was given a task to optimize this scalar valued UDF which was written terribly with bunch of cursors and no proper indexes on the underlying tables and was part of a select statement(bad design).
For a set of predicates the function returns about 160000 rows which used to take about 5 minutes 40 seconds to execute.
Also, I'm sure it was a typo... a "scalar" UDF cannot return 160,000 rows. Perhaps you were talking about a multi-line table valued function.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 19, 2012 at 8:52 pm
You bring up a good point Jeff: if you are profiling UDF-using queries, make sure SET STATISTICS IO is OFF as well as SHOW ACTUAL EXECUTION PLAN to avoid seeing higher activity in profiler.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 20, 2012 at 5:04 am
Jeff Moden (6/19/2012)
Just a recommendation. I've found that using SET STATISTICS when a scalar or multi-line table valued UDF is involved will cause the UDF to look much worse than it actually is.
Just you saying it is enough to give me pause. I, like I am sure a lot of other people do to, use STATISTICS TIME to compare performance when tuning to compare a before and after version of something. Is it fair to say that in a tuning scenario, like this one, that any skewed results would be a wash since they'd be skewed across both (or all) versions of the UDF? Any references you can provide detailing the issue would be most appreciated.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 21, 2012 at 10:19 pm
Jeff Moden (6/19/2012)
Also, I'm sure it was a typo... a "scalar" UDF cannot return 160,000 rows. Perhaps you were talking about a multi-line table valued function.
Sorry it was a something else what I meant.The function is a scalar UDF.
What I actually meant was the execution time of the scalar UDF against a table having 160,000 rows.Something like this
Select dbo.MyUDF(table.column-name) from table
where the row count of the table is 160,000 rows.
If you see the screenshot below the UDF gets executed against the qualifying rows of the table of about 353238.
I will get back with my findings on the suggestions by you guys as for the time being I have been assigned some other much more important task than this one.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 22, 2012 at 6:51 am
opc.three (6/20/2012)
Jeff Moden (6/19/2012)
Just a recommendation. I've found that using SET STATISTICS when a scalar or multi-line table valued UDF is involved will cause the UDF to look much worse than it actually is.Just you saying it is enough to give me pause. I, like I am sure a lot of other people do to, use STATISTICS TIME to compare performance when tuning to compare a before and after version of something. Is it fair to say that in a tuning scenario, like this one, that any skewed results would be a wash since they'd be skewed across both (or all) versions of the UDF? Any references you can provide detailing the issue would be most appreciated.
The way I found it was quite by accident so I don't have any references on the subject. I was testing some code for performance. The first thing that I usually do is just make a note of the run time in the lower right hand corner of SSMS just to get a "feel" for the code. When I want to make demonstrable code, I add the SET STATISTICS TIME to the code and rerun. I was shocked when something that took just a couple of seconds on a million row run suddenly took minutes. I knew I didn't change anything except the SET STATISTICS so I commented all of that out and the time dropped back to seconds.
Detection of this problem is obviously pretty simple but only if you're aware that the problem can exist. Run the code without SET STATISTICS and then with. If the duration isn't the same, then the problem exists and you'll have to use SQL Profiler to get true measure.
And, no... it doesn't skew everything. I haven't taken the time to figure out everything that it skews but I do know that it doesn't skew everything because the "by accident" discovery was against two pieces of code. One was affected and the other wasn't. The one that was affected had a scalar UDF in it, the other didn't. When I included a different sUDF in the faster code, the same symptom appeared. It was fast without SET STATISTICS TIME ON and much slower with it. It's a bit like having the Actual Execution Plan turned on while trying to measure performance but worse.
And, no... it doesn't skew things everytime there's an sUDF or mUDF in the code (or at least, it doesn't appear so). I also haven't taken the time to figure out what the triggering mechanism is.
I'm not sure that I can find the exact code that caused this revelation but it's worth looking for or trying to recreate.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2012 at 8:34 am
Jeff Moden (6/22/2012)
opc.three (6/20/2012)
Jeff Moden (6/19/2012)
Just a recommendation. I've found that using SET STATISTICS when a scalar or multi-line table valued UDF is involved will cause the UDF to look much worse than it actually is.Just you saying it is enough to give me pause. I, like I am sure a lot of other people do to, use STATISTICS TIME to compare performance when tuning to compare a before and after version of something. Is it fair to say that in a tuning scenario, like this one, that any skewed results would be a wash since they'd be skewed across both (or all) versions of the UDF? Any references you can provide detailing the issue would be most appreciated.
The way I found it was quite by accident so I don't have any references on the subject. I was testing some code for performance. The first thing that I usually do is just make a note of the run time in the lower right hand corner of SSMS just to get a "feel" for the code. When I want to make demonstrable code, I add the SET STATISTICS TIME to the code and rerun. I was shocked when something that took just a couple of seconds on a million row run suddenly took minutes. I knew I didn't change anything except the SET STATISTICS so I commented all of that out and the time dropped back to seconds.
Detection of this problem is obviously pretty simple but only if you're aware that the problem can exist. Run the code without SET STATISTICS and then with. If the duration isn't the same, then the problem exists and you'll have to use SQL Profiler to get true measure.
And, no... it doesn't skew everything. I haven't taken the time to figure out everything that it skews but I do know that it doesn't skew everything because the "by accident" discovery was against two pieces of code. One was affected and the other wasn't. The one that was affected had a scalar UDF in it, the other didn't. When I included a different sUDF in the faster code, the same symptom appeared. It was fast without SET STATISTICS TIME ON and much slower with it. It's a bit like having the Actual Execution Plan turned on while trying to measure performance but worse.
And, no... it doesn't skew things everytime there's an sUDF or mUDF in the code (or at least, it doesn't appear so). I also haven't taken the time to figure out what the triggering mechanism is.
I'm not sure that I can find the exact code that caused this revelation but it's worth looking for or trying to recreate.
Thanks for the reply, Jeff. I will certainly keep this scenario in mind in the future when testing. I cannot say I have ever run into the problem you described, but knowing a little bit about how the QO processes sUDFs I could certainly imagine how the latent issue you described could surface in some scenarios.
In thinking about this topic here and there since your initial post I wondered if you were referring to a single call to a UDF or as part of a query where the UDF wrapped a column. Have you experienced the problem when running a UDF with STATISTICS TIME ON for a single input, i.e. when not wrapping a column in a query as in SELECT dbo.MyScalarUDF('Input1', 'Input2', 'etc.');, or have you only encountered it when wrapping a column? For the specific scenario the OP is facing I wonder if it's worth recommending he isolate the UDF call for one set of inputs for purposes of comparing performance across the two versions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply