Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Optimizing a cursor based routine – Part 2 Expand / Collapse
Author
Message
Posted Monday, June 27, 2011 9:20 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Comments posted to this topic are about the item Optimizing a cursor based routine – Part 2



Clear Sky SQL
My Blog
Kent user group
Post #1132599
Posted Tuesday, June 28, 2011 9:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 17,812, Visits: 15,738
Just to nitpick something. The adventureworks sales were understated by ~ $22M, not $28M.


Nice article Dave.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1133016
Posted Tuesday, June 28, 2011 11:03 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 8:23 PM
Points: 73, Visits: 761
Thank you.

I understand the topic of optimizing a cursor based routine pretty well and yet again another day comes where I learn how much more I have to learn. I can't wait to try out some of these techniques on some more complex reports!
Post #1133090
Posted Tuesday, June 28, 2011 11:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 3,354, Visits: 2,001
Very nice article. We have very few cursors but I have learned other performance improving tips from this article regardless.
Post #1133135
Posted Tuesday, June 28, 2011 4:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
Good job Dave - very well written.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1133318
Posted Tuesday, June 28, 2011 5:02 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 4,399, Visits: 10,689
Top-notch contributions from you Dave, as usual!
Keep up the great job.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1133335
Posted Wednesday, June 29, 2011 2:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Thanks all,

Enjoyed doing it, now to find the next set of bad code to write about :)




Clear Sky SQL
My Blog
Kent user group
Post #1133501
Posted Wednesday, June 29, 2011 1:03 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:20 PM
Points: 1,945, Visits: 3,067
Dave Ballantyne (6/29/2011)
Thanks all,

Enjoyed doing it, now to find the next set of bad code to write about :)


Actually, you can go back to the DDL in this nightmare. Singular names for tables, vague names for columns, etc. Tables for the same data model split into different locations. My favor was "FROM Store.Store" -- I was waiting for a column "Store.Store.store" to complete the pain for the bastard that has to maintian this.

The idiot that did this thinks that a customer is an attribute of a store, and not in a relationship with the store. Etc. There is so little in this schema.

Basically this is an EAV and OO design mashed into SQL. You even have functions to mimic methods! Is it too much to ask that MS actually follow basic ISO standards and data modeling? Rant, rant.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1134007
Posted Wednesday, June 29, 2011 1:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Something tells me you are not a fan of AdventureWorks Joe ;)



Clear Sky SQL
My Blog
Kent user group
Post #1134030
Posted Thursday, June 30, 2011 3:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 4:23 AM
Points: 13, Visits: 66
Dave, excellent job with the post! Very informative and good example to demo the technology.

Big Question: Where can I find the AdventureWorks2008R2 database?

I was running it against the AdventureWorks database but some of the columns/tables are missing....

I have downloaded almost everything off codeplex, and I have found these databases:

1) adventureworks
2) adventureworksDW
3) adventureworksDW2008
4) adventureworksDW2008R2
5) adventureworksLT
6) adventureworksLT2008R2

But no database named "AdventureWorks2008R2"?
Post #1134752
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse