SQL Server Performance Forum – Threads Archive

how to avoid sort?

I have two tables: 1. Current_Table
columns: A, B, C, D, E, F, G, H
PK columns: A, B, C, D 2. History_Table
columns: A, B, C, D, E, F, G, H
PK columns: A, B, C, D, G, H Obviously, the History_Table is used to keep track of changes to the current table. The only difference is the Primary key between those two tables. For backup procedure, I have a query like this: insert into
History_Table
selet A,B,C,D,E,F,G,H
from
Current_Table
where
Current_Table.A = @A
and
Current_Table.B = @B Now when the current_table grows bigger, the above query becomes very slow. I took a look at the execution plan. The most time is spent on a sort after the index seek. The sort is like: ———————————–
sort ……
…… Argument:
ORDER BY: (Current_Table.C ASC, Current_Table.D ASC, Current_Table.E ASC, Current_Table.F ASC)
———————————— So is there a way to avoid this sort? Thanks.

Your insert query is somewhat confusing – not that it explains the ordering problem, but obviously we’re not seeing the entire picture here: insert into OLD_table
select a,b,c,d,e,f,g,h
from CURRENT_table
where OLD_table.A = @A and OLD_table.B = @B In the WHERE statement, you can only refer to tables or aliases mentioned in the FROM clause, so you should get this error message: "The column prefix ‘OLD_table’ does not match with a table name or alias name used in the query."

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Your insert query is somewhat confusing – not that it explains the ordering problem, but obviously we’re not seeing the entire picture here: insert into OLD_table select a,b,c,d,e,f,g,h from CURRENT_table where OLD_table.A = @A and OLD_table.B = @B In the WHERE statement, you can only refer to tables or aliases mentioned in the FROM clause, so you should get this error message: "The column prefix ‘OLD_table’ does not match with a table name or alias name used in the query." <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />]Put the wrong table name. Should be "Current_table.A = @A and Current_table.B = @B". I’ll update the original post too.

And there’s nothing structural you’ve left out here? Just wondering … [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> well, there used to be a FK and Index on the History_Table but I dropped those and the sort is still there <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />

Ah, there’s no primary key?! SQL Server needs a primary key on each and every table, otherwise the response time will rise through the roof. So add a meaningless identity column, and make it the primary key – I’m pretty sure those ugly sorting routines will disappear.

quote:Originally posted by Adriaan Ah, there’s no primary key?! SQL Server needs a primary key on each and every table, otherwise the response time will rise through the roof. So add a meaningless identity column, and make it the primary key – I’m pretty sure those ugly sorting routines will disappear.

no, there ARE primary keys for both the current_table and history_table. From my original post: 1. Current_Table
columns: A, B, C, D, E, F, G, H
PK columns: A, B, C, D 2. History_Table
columns: A, B, C, D, E, F, G, H
PK columns: A, B, C, D, G, H Actually, I just did some testing. I removed the primary keys and the insert turned out to be much faster. So the sort definitely has something to do with the primary key.

Ah, stupid of me – indeed you were talking about a foreign key the last time. So is the PK a clustered index? Probably a good idea to make it non-clustered.

Ok, I made the index nonclustered and it did improve the preformance. Thanks.

]]>