CODEWITHSUNDEEP
sqlsql-server
Tamer Khalil
Tamer Khalil

Reputation: 3

Improving view performance without using an Index

I need to improve the performance of a view. Unfortunately I can't use an index since I'm using "Top Percent" and randomness in my query.

Here is the query used by the view

Select Top (10) Percent from Table
Order By NEWID()

The view pulls the data in around 50 seconds which is too much. I hope you could help me to find a solution for that, without touching the business layer.

Upvotes: 0

Views: 263

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

For a truly random sample, you need some form of randomness. One method that doesn't require sorting is approximate, but might be sufficient for your purposes:

Select t.*
from Table t
where rand(checksum(newid()) <= 0.1;

This is approximate, of course. If you really needed exactly 10 percent, this approach would need more work.

An alternative if an almost-random-sample is good enough is tablesample (which you can read about here).

select t.*
from table t
tablesample (10 percent);

Note that this does a random sample of pages, so it is not a true random sample. And, it cannot be used in a view.

Upvotes: 1

TomTom
TomTom

Reputation: 62093

There is no way to improve this given your requirements. Get more hardware - only solution. It is likely you overload tempdb - in which case a high performance SSD and proper configuration on that one may help.

The reason is that in order to get the top 10 percent by your random order, SQL Server MUST process ALL rows, and order them by the random element.

This is the type of query that looks nice on paper but can lead to tremendous performance issues. I would start by looking at this requirement and try to get around it. FULL randomness is just expensive for non trivial data sets.

Upvotes: 1

Dane
Dane

Reputation: 287

Assuming the table has ids. You can generate random numbers to pick the ten results in your business logic. From here your SQL would be:

SELECT percent FROM table
WHERE ID IN ([coma separated list here]).

Upvotes: 0

Related Questions