CODEWITHSUNDEEP
sqlsql-serversorting
Adam
Adam

Reputation: 400

SQL Server sort slow with many common values

I have a view of invoices that brings in customer name, things like that.

There are a few million rows here over a few hundred customers. The view is lightning fast - but if I add a sort on Customer Name it takes a while to load (like 3000ms compared to 5ms).

The query joins Invoice to Customer, then sorts by Customer Name then by order number.

I understand why - just wondering if there are any tricks for improving the sort speed?

Upvotes: 0

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You could add an index on the customer id/name.

You could partition by the table by customer.

The order by is going to take a long time, because -- without an index -- it has to read all the data. If you use and index or table partition, then you can get the rows without reading the entire table.

To elaborate on the index. There are two considerations. The first is whether you are selecting all the rows or only a few. An index will definitely help the time to first record returned, in most cases.

The second consideration is whether the index helps in fetching all the records. The question here is selectivity. If a data page in the invoice table has several hundred records, and there are only several hundred customers, then the index will not help. On average, each page would have each customer.

If a data page has only a few dozen invoices, then, on average, only one page in about ten would have a given customers invoice -- and, voila, the index would reduce the I/O from a full table scan by a factor of about 10. (There are other considerations, such as read-ahead but I'm skipping those.)

You don't provide enough information about the system and table structure for a definitive answer, but an index might be useful.

Upvotes: 1

Related Questions