November 10, 2011 at 8:18 am
Tx again for all your support.
November 10, 2011 at 8:19 am
Ninja's_RGR'us (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...I'm sure we've all had days like that...
I inherited a query like that back in 99. It was a 500 line query with lots of unions. I got it down to a 50 line query with no unions. It ran better too. 🙂
I think this is my worst one here atm.
39 pages in word, 6688 words!
This is for a SINGLE report for a SINGLE user! 😛
Oh and there's 2 version of the proc :blink:.
Childs play. We have 10,000 line procs and they are a bugger to debug.
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
November 10, 2011 at 8:20 am
Brandie Tarvin (11/10/2011)
Ninja's_RGR'us (11/10/2011)
Got another puzzle for you guys and possibly another bug (row_number, partition by & cte).http://www.sqlservercentral.com/Forums/Topic1203532-266-1.aspx
Gave it an initial shot. Want to see what your answer is before I delve further into it.
Right now I want to know if this is a bug or not and if it's been reported & fixed in 2k5 SP4 and up.
Before wasting another 2-3 hours on repro and connect and wanted to survey the crowd as google hasn't returned anything directly related to that case.
I'm able to move forward, but I'd like to get the bug fixed if it's really a problem.
November 10, 2011 at 8:21 am
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...I'm sure we've all had days like that...
I inherited a query like that back in 99. It was a 500 line query with lots of unions. I got it down to a 50 line query with no unions. It ran better too. 🙂
I'm trying to make some time to go after a rather nasty trigger we've got here with a recursive CTE in the middle of it. Ugly, ugly, ugly. I'm still reverse-engineering the business logic, though - I'm not going to touch it until I actually understand what it's trying to accomplish. But it sits out there, taunting me, and keeps making it closer and closer to the top of the "must tackle now" list...
The only ones I hate more than the kind where no-one seems to know what it does, why it does it or exactly how are the ones that turn out to do nothing useful or nothing at all once you've figured out what it does.
How about the ones with self joins joining to the same field on both sides of the join? No additional data, just using up an extra table scan because performance wasn't bad enough.
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
November 10, 2011 at 8:23 am
Ninja's_RGR'us (11/10/2011)
Brandie Tarvin (11/10/2011)
Ninja's_RGR'us (11/10/2011)
Got another puzzle for you guys and possibly another bug (row_number, partition by & cte).http://www.sqlservercentral.com/Forums/Topic1203532-266-1.aspx
Gave it an initial shot. Want to see what your answer is before I delve further into it.
Right now I want to know if this is a bug or not and if it's been reported & fixed in 2k5 SP4 and up.
I don't use partitioning in CTEs so don't recognize it as an issue. I've done Row_number, but haven't seen any issue. Now you have me curious, though.
November 10, 2011 at 8:26 am
SQLRNNR (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...I'm sure we've all had days like that...
I inherited a query like that back in 99. It was a 500 line query with lots of unions. I got it down to a 50 line query with no unions. It ran better too. 🙂
I'm trying to make some time to go after a rather nasty trigger we've got here with a recursive CTE in the middle of it. Ugly, ugly, ugly. I'm still reverse-engineering the business logic, though - I'm not going to touch it until I actually understand what it's trying to accomplish. But it sits out there, taunting me, and keeps making it closer and closer to the top of the "must tackle now" list...
The only ones I hate more than the kind where no-one seems to know what it does, why it does it or exactly how are the ones that turn out to do nothing useful or nothing at all once you've figured out what it does.
How about the ones with self joins joining to the same field on both sides of the join? No additional data, just using up an extra table scan because performance wasn't bad enough.
How about queries with joins to tables that aren't used anywhere in the query. As if the dev changed track mid-stream and forgot to clean up afterwards? (I admit to being guilty for this one on the rare occasion.) :blush:
November 10, 2011 at 8:27 am
Ninja's_RGR'us (11/10/2011)
Father in hospital, house flooded. Need some R&R atm.
Best wishes and prayers for a speedy and good outcome.
November 10, 2011 at 8:31 am
Chad Crawford (11/10/2011)
Ninja's_RGR'us (11/10/2011)
Father in hospital, house flooded. Need some R&R atm.Best wishes and prayers for a speedy and good outcome.
tx.
November 10, 2011 at 8:32 am
SQLRNNR (11/10/2011)
Ninja's_RGR'us (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...I'm sure we've all had days like that...
I inherited a query like that back in 99. It was a 500 line query with lots of unions. I got it down to a 50 line query with no unions. It ran better too. 🙂
I think this is my worst one here atm.
39 pages in word, 6688 words!
This is for a SINGLE report for a SINGLE user! 😛
Oh and there's 2 version of the proc :blink:.
Childs play. We have 10,000 line procs and they are a bugger to debug.
I went looking for one that I'm rewriting.
2700 lines, 57 pages (Word), 13 temp tables.
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
November 10, 2011 at 8:32 am
Ninja's_RGR'us (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...I'm sure we've all had days like that...
Yup noone WANTS to help... if they could. [...]
I do! For some odd reason I quite enjoy working on stuff like that. I don't have a perfect record, but it's a great feeling to attack something like that, make it smaller and (hopefully) run faster. Granted, I don't have the time to tackle many that are not directly job related (would love to help here more than I do), but when I have the chance I quite enjoy it. They've started using Entity for a new project here, and I think there will be a couple queries similar to this one headed my way soon. Luckily they appear to be pretty straightforward UNIONs and derived tables, so it's likely to be a quick win!
November 10, 2011 at 8:38 am
Chad Crawford (11/10/2011)
...They've started using Entity for a new project here...
My condolences.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2011 at 9:18 am
ChrisM@Work (11/10/2011)
Kiara (11/10/2011)
Stefan Krzywicki (11/10/2011)
Kiara (11/10/2011)
In case y'all haven't seen this.. http://thedailywtf.com/Articles/The-Query-of-Despair.aspx...I'm sure we've all had days like that...
I inherited a query like that back in 99. It was a 500 line query with lots of unions. I got it down to a 50 line query with no unions. It ran better too. 🙂
I'm trying to make some time to go after a rather nasty trigger we've got here with a recursive CTE in the middle of it. Ugly, ugly, ugly. I'm still reverse-engineering the business logic, though - I'm not going to touch it until I actually understand what it's trying to accomplish. But it sits out there, taunting me, and keeps making it closer and closer to the top of the "must tackle now" list...
Hi Ki
How about posting up the rCTE bit?
Chris, after that last mess I posted (in which I expertly demonstrated what happens when you get buried too deeply into the middle of a problem and forget to come up for air) you want to play with more? 😀
Once I figure out what it's trying to accomplish, if you really are that bored, I will, but I haven't started playing with it yet, so I can't build a postable version of it yet in my sandbox.
-Ki
November 10, 2011 at 9:22 am
Ninja's_RGR'us (11/10/2011)
We're waiting on an OR & date. He's on top of the list. He needs an open heart surgury to fix 5 different problems. That's 10 hours on the table with the heart stopped when everything goes according to plan.Let's just say nobody's thrilled about that at the moment.
Dang Remi, that's really rotten. Praying for your dad and your family...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2011 at 9:24 am
WayneS (11/10/2011)
Ninja's_RGR'us (11/10/2011)
We're waiting on an OR & date. He's on top of the list. He needs an open heart surgury to fix 5 different problems. That's 10 hours on the table with the heart stopped when everything goes according to plan.Let's just say nobody's thrilled about that at the moment.
Dang Remi, that's really rotten. Praying for your dad and your family...
tx.
He always said he was killing himself at work. 18 months into retirements, looks like what he didn't want is coming true!
November 10, 2011 at 9:35 am
Koen Verbeeck (11/8/2011)
Steve Jones - SSC Editor (11/8/2011)
I'm sure you'll pass me. I am down to about 150 posts a month, too busy with other stuff and most questions are being handled.Was this you as well?
If it was, then your point total would have been 10,000 higher.
No, those are automated ones.
Viewing 15 posts - 31,606 through 31,620 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply