May 19, 2014 at 6:00 am
Again, let me get this straight...
ChrisM@Work (5/19/2014)
Investigate all of the tables referenced in this query and list those tables which contain columns [chassis] and [model], making a note of the datatype. You want to make the datatype the same for each column whichever table it’s in,
Ie alter the table structure to change datatype so that these match between tables, or create the indexes that use datatypes that match between said indexes? Apologies if the second option is non-sequiter, I didn't even suspect until today that an index might be use a different datatype for a column than the one assigned to the column in the original 'Create Table' statement.
Next, address indexing of tblRegHist. A PK or at least a clustered index would normally be recommended. This query would definitely benefit from a covering index too, but since it’s a nonsense query you’re using for testing, make a note to remove it afterwards.
I seem to remember that a clustered index is a greater necessity than a non-clustered one, if that's the most grievous lacking here I will add it. I'm not sure why I'd use a 'nonsense' query, this is all happening on a pre-migration box so I'm free to test in the most lifelike manner possible.
May 19, 2014 at 6:24 am
Jake Shelton (5/19/2014)
Again, let me get this straight...Ie alter the table structure to change datatype so that these match between tables, or create the indexes that use datatypes that match between said indexes? Apologies if the second option is non-sequiter, I didn't even suspect until today that an index might be use a different datatype for a column than the one assigned to the column in the original 'Create Table' statement.
No. You can't change the data type in an index. Whatever the data type of the column is, that's what it will be in the index. No options there, at all.
I seem to remember that a clustered index is a greater necessity than a non-clustered one, if that's the most grievous lacking here I will add it. I'm not sure why I'd use a 'nonsense' query, this is all happening on a pre-migration box so I'm free to test in the most lifelike manner possible.
The reason for the importance of the clustered index is because the clustered index literally stores the data. So, the index itself becomes the access path to the data. Otherwise you're dealing with a heap, which, within SQL Server, is generally less efficient in terms of storage and retrieval. Although, as some wag is sure to come along and point out, there are exceptions to that general rule. Regardless, most of your tables, most of the time, should have a well-chosen and properly defined clustered index. Exceptions to that rule are exceptional.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2014 at 6:47 am
Jake Shelton (5/19/2014)
Again, let me get this straight...ChrisM@Work (5/19/2014)
Investigate all of the tables referenced in this query and list those tables which contain columns [chassis] and [model], making a note of the datatype. You want to make the datatype the same for each column whichever table it’s in,Ie alter the table structure to change datatype so that these match between tables, or create the indexes that use datatypes that match between said indexes? Apologies if the second option is non-sequiter, I didn't even suspect until today that an index might be use a different datatype for a column than the one assigned to the column in the original 'Create Table' statement.
Next, address indexing of tblRegHist. A PK or at least a clustered index would normally be recommended. This query would definitely benefit from a covering index too, but since it’s a nonsense query you’re using for testing, make a note to remove it afterwards.
I seem to remember that a clustered index is a greater necessity than a non-clustered one, if that's the most grievous lacking here I will add it. I'm not sure why I'd use a 'nonsense' query, this is all happening on a pre-migration box so I'm free to test in the most lifelike manner possible.
Grant has eloquently addressed most of this, except the nonsense query.
Joining the results of a NOT EXISTS query back to the table used for the NOT EXISTS predicate isn't going to output any rows under any circumstances. It's pointless.
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
May 20, 2014 at 10:11 am
Added the Clustered Index to tbl.RegHist (after dropping the non-clustered one of the same name), runtime remains the same.
Attempted to change the datatype for the Chassis column to Varchar(8)...
Msg 5074, Level 16, State 1, Line 1
The object 'PK_tblVehicleTurnoverSummary' is dependent on column 'chassis'.
Msg 5074, Level 16, State 1, Line 1
The index '_dta_index_tblVehicleTurnoverSummary_14_1950018078__K3_K7_K2_K1_K4' is dependent on column 'chassis'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1950018078_1_2_7_4_3' is dependent on column 'chassis'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN chassis failed because one or more objects access this column.
May 20, 2014 at 10:19 am
Jake Shelton (5/20/2014)
Added the Clustered Index to tbl.RegHist (after dropping the non-clustered one of the same name), runtime remains the same.Attempted to change the datatype for the Chassis column to Varchar(8)...
Msg 5074, Level 16, State 1, Line 1
The object 'PK_tblVehicleTurnoverSummary' is dependent on column 'chassis'.
Msg 5074, Level 16, State 1, Line 1
The index '_dta_index_tblVehicleTurnoverSummary_14_1950018078__K3_K7_K2_K1_K4' is dependent on column 'chassis'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1950018078_1_2_7_4_3' is dependent on column 'chassis'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN chassis failed because one or more objects access this column.
In this case the primary key would need dropped. But before going down that road, you need to see if their are FK dependencies tied to it. If there are, the FKs would need to be dropped. Then the PK can be dropped. You can see at this point that it will take a lot more effort and planning. In the end it will be worth it.
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
May 20, 2014 at 10:28 am
SQLRNNR (5/20/2014)
Jake Shelton (5/20/2014)
Added the Clustered Index to tbl.RegHist (after dropping the non-clustered one of the same name), runtime remains the same.Attempted to change the datatype for the Chassis column to Varchar(8)...
Msg 5074, Level 16, State 1, Line 1
The object 'PK_tblVehicleTurnoverSummary' is dependent on column 'chassis'.
Msg 5074, Level 16, State 1, Line 1
The index '_dta_index_tblVehicleTurnoverSummary_14_1950018078__K3_K7_K2_K1_K4' is dependent on column 'chassis'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_1950018078_1_2_7_4_3' is dependent on column 'chassis'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN chassis failed because one or more objects access this column.
In this case the primary key would need dropped. But before going down that road, you need to see if their are FK dependencies tied to it. If there are, the FKs would need to be dropped. Then the PK can be dropped. You can see at this point that it will take a lot more effort and planning. In the end it will be worth it.
Even more so as that was just one column of three tables containing the column-names 'Chassis' and 'Model(code)' !!!
May 21, 2014 at 8:58 am
Par excellence!!!
Dropped the index, stats and Primary Key (this is JUST for the chassis column on tblVehicleTurnoverSummary, not even touching the model column datatype), changed the datatype to varchar (8), and it ran in under 4 seconds!!
A warm congratulatory thanks to ALL of you good gentlemens, especially Chris+Grant!!
May 21, 2014 at 9:03 am
Very good. Another superb example of implicit conversions gone wild.
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
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply