Order by Clause Performance Considerations

The defined length matters

Remember that a variable-length column has a defined length and an actual length.

For example, a VARCHAR (30) column may contain ABC, in which case its defined length is 30 characters and its actual length is three characters.

For sorting, it's the 30-character definition that matters!

We tested this by varying the defined length of a VARCHAR column, while keeping the actual data length the same. The result was that Scenario #1 is slower than Scenario #2 even when the contents are the same in both cases:

Scenario #1:

create table #test

(

name varchar(100)

)

insert into #test

Select top 1000 custname from customer

The length of the custname column is 25.In this scenario we have temporary table column has defined length more than actual length.

select name from #test

order by name

 

Scenario #2:

create table #test2

(

name varchar(25)

)

insert into #test2

select top 1000 firstname from client

In this scenario we have temporary table column has defined length equal to actual length.

select name from #test2

order by name

Moral

Defining VARCHAR columns with "room to grow" degrades sorts. The gain might seem surprising,but it's consistent with the way that most sort algorithms work: They allot a fixed memory buffer in advance, and the size of the buffer depends on the maximum anticipated key size.