CODEWITHSUNDEEP
sqlsql-serverperformance
Anish Bhobe
Anish Bhobe

Reputation: 69

Fastest way to sort in Ms sql

I have two large databases(2.7 million entries each) which need to be merged and sorted . The sorting of merged table consumes a lot of time when done by order by. Is there any other way to sort this merged table faster? I have already tried indexing which speeds up the execution but i'm looking for more speed If anyone can help.Thanks

Upvotes: 2

Views: 2128

Answers (2)

Y.B.
Y.B.

Reputation: 3586

  1. Have clustered index on the sort column [date] to pre-order the data in each table.
  2. Create indexed view on the query with primary key on ([date], id). That would effectively create merged table maintained automatically by RDBMS engine.
  3. Add more RAM, Cores, faster SSDs for tempdb etc.

Update following comments on approach 1. Basing on this query:

Select *
From table1
Inner Join table2 On table1.id=table2.id
Order By table1.[date]

table2 having id for the primary key does not require any additional indexes.

table1 would benefit from

CREATE CLUSTERED INDEX idx_table1_date ON table1 ([date])
INCLUDE ( ...all other fields except id which is included anyway... );
--... so that all the select data is available from the index without another lookup by PK

That would effectively create a copy of table1 pre-sorted by date. As the result query would scan idx_table1_date and lookup table2 by primary key.

Upvotes: 4

MK_
MK_

Reputation: 1169

It depends much on your actual columns but you should try to optimize the indexes even further for this query. Properly placed indexes should speed it up, but you have got to realize it's still a bunch of data and sorting is a pain in SQL Server.

You might want to check this link for some tips.

Upvotes: 0

Related Questions