April 11, 2011 at 11:01 am
WayneS (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.http://www.sqlservercentral.com/articles/T-SQL/66097/
Edit: and http://www.sqlservercentral.com/articles/T-SQL/66494/
(sorry, couldn't resist...)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono! Basically this is code with tons of exceptions that has evolved over years and it works the way they want it to work right now and are operating by the "If it ain't broke" rule. Even though they've complained it is slow, even though there have been allusions to making it perform better, I'm not allowed to remove the cursors.
I figure the best I can do right now is make the cursors (and there are a lot of them, some nested!) perform as well as possible.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 11:05 am
Stefan Krzywicki (4/11/2011)
WayneS (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.http://www.sqlservercentral.com/articles/T-SQL/66097/
Edit: and http://www.sqlservercentral.com/articles/T-SQL/66494/
(sorry, couldn't resist...)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono! Basically this is code with tons of exceptions that has evolved over years and it works the way they want it to work right now and are operating by the "If it ain't broke" rule. Even though they've complained it is slow, even though there have been allusions to making it perform better, I'm not allowed to remove the cursors.
I figure the best I can do right now is make the cursors (and there are a lot of them, some nested!) perform as well as possible.
Ouch - not good.
Have you rewritten the proc using set-based methods as an aside to show them that they could get better performance with same results?
Just rewrite it as a new proc and run the two procs side by side for comparison purposes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 11, 2011 at 11:10 am
CirquedeSQLeil (4/11/2011)
Stefan Krzywicki (4/11/2011)
WayneS (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.http://www.sqlservercentral.com/articles/T-SQL/66097/
Edit: and http://www.sqlservercentral.com/articles/T-SQL/66494/
(sorry, couldn't resist...)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono! Basically this is code with tons of exceptions that has evolved over years and it works the way they want it to work right now and are operating by the "If it ain't broke" rule. Even though they've complained it is slow, even though there have been allusions to making it perform better, I'm not allowed to remove the cursors.
I figure the best I can do right now is make the cursors (and there are a lot of them, some nested!) perform as well as possible.
Ouch - not good.
Have you rewritten the proc using set-based methods as an aside to show them that they could get better performance with same results?
Just rewrite it as a new proc and run the two procs side by side for comparison purposes.
I've considered it, but it'll take some time as it is a stored procedure which calls several stored procedures which use one or more cursors each which sometimes call stored procedures that use cursors.
For the meantime I'm going to see if adding Fast_Forward or Local Static Forward_Only or Local Fast_Forward makes an appreciable difference on some of the outer cursors and then all the cursors.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 11:19 am
Stefan Krzywicki (4/11/2011)
CirquedeSQLeil (4/11/2011)
Stefan Krzywicki (4/11/2011)
WayneS (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.http://www.sqlservercentral.com/articles/T-SQL/66097/
Edit: and http://www.sqlservercentral.com/articles/T-SQL/66494/
(sorry, couldn't resist...)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono! Basically this is code with tons of exceptions that has evolved over years and it works the way they want it to work right now and are operating by the "If it ain't broke" rule. Even though they've complained it is slow, even though there have been allusions to making it perform better, I'm not allowed to remove the cursors.
I figure the best I can do right now is make the cursors (and there are a lot of them, some nested!) perform as well as possible.
Ouch - not good.
Have you rewritten the proc using set-based methods as an aside to show them that they could get better performance with same results?
Just rewrite it as a new proc and run the two procs side by side for comparison purposes.
I've considered it, but it'll take some time as it is a stored procedure which calls several stored procedures which use one or more cursors each which sometimes call stored procedures that use cursors.
For the meantime I'm going to see if adding Fast_Forward or Local Static Forward_Only or Local Fast_Forward makes an appreciable difference on some of the outer cursors and then all the cursors.
I agree with Jason - OUCH!
I believe what you're looking for is the combination of LOCAL FAST_FORWARD.
Edit: I also like Jason's suggestion. Just go ahead and write a new proc. Then show them that where the current runs in 2 hrs, yours runs in 10 seconds, and produces the same results.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 11, 2011 at 11:30 am
WayneS (4/11/2011)
Stefan Krzywicki (4/11/2011)
CirquedeSQLeil (4/11/2011)
Stefan Krzywicki (4/11/2011)
WayneS (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.http://www.sqlservercentral.com/articles/T-SQL/66097/
Edit: and http://www.sqlservercentral.com/articles/T-SQL/66494/
(sorry, couldn't resist...)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono! Basically this is code with tons of exceptions that has evolved over years and it works the way they want it to work right now and are operating by the "If it ain't broke" rule. Even though they've complained it is slow, even though there have been allusions to making it perform better, I'm not allowed to remove the cursors.
I figure the best I can do right now is make the cursors (and there are a lot of them, some nested!) perform as well as possible.
Ouch - not good.
Have you rewritten the proc using set-based methods as an aside to show them that they could get better performance with same results?
Just rewrite it as a new proc and run the two procs side by side for comparison purposes.
I've considered it, but it'll take some time as it is a stored procedure which calls several stored procedures which use one or more cursors each which sometimes call stored procedures that use cursors.
For the meantime I'm going to see if adding Fast_Forward or Local Static Forward_Only or Local Fast_Forward makes an appreciable difference on some of the outer cursors and then all the cursors.
I agree with Jason - OUCH!
I believe what you're looking for is the combination of LOCAL FAST_FORWARD.
Edit: I also like Jason's suggestion. Just go ahead and write a new proc. Then show them that where the current runs in 2 hrs, yours runs in 10 seconds, and produces the same results.
We're currently upgrading servers. I redesigned one of the databases for the new server. We've been testing for 3 months and they're not fully convinced it is ready despite producing the same results. And that's with the only modifications being to accomodate my new structures. Imagine if the process were completely different how long testing will take!
But yes, that is my long-term plan.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 11:36 am
Stefan Krzywicki (4/11/2011)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono!
Sounds familiar.
Some time back I spend 3 days optimising a particularly nasty procedure (Original developer fell in love with SUM (..) OVER in combination with DISTINCT). I rewrote it with a straightforward SUM and Group By. Logically equivalent in every way. BA heard about it and came over insisting that if I modified it I would break the code, that there was no way I could possibly change it and get it correct and that I would obviously skimp on the testing and break the critical production code.
After 30 minutes of arguing about set theory, equivalence of statements, testing requirements and development methodology I gave up and discarded the changes.
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
April 11, 2011 at 11:40 am
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono!
Sounds familiar.
Some time back I spend 3 days optimising a particularly nasty procedure (Original developer fell in love with SUM (..) OVER in combination with DISTINCT). I rewrote it with a straightforward SUM and Group By. Logically equivalent in every way. BA heard about it and came over insisting that if I modified it I would break the code, that there was no way I could possibly change it and get it correct and that I would obviously skimp on the testing and break the critical production code.
After 30 minutes of arguing about set theory, equivalence of statements, testing requirements and development methodology I gave up and discarded the changes.
Yeah, the owner of this code is at the point where if I mention changing it, she just walks away from me saying "nononononono". She'll come around, but I'll have to have it proven and tested before I show it. It is just that she went through so much pain getting it working and she doesn't want to go through that again.
Since this is on a "production" server and I have a test database where I'm doing my development, how can I do time comparisons without wiping statistics on the machine in-between tests?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 11:56 am
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono!
Sounds familiar.
Some time back I spend 3 days optimising a particularly nasty procedure (Original developer fell in love with SUM (..) OVER in combination with DISTINCT). I rewrote it with a straightforward SUM and Group By. Logically equivalent in every way. BA heard about it and came over insisting that if I modified it I would break the code, that there was no way I could possibly change it and get it correct and that I would obviously skimp on the testing and break the critical production code.
After 30 minutes of arguing about set theory, equivalence of statements, testing requirements and development methodology I gave up and discarded the changes.
Yeah, the owner of this code is at the point where if I mention changing it, she just walks away from me saying "nononononono". She'll come around, but I'll have to have it proven and tested before I show it. It is just that she went through so much pain getting it working and she doesn't want to go through that again.
Since this is on a "production" server and I have a test database where I'm doing my development, how can I do time comparisons without wiping statistics on the machine in-between tests?
Isn't there a way you could put those statistics into a table so you can keep historical info?
Or (heaven forbid I suggest this) use trace files for historical tracking purposes?
April 11, 2011 at 11:59 am
Brandie Tarvin (4/11/2011)
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
Believe me, I don't want to be using a cursor, but it is part of production code and every time I mention changing it to set based code the person who owns it begins comments with NONONonononononononono!
Sounds familiar.
Some time back I spend 3 days optimising a particularly nasty procedure (Original developer fell in love with SUM (..) OVER in combination with DISTINCT). I rewrote it with a straightforward SUM and Group By. Logically equivalent in every way. BA heard about it and came over insisting that if I modified it I would break the code, that there was no way I could possibly change it and get it correct and that I would obviously skimp on the testing and break the critical production code.
After 30 minutes of arguing about set theory, equivalence of statements, testing requirements and development methodology I gave up and discarded the changes.
Yeah, the owner of this code is at the point where if I mention changing it, she just walks away from me saying "nononononono". She'll come around, but I'll have to have it proven and tested before I show it. It is just that she went through so much pain getting it working and she doesn't want to go through that again.
Since this is on a "production" server and I have a test database where I'm doing my development, how can I do time comparisons without wiping statistics on the machine in-between tests?
Isn't there a way you could put those statistics into a table so you can keep historical info?
Or (heaven forbid I suggest this) use trace files for historical tracking purposes?
I just don't want to wipe all statistics on the machine because other queries use them to improve performance.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 12:02 pm
Stefan Krzywicki (4/11/2011)
I just don't want to wipe all statistics on the machine because other queries use them to improve performance.
Errr, what statistics are you talking about and why do you want to wipe them?
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
April 11, 2011 at 12:02 pm
Ok, had enough complaints about the lizard. Let's see if this is better.
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
April 11, 2011 at 12:15 pm
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
I just don't want to wipe all statistics on the machine because other queries use them to improve performance.Errr, what statistics are you talking about and why do you want to wipe them?
I'm likely using the wrong word. the metrics that are gathered by SQL Server to improve performance on subsequent runnings of a query. I knew the word at one point, but can't think of it now. The stored plan?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 12:15 pm
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.
People complained about the lizard?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 11, 2011 at 12:22 pm
Stefan Krzywicki (4/11/2011)
I'm likely using the wrong word. the metrics that are gathered by SQL Server to improve performance on subsequent runnings of a query. I knew the word at one point, but can't think of it now. The stored plan?
No such thing.
The 'metrics' that it gathers based on column distribution are the column/index statistics, they're stored in the system tables and are just based on the column distribution, not previous query executions.
The cached plan is based on the optimiser's estimate of the costs. It doesn't take into account previous executions, just the table structure, indexes, column/index statistics.
If you're thinking about what's in sys.dm_exec_query_stats, that's just cumulative statistics for reporting. They're not reused by SQL in any way.
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
April 11, 2011 at 12:23 pm
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.People complained about the lizard?
Yes, I have a bunch of PMs on the topic. Not all nice.
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
Viewing 15 posts - 25,561 through 25,575 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply