August 31, 2016 at 1:10 pm
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
Tarun Jaggi (8/29/2016)
Hi All,I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a non-clustered index on (Column2, Column3).
Now when I am deleting (using join with temp table) the data based on Column3 ...
If you are joining to the main table only by column3, and not also by column1 and/or column 2, then you need to create another nonclus index on the main table on column3. If the temp table can be large, cluster the temp table on column3.
Careful there. I can't count the number of times I have REMOVED index(es) from client code to provide a performance INCREASE to the total operation. The cost of maintaining or creating an index just to hit the object once is VERY seldom a win.
If you don't, SQL will be forced to do a full table scan. Particularly with RCSI in effect, that can lead to many deadlocks. I've removed thousands of indexes here, but when one is needed, it is needed. Here I believe that it is.
But you are robbing Peter to pay Paul, right? Don't you have to do a full table scan to build the index on the temp table? And do the tempdb-hitting sort and then tempdb hit to write out the index data (or often worse populate the data with the clustered index in place already)??
And with a straight up join of large data it is almost guaranteed that the entire object will be hit - meaning a clustered index (table) scan. So why is that scan different from a heap scan?
Also, I may have missed it but I didn't see any mention by the OP of deadlocks occurring during this process.
Thought you were objecting to the index on the main table.
As to the temp table, definitely create the clus index prior to loading the table. Otherwise, yes, you do have to scan the data again. The goal is to get a merge join between the nonclus index on the main table and the clus index on the temp table. If you don't index the temp table, you'll be forced into a loop join (most likely) or a hash join if the temp table is really large, either of which will have much worse performance than a merge join.
Getting the merge join on a very large base table is the primary reason I didn't say "always" in my statement against indexes on temp objects.
As to the temp table, definitely create the clus index prior to loading the table. Otherwise, yes, you do have to scan the data again.
I disagree there certainly for those cases where the clustered index on the temp object isn't always increasing, and maybe not even then (identity excepted, but that is another almost always silly thing I see clients do with their temp tables). I put down a TODO item for myself to validate this belief with some testing.
You also have to consider if that temp table will be joined to multiple times.
At any rate, if the temp table is small, the sort to initially load it in clus order is trivial anyway. If it's not, the sort allows a merge join to the main table -- even if you have force SQL to use merge -- which is the main thing anyway, i.e., to reduce the contention time and overhead on the main table. If I can offload some I/O, etc., from the main table to the temp table I'll do that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 31, 2016 at 1:15 pm
The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a larger table.
Anyway we have beat this horse enough. Moving on ... 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2016 at 1:20 pm
TheSQLGuru (8/31/2016)
The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a larger table.
Quite true, which means you end up sorting the data anyway, and every time you run the query. Why not do it just once when you create the table and have it in place for the life of the table!?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 31, 2016 at 2:35 pm
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a larger table.Quite true, which means you end up sorting the data anyway, and every time you run the query. Why not do it just once when you create the table and have it in place for the life of the table!?
I'm still (and always have been on this thread) talking about a temporary table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2016 at 2:55 pm
TheSQLGuru (8/31/2016)
ScottPletcher (8/31/2016)
TheSQLGuru (8/31/2016)
The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a larger table.Quite true, which means you end up sorting the data anyway, and every time you run the query. Why not do it just once when you create the table and have it in place for the life of the table!?
I'm still (and always have been on this thread) talking about a temporary table.
Yes, after my initial misunderstanding, we both were, as was I above. No index on temp tables isn't bad, but far too often I've seen only a nonclus index. That almost never makes sense. Just cluster by that column instead.
I didn't think you objected to adding a nonclus index, on column3, to the main table, as I also suggested, but I could be wrong.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply