Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When would be the order by faster ?


When would be the order by faster ?

Author
Message
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
use DOMAIN_event
go

CREATE table email_destination_bk
(
msg_stub ut_stub not null ,
acct_id int not null ,
evt_stub ut_stub not null,
evt_code nvarchar(20)
)
create clustered index idx on email_destination_bk ( evt_stub)

CREATE table email_source_bk
(
acct_id int not null ,
evt_stub ut_stub not null,
[ut_stub] ut_stub not null,
evt_code nvarchar(20)
)
create clustered index idx on email_source_bk ( acct_id, evt_code)

---------------------------------------------------------------------------
truncate table email_destination_bk
insert into email_destination_bk
select ut_stub,acct_id , evt_stub , evt_code
from email_source_bk
where acct_id = 2000122
order by evt_stub, acct_id



truncate table email_destination_bk
insert into email_destination_bk
select ut_stub,acct_id , evt_stub , evt_code
from email_source_bk
where acct_id = 2000122
order by acct_id,evt_code






when order by will behave better , when is it according to source table ? or destination table ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7444 Visits: 15128
Bhuvnesh

I'm not sure what you're asking, but in any case, why would you use an ORDER BY in a SELECT statement for an INSERT? This doesn't guarantee that the rows will be inserted in that order. Even if it did, you still couldn't guarantee that the rows will come out in that order... unless you use an ORDER BY!

Hope that makes sense
John
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
John Mitchell-245523 (8/9/2010)
This doesn't guarantee that the rows will be inserted in that order.
thanks
Then in which case , "order by" will be relevant for insert statement.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
When there's an identity column on the destination table. The order by will set the order of the identity. Nothing else.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7444 Visits: 15128
Bhuvnesh

What I meant is that you should use an ORDER BY when SELECTing, not when INSERTing. So instead of this:

-- INSERT data into destination table
insert into email_destination_bk
select ut_stub,acct_id , evt_stub , evt_code
from email_source_bk
where acct_id = 2000122
order by acct_id,evt_code

-- SELECT data from destination table
select ut_stub,acct_id , evt_stub , evt_code
from email_destination_bk



you'd do this:

-- INSERT data into destination table
insert into email_destination_bk
select ut_stub,acct_id , evt_stub , evt_code
from email_source_bk
where acct_id = 2000122

-- SELECT data from destination table
select ut_stub,acct_id , evt_stub , evt_code
from email_destination_bk
order by acct_id,evt_code



Bear in mind that if you want data stored in a table in a certain order then you need to create the appropriate clustered index on the table. Bear in mind also that even this doesn't absolutely guarantee that your SELECT statement will return the data from that table in that order, unless you include an ORDER BY clause.

John
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
One of my Senior DBA told me that if we have "order by" clause according to source table then there would be page split during insertion as the data is not in the order as we have clustered key in destination table.but this would happen in case of heavy volume of data.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
Tell your senior DBA that he should check his facts before making statements like that. It's trivial to prove he's wrong.

CREATE TABLE TestingInsertOrder (
ID UNIQUEIDENTIFIER PRIMARY KEY,
Filler CHAR(50)
)
GO

INSERT INTO TestingInsertOrder (ID)
SELECT TOP (50000) NEWID()
FROM master.sys.columns a CROSS JOIN master.sys.columns b



I'm sure we'll all agree that there's no way that the newID will be generated in order? If what your senior DBA says is true, SQL will insert them in the random order they're generated. But check the execution plan.

http://www.sqlservercentral.com/Forums/Attachment6774.aspx

There's a sort operator in there, sorting the rows into the clustered index order prior to the insert.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7444 Visits: 15128
Gail, reading between the lines, I think what the senior DBA is saying is that if you insert data that is already sorted in the order of the clustered index of the destination table, then SQL Server has less work to do when the data is inserted. However, the ORDER BY and the clustered index insert both use a sort operation, as your test shows, so I don't think it makes much difference. Bhuvnesh, you need to test and test this before deciding how to proceed - you'll be especially interested in what happens when there's already a lot of data in the destination table.

John
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47239 Visits: 44377
John Mitchell-245523 (8/10/2010)
Gail, reading between the lines, I think what the senior DBA is saying is that if you insert data that is already sorted in the order of the clustered index of the destination table, then SQL Server has less work to do when the data is inserted.


The question is, how would SQL know that the data is in order if it doesn't put a sort in?

In the initial post, neither of the order by statements matched the clustered index, so if SQL honoured those order bys, it would have to order twice during the insert.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7444 Visits: 15128
Gail

The first ORDER BY in the original post matches the clustered index, albeit with the unnecessary addition of a second column.

I think you're right - a sort would be necessary. Maybe the sort would have to do less work if the data is already ordered. I don't know - I haven't tried it. This is why I said to Bhuvnhesh that he must test thoroughly.

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search