Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

When would be the order by faster ? Expand / Collapse
Author
Message
Posted Monday, August 9, 2010 6:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #965885
Posted Monday, August 9, 2010 6:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,437, Visits: 10,127
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
Post #965890
Posted Monday, August 9, 2010 6:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #965897
Posted Monday, August 9, 2010 6:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 40,425, Visits: 36,875
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 2008, MVP
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

Post #965905
Posted Monday, August 9, 2010 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,437, Visits: 10,127
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
Post #965911
Posted Tuesday, August 10, 2010 2:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #966418
Posted Tuesday, August 10, 2010 3:03 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 40,425, Visits: 36,875
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 2008, MVP
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

Post #966458
Posted Tuesday, August 10, 2010 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,437, Visits: 10,127
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
Post #966469
Posted Tuesday, August 10, 2010 4:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 40,425, Visits: 36,875
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 2008, MVP
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

Post #966515
Posted Tuesday, August 10, 2010 4:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 5,437, Visits: 10,127
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
Post #966528
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse