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 «««1234»»

Looping without cursor not updating properly Expand / Collapse
Author
Message
Posted Tuesday, March 12, 2013 7:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Gosh thanks!

A gentle reminder - always always test If this solution works, it can be condensed and tweaked for performance.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429783
Posted Tuesday, March 12, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:12 PM
Points: 13,441, Visits: 12,303
Thanks for picking this up and seeing it through Chris. I got slammed yesterday afternoon and couldn't get back to this one.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1429799
Posted Tuesday, March 12, 2013 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
No worries mate. I had the day off yesterday and picked it up at home - when you hadn't posted up by this morning it was ready to go.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429804
Posted Tuesday, March 12, 2013 7:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
ChrisM@Work (3/12/2013)
Gosh thanks!

A gentle reminder - always always test If this solution works, it can be condensed and tweaked for performance.


Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. :) I'm still poking at it and trying to be sure I understand it before I try tweaking.
Post #1429818
Posted Tuesday, March 12, 2013 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Siobhan Perricone (3/12/2013)
ChrisM@Work (3/12/2013)
Gosh thanks!

A gentle reminder - always always test If this solution works, it can be condensed and tweaked for performance.


Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. :) I'm still poking at it and trying to be sure I understand it before I try tweaking.


Proper job! A number of folks around here have a line in their sig saying something like "don't use any code from the internet without first fully understanding how it works". If in doubt, just ask. It's quite straightforward stuff.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429824
Posted Tuesday, March 12, 2013 8:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 4,356, Visits: 6,188
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1429834
Posted Tuesday, March 12, 2013 8:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.


Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429850
Posted Tuesday, March 12, 2013 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Siobhan Perricone (3/12/2013)

Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. :) I'm still poking at it and trying to be sure I understand it before I try tweaking.


Because of the random selection at the start of this process, you get different results every time the code is run. That's a real PITA because other folks can't see the same data as you and verify your assumptions. To get around it, here's a fixed set of 80 rows of "chosen ones" for folks to play with:

SELECT rn, FirewoodLottery_id, Choice1, Choice2   
INTO #TheChosenOnes
FROM (
SELECT rn = 1, FirewoodLottery_id = 100, Choice1 = 'Shrewsbury', Choice2 = 'Shrewsbury' UNION ALL
SELECT 2, 6, 'Orange', 'Groton' UNION ALL
SELECT 3, 83, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 4, 12, 'Orange', 'Groton' UNION ALL
SELECT 5, 104, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 6, 4, 'North Duxbury', 'North Duxbury' UNION ALL
SELECT 7, 49, 'Groton', 'Groton' UNION ALL
SELECT 8, 27, 'Groton', 'Orange' UNION ALL
SELECT 9, 64, 'Johnson', 'Johnson' UNION ALL
SELECT 10, 29, 'Orange', 'Groton' UNION ALL
SELECT 11, 98, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 12, 117, 'Groton', 'Orange' UNION ALL
SELECT 13, 109, 'Groton', 'Orange' UNION ALL
SELECT 14, 86, 'North Duxbury', 'Shrewsbury' UNION ALL
SELECT 15, 45, 'Orange', 'Groton' UNION ALL
SELECT 16, 94, 'Westmore', 'Westmore' UNION ALL
SELECT 17, 62, 'Orange', 'Groton' UNION ALL
SELECT 18, 32, 'Groton', 'Orange' UNION ALL
SELECT 19, 56, 'Westmore', 'Westmore' UNION ALL
SELECT 20, 26, 'Westmore', 'Groton' UNION ALL
SELECT 21, 33, 'Groton', 'Orange' UNION ALL
SELECT 22, 120, 'Westmore', 'North Duxbury' UNION ALL
SELECT 23, 9, 'Westmore', 'Westmore' UNION ALL
SELECT 24, 2, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 25, 84, 'Johnson', 'North Duxbury' UNION ALL
SELECT 26, 65, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 27, 19, 'Westmore', 'Westmore' UNION ALL
SELECT 28, 89, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 29, 76, 'Johnson', 'Groton' UNION ALL
SELECT 30, 96, 'Groton', 'Orange' UNION ALL
SELECT 31, 59, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 32, 38, 'Orange', 'Groton' UNION ALL
SELECT 33, 17, 'Groton', 'Orange' UNION ALL
SELECT 34, 79, 'Johnson', 'North Duxbury' UNION ALL
SELECT 35, 116, 'Orange', 'Orange' UNION ALL
SELECT 36, 30, 'Westmore', 'Groton' UNION ALL
SELECT 37, 77, 'Groton', 'Johnson' UNION ALL
SELECT 38, 14, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 39, 20, 'Westmore', 'Westmore' UNION ALL
SELECT 40, 106, 'North Duxbury', 'Groton' UNION ALL
SELECT 41, 103, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 42, 105, 'Orange', 'Groton' UNION ALL
SELECT 43, 31, 'Groton', 'Orange' UNION ALL
SELECT 44, 92, 'Johnson', 'Johnson' UNION ALL
SELECT 45, 69, 'Westmore', 'Westmore' UNION ALL
SELECT 46, 118, 'Orange', 'Westmore' UNION ALL
SELECT 47, 51, 'Westmore', 'Westmore' UNION ALL
SELECT 48, 18, 'Westmore', 'Westmore' UNION ALL
SELECT 49, 7, 'Orange', 'Groton' UNION ALL
SELECT 50, 54, 'Westmore', 'Westmore' UNION ALL
SELECT 51, 75, 'Orange', 'Groton' UNION ALL
SELECT 52, 88, 'Orange', 'Groton' UNION ALL
SELECT 53, 99, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 54, 78, 'Groton', 'Orange' UNION ALL
SELECT 55, 113, 'Johnson', 'Johnson' UNION ALL
SELECT 56, 1, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 57, 95, 'North Duxbury', 'Westmore' UNION ALL
SELECT 58, 46, 'Groton', 'Groton' UNION ALL
SELECT 59, 73, 'North Duxbury', 'Johnson' UNION ALL
SELECT 60, 72, 'Westmore', 'Westmore' UNION ALL
SELECT 61, 110, 'Orange', 'Groton' UNION ALL
SELECT 62, 22, 'Johnson', 'North Duxbury' UNION ALL
SELECT 63, 97, 'North Duxbury', 'Roxbury' UNION ALL
SELECT 64, 115, 'Roxbury', 'Orange' UNION ALL
SELECT 65, 16, 'Groton', 'Orange' UNION ALL
SELECT 66, 11, 'North Duxbury', 'North Duxbury' UNION ALL
SELECT 67, 114, 'Westmore', 'Westmore' UNION ALL
SELECT 68, 58, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 69, 34, 'Westmore', 'Groton' UNION ALL
SELECT 70, 63, 'Johnson', 'Plymouth' UNION ALL
SELECT 71, 47, 'Roxbury', 'Orange' UNION ALL
SELECT 72, 74, 'Westmore', 'Westmore' UNION ALL
SELECT 73, 111, 'Orange', 'Orange' UNION ALL
SELECT 74, 43, 'Groton', 'Orange' UNION ALL
SELECT 75, 5, 'North Duxbury', 'Johnson' UNION ALL
SELECT 76, 91, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 77, 101, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 78, 102, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 79, 80, 'Westmore', 'Groton' UNION ALL
SELECT 80, 107, 'Westmore', 'Westmore'
) d



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429854
Posted Tuesday, March 12, 2013 8:49 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 4,356, Visits: 6,188
ChrisM@Work (3/12/2013)
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.


Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.


Getting a bit off-topic here, but:

A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process.

B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1429865
Posted Tuesday, March 12, 2013 9:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
TheSQLGuru (3/12/2013)
ChrisM@Work (3/12/2013)
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.


Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.


Getting a bit off-topic here, but:

A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process.

B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit.


We'll have to agree to disagree for now because I can't fault the sense of your words. I time virtually everything I write and have done for quite a while. You can tell if I've written a query because it's likely to have something like this after it;
-- (24611 row(s) affected) / 00:00:01


I'd love to have enough time to set up a decent test to demonstrate what I'm seeing, but it won't happen this week.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429881
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse