August 21, 2012 at 1:42 am
Stefan Krzywicki (8/20/2012)
Brandie Tarvin (8/20/2012)
ChrisM@Work (8/17/2012)
Pet rent? Never heard of that over here.Landlords in the States are adding it to the usual rental fees as a way to discourage pet ownership among renters or to pay for the damages done by said pets during the rental period.
Or to grab more cash from people who have no other choice. I've found they tend to make up charges to keep your deposit at the end of your rental period and will do the same about "pet damage" as well.
That's all too common over here.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2012 at 1:45 am
dwain.c (8/21/2012)
It has been a rare experience for me to post a question to this forum, but I will now. How about a poll?Yesterday I was called upon to go back and look at the first SP I ever wrote (about 15 months ago). The SP is quite complicated, so much so that during its development nobody on the development team could do it right. So I wrote it from scratch, and with my very limited SQL experience at the time, I did the unspeakable and solved the problem with 2 nested CURSORs. That's right - 2 CURSORs! :w00t: In my defence I will say that this occurred before I adoped my mantra. 😀
Sometime after that, and after writing one other SP that contains a CURSOR (1 only, I swear!), I stumbled upon a statement by someone (can't remember who) that said "in your career you should write no more than 5 CURSORs." I take some solace in the fact that my total stands at 3 and I've never again had to resort to one since I came across that quotation.
So now onto my question.
If I rewrite this SP to use 1 or 0 CURSORs, will the SQL-verse acknowledge this reduction in my career CURSOR usage and lower my career total CURSOR count?
No. You're banned. :hehe:
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2012 at 1:46 am
ChrisM@Work (8/21/2012)
dwain.c (8/21/2012)
It has been a rare experience for me to post a question to this forum, but I will now. How about a poll?Yesterday I was called upon to go back and look at the first SP I ever wrote (about 15 months ago). The SP is quite complicated, so much so that during its development nobody on the development team could do it right. So I wrote it from scratch, and with my very limited SQL experience at the time, I did the unspeakable and solved the problem with 2 nested CURSORs. That's right - 2 CURSORs! :w00t: In my defence I will say that this occurred before I adoped my mantra. 😀
Sometime after that, and after writing one other SP that contains a CURSOR (1 only, I swear!), I stumbled upon a statement by someone (can't remember who) that said "in your career you should write no more than 5 CURSORs." I take some solace in the fact that my total stands at 3 and I've never again had to resort to one since I came across that quotation.
So now onto my question.
If I rewrite this SP to use 1 or 0 CURSORs, will the SQL-verse acknowledge this reduction in my career CURSOR usage and lower my career total CURSOR count?
No. You're banned. :hehe:
Tough audience.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2012 at 2:18 am
<soapbox>
Cursors are tools. Use them where they work, don't use them where they don't. Statements like 'you should never use a <whatever>' are just shortsighted
If we count while loops as cursors, I wrote about 10 since saturday
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 21, 2012 at 2:27 am
GilaMonster (8/21/2012)
<soapbox>Cursors are tools. Use them where they work, don't use them where they don't. Statements like 'you should never use a <whatever>' are just shortsighted
If we count while loops as cursors, I wrote about 10 since saturday
Rest assured that if I ever encounter a case where a CURSOR out-performs a set based solution, I'll happily use it. In this case, I believe a rewrite without inner the CURSOR will probably be faster. The logic may be too complex to eliminate the outer CURSOR.
I have written some WHILE loops but they don't count.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2012 at 2:38 am
GilaMonster (8/21/2012)
<soapbox>Cursors are tools. Use them where they work, don't use them where they don't. Statements like 'you should never use a <whatever>' are just shortsighted
If we count while loops as cursors, I wrote about 10 since saturday
A DBA is far more likely than a dev to require code using loops or cursors - there isn't a set-based equivalent to "check the fragmentation level of this index in this db: if it's above a certain threshold, then rebuild, if it's above another threshold, then reorganise, otherwise restart the process with the next index in the list of indexes in the db". I haven't had to write any sprocs using cursors other than maintenance scripts like this. Funny thing is, the few times I've tested a cursor-based algorithm against a set-based one for DML, the cursor version hasn't performed anything like as badly as one might expect - meaning, sooner or later it's going to happen. There's a lot of dogma out there.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2012 at 3:00 am
GilaMonster (8/21/2012)
<soapbox>Cursors are tools. Use them where they work, don't use them where they don't. Statements like 'you should never use a <whatever>' are just shortsighted
If we count while loops as cursors, I wrote about 10 since saturday
If this was a movie, I'd be the idiot that stands up and starts slow clapping you now 😉
The unfortunate problem is that a lot of developers that come from other backgrounds have a tendency to resort to looping when a set-based solution is far superior. This then leads a lot of senior DBAs / SQL developers to issue sweeping statements about never writing a cursor or a while loop in SQL. This has positive and negative aspects - positives are that junior SQL developers / DBAs come in and learn to think in a set-based manner. They are forbidden from writing loops and cursors so every problem they come across has to be solved with a set-based solution. The negatives are that some of these junior SQL developers, mainly the self-taught, never learn that the rule about no cursors or looping only applies until you actually know when the correct time to use them is.
August 21, 2012 at 3:12 am
dwain.c (8/21/2012)
Rest assured that if I ever encounter a case where a CURSOR out-performs a set based solution, I'll happily use it. In this case, I believe a rewrite without inner the CURSOR will probably be faster. The logic may be too complex to eliminate the outer CURSOR.
It's not always about performance; sometimes concurrency effects (for example) are more important.
I have written some WHILE loops but they don't count.
Yes they do.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 21, 2012 at 3:21 am
dwain.c (8/21/2012)
I have written some WHILE loops but they don't count.
Why not? They're just about the same as cursors just without the syntax.
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 21, 2012 at 3:43 am
SQL Kiwi (8/21/2012)
dwain.c (8/21/2012)
Rest assured that if I ever encounter a case where a CURSOR out-performs a set based solution, I'll happily use it. In this case, I believe a rewrite without inner the CURSOR will probably be faster. The logic may be too complex to eliminate the outer CURSOR.It's not always about performance; sometimes concurrency effects (for example) are more important.
I have written some WHILE loops but they don't count.
Yes they do.
GilaMonster (8/21/2012)
dwain.c (8/21/2012)
I have written some WHILE loops but they don't count.Why not? They're just about the same as cursors just without the syntax.
I meant they don't count towards my question. I do try to avoid them as well but I have found times when they're useful.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2012 at 4:00 am
SQL Kiwi (8/21/2012)
dwain.c (8/21/2012)
Rest assured that if I ever encounter a case where a CURSOR out-performs a set based solution, I'll happily use it. In this case, I believe a rewrite without inner the CURSOR will probably be faster. The logic may be too complex to eliminate the outer CURSOR.It's not always about performance; sometimes concurrency effects (for example) are more important.
Can you (or someone) expound on what you mean by "concurrency effects?"
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 21, 2012 at 4:06 am
dwain.c (8/21/2012)
Can you (or someone) expound on what you mean by "concurrency effects?"
I have encountered a number of very high read workload OLTP systems (web sites) where any changes to data were required to be made one row at a time with a short delay between each.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 21, 2012 at 4:35 am
WayneS (8/20/2012)
Hey Brandie... I noticed your LinkedIn profile has you in a new position - are you moving away from SQL, or is this additional stuff that you're piling on yourself? (To borrow from a magazine: Curious minds want to know!)
Help! I'm buried under a pile and I can't get up!!! (Where's my Life Alert when I need it?)
It's an additional part time job. I posted about it on the thread a few weeks ago but hadn't updated LinkedIn yet.
And Congrats!
Thanks. @=)
August 21, 2012 at 4:41 am
SQL Kiwi (8/21/2012)
dwain.c (8/21/2012)
Can you (or someone) expound on what you mean by "concurrency effects?"I have encountered a number of very high read workload OLTP systems (web sites) where any changes to data were required to be made one row at a time with a short delay between each.
Yikes!
At my workplace, we forbid the Devs to use cursors. It's part of our db standards. Any time they want to go against standards, they have to justify it with a well-reasoned business case. Most of the time they can't.
If they can justify it, though, we actually do bend and allow the code. It's just that we don't want Devs writing cursors because that's what they know.
I have used cursors in procs, but that's because we've automated a lot of our maintenance stuff and it's easier to update a proc than remember the zillion job / SSIS touchpoints using the code.
August 21, 2012 at 4:47 am
Tangent: Do any WordPress gurus have time to help me with my website? I'm not rich, but maybe we can come up with a payment arrangement?
I'm trying to figure out how to load my book covers and purchase details into my wordpress database and pull them out onto a page with a pretty display. But WordPress doesn't seem to have any templates that will do my blog pages the way I want and give me a page template for the book gallery.
I also can't figure out how to load the information into the MySQL database.
Please let me know if you're interested in assisting me and I'll PM you with my email so we can have a conversation. I'm still having trouble with my PM. The help desk guy who I was talking with seems reluctant to respond to my last few emails about the issue. @sigh.
Viewing 15 posts - 37,546 through 37,560 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply