June 10, 2014 at 12:56 pm
That's where we disagree. 🙂
When I set statistics time,io on for the JOIN solution, it gives the following results:
Table '#B558B379' Scan count 0, logical reads 3
Table #test Scan count 1, logical reads 4
Table 'Worktable' Scan count 1, logical reads 9
Table #ident_map Scan count 0, logical reads 3
Table Worktable Scan count 0, logical reads 0,
Table #test Scan count 1, logical reads 1
Table '#B558B379'. Scan count 1, logical reads 1,
Total elapsed time between 11-13 ms
Using the added column with statistics time,io on we see the following
Table #ident_map Scan count 0, logical reads 3
Table #test Scan count 1, logical reads 4
Table Worktable Scan count 1, logical reads 9
Five logical reads saved, and elapsed time was only 3 milliseconds. Clearly an improvement, at least for data at this scale.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 10, 2014 at 1:58 pm
The Dixie Flatline (6/10/2014)
That's where we disagree. 🙂When I set statistics time,io on for the JOIN solution, it gives the following results:
Table '#B558B379' Scan count 0, logical reads 3
Table #test Scan count 1, logical reads 4
Table 'Worktable' Scan count 1, logical reads 9
Table #ident_map Scan count 0, logical reads 3
Table Worktable Scan count 0, logical reads 0,
Table #test Scan count 1, logical reads 1
Table '#B558B379'. Scan count 1, logical reads 1,
Total elapsed time between 11-13 ms
Using the added column with statistics time,io on we see the following
Table #ident_map Scan count 0, logical reads 3
Table #test Scan count 1, logical reads 4
Table Worktable Scan count 1, logical reads 9
Five logical reads saved, and elapsed time was only 3 milliseconds. Clearly an improvement, at least for data at this scale.
I hear what you are saying and in fact I almost agree. There are few points though which I must put forward,
-Rolling over a period is a one-off per period, performance considerations in that sense aren't great.
-Having to look up the previous value is a prerequisite for the insert.
-The benefits of a (Kimball'istic) bridge table (identity_map) are great when it comes to avoiding self joining.
-Not having to change the existing schema means working within the requirements
😎
June 10, 2014 at 2:15 pm
This has turned into a very enjoyable discussion. 🙂
-Rolling over a period is a one-off per period, performance considerations in that sense aren't great.
(I agree.)
-Having to look up the previous value is a prerequisite for the insert.
(??? With the new column in the #test table, you don't have to look anything up. It's simply a function of the OUTPUT.)
-The benefits of a (Kimball'istic) bridge table (identity_map) are great when it comes to avoiding self joining.
(I agree that bridge tables are a good thing in one-many or many-many scenarios. Both examples populate identity_map. However in 1-1 situations we add an intermediate join to the bridge table and still wind up joining back to the original table. Actually, with the test table containing both old and new values, Ident_Map could be replaced by a nonclustered index, ending the argument entirely.)
-Not having to change the existing schema means working within the requirements
(The business requirements are to store the old identity value with the new. That can be accomplished in a number of ways. All other things being equal, developers should look for the most efficient ways to satisfy the business requirements. I will concede that adding another column may not be allowed in some environments, and that it will marginally increase the amount of disk space used.)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 10, 2014 at 2:50 pm
This all started when I asked IF the OP was able to modify the table. And if so, I suggested adding a new column. Both approaches have merit and the best choice will depend on the circumstances. The only person who knows that is the OP. We can debate this until we are blue in the face but without the specifics of the rest of the system it is just a discussion about whose shot in the dark was closest to the target. 😛
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 3:31 pm
Go ahead, Sean. Spoil my fun. :crying:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 10, 2014 at 3:34 pm
The Dixie Flatline (6/10/2014)
Go ahead, Sean. Spoil my fun. :crying:
No fun spoiling here!!! The discussion is a good one but you both really want to be right and the reality is that both of you are depending on stuff we don't know. I declare a draw!!!
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 4:00 pm
Sean, I'm just kidding with you.
I wasn't continuing this discussion in order to "win" it. The best discussions ultimately reach a consensus, because everyone understands the other person's point of view. If people are learning, then it's a win-win situation all around. You're absolutely right that the OP has moved on, and Eirikur has made a number of good points that I'm happy to concede. When we disagree, I just want to make sure I understand why.
I'll hush now.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 10, 2014 at 4:14 pm
I was watching all the discussions and apart from it trying to get the column added by my architect.
Adding the column is still going on..
It appears the design will be modified to include a new column ( not to store old identity ) to include a natural key in the table.
Once i have the natural key added, then the solution would be
1) Rollover the records to new quarter
2) Do a self join on new quarter records to old quarter records based on natural key
3) With the join, get the old and new identity values and load into ident_map table.
June 10, 2014 at 4:18 pm
Thanks for letting us know, vasuarjula.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 11, 2014 at 7:03 am
The Dixie Flatline (6/10/2014)
Sean, I'm just kidding with you.I wasn't continuing this discussion in order to "win" it. The best discussions ultimately reach a consensus, because everyone understands the other person's point of view. If people are learning, then it's a win-win situation all around. You're absolutely right that the OP has moved on, and Eirikur has made a number of good points that I'm happy to concede. When we disagree, I just want to make sure I understand why.
I'll hush now.
I knew you were just kidding with me. 😛 It was a good discussion and I sort of feel like I quashed it. You both made a number of excellent points. Please don't ever hush.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2014 at 1:42 pm
CELKO (6/11/2014)
What you posted is not tables at all! No keys, no DRI and very poor design.The proprietary, non-relational IDENTITY is a table property and not a column (but then, you think that rows are records!)This is as silly as using a physical parking space instead of a VIN for the key in a table of automobiles.
Oooucchh!!!
Such a comment is as daft as counting squashed flies on the bonnet instead of a VIN, didn't you read the original post? It is a design the OP has to work with, not against!
There is a design flaw called “attribute splitting”; it is when you take one value and put it in two or more columns.
The quarters are such a design error.
In the real world, not the SQL Standard Utopia, this is often necessary, again not the OP's call to make!
Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.
The report period table gives a name to a range of dates that is common to the entire enterprise.
CREATE TABLE quarter_Periods
(quarter_name CHAR(6) NOT NULL PRIMARY KEY
CHECK (quarter_name LIKE '[12][0-9][0-9][0-9]Q[1234]'),
quarter_start_date DATE NOT NULL,
quarter_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (quarter_start_date <= quarter_end_date),
etc);
Here is a corrected design:
CREATE TABLE Tests
(test_nbr INTEGER NOT NULL PRIMARY KEY,
something_name VARCHAR(20) NOT NULL,
test_qtr CHAR(6) NOT NULL);
This is the syntax for insertion in SQL. Now y0u have not created this silly problem, extra code and kludges! c
INSERT INTO Tests(test_nbr, something_name, test_qtr)
VALUES
(1, 'today', '2014Q1'),
(2, 'tomorrow, '2014Q1'),
(3, 'friday', '2014Q1'),
(4, 'today', '2014Q2'),
(5, 'tomorrow, '2014Q2'),
(6, 'friday', '2014Q2');
I have been teaching SQL for a few decade and I worked on the Language Standard ; I will estimate that you will need about 5 years to become a useful SQL programmer. You have to unlearn so much!
Your design is slightly out of date though, Tithonian Jurassic I would guess:hehe:
Grin aside, the last comment is uncalled for, it will not earn you any respect. I refrain from negative comments but I am really tempted to make an exception here.
😎
June 11, 2014 at 2:25 pm
Eirikur Eiriksson (6/11/2014)
Grin aside, the last comment is uncalled for, it will not earn you any respect.
I don't think Joe is all that worried about earning respect. Might be worth a quick google to see who he is. 😉
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2014 at 2:37 pm
Sean Lange (6/11/2014)
Eirikur Eiriksson (6/11/2014)
Grin aside, the last comment is uncalled for, it will not earn you any respect.I don't think Joe is all that worried about earning respect. Might be worth a quick google to see who he is. 😉
Thank you Sean for the heads up, I tend to get carried away if I feel someone is unjustly accused of wrong doing. Throws an unhandled exception.
Eirikur
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply