CODEWITHSUNDEEP
.netsqlsql-server
FSou1
FSou1

Reputation: 1941

Get random row from table- try to optimize query - SQL Server

I need to take random row from table. At first, i wrote SP like this:

SELECT * FROM 
    (SELECT TOP 1 
     *
     FROM PeopleTales PT
     LEFT JOIN PeopleTalesCategories PTC ON PT.CategoryAn = PTC.Analit
     WHERE LEN(PT.Text) > 900
     ORDER BY NEWID()
    ) t1

This is not very good in Time statistics:

Average total execution time = 200.

Next, i tried to take row manually:

WITH CTE_t
AS
(
        SELECT
        ROW_NUMBER () OVER (ORDER BY PT.Analit) AS RowNumber,
        *
        FROM PeopleTales PT
        LEFT JOIN PeopleTalesCategories PTC ON PT.CategoryAn = PTC.Analit
        WHERE LEN(PT.Text) > 900
)


SELECT * FROM CTE_t
WHERE CTE_t.RowNumber = 1 + ABS(CHECKSUM(NewId())) % (SELECT COUNT(CTE_t.RowNumber) FROM CTE_t)

It's a little better by execution time, but sometimes i had 0 rows, sometimes 1,2 or even 3 rows in result! + i know, using CTE isn't very good. It's not decision.

Average total execution time = 60.

So, next step:

SELECT * FROM (
    SELECT
        ROW_NUMBER () OVER (ORDER BY PT.Analit) AS RowNumber,
        *
    FROM PeopleTales PT
    LEFT JOIN PeopleTalesCategories PTC ON PT.CategoryAn = PTC.Analit
    WHERE LEN(PT.Text) > 900
    ) t1
WHERE t1.RowNumber = (SELECT 1 + ABS(CHECKSUM(NewId())) % (SELECT 
                                                            COUNT(Analit) 
                                                            FROM PeopleTales
                                                            WHERE LEN(Text) > 900))

Every time i have just one row, and its ok with total execution time.

Average total execution time = 60.

So, is it more ways to take random row or optimize my query?

Thx alot for responses.

Upvotes: 0

Views: 202

Answers (1)

Callie J
Callie J

Reputation: 31306

How many rows are in the table, and is it possible to modify your schema? One problem is that all the selects are based on LEN(Column), so SQL Server can't use an index effectively.

What you might want to consider is a computed column, and placing an index on it. Something like:

ALTER TABLE PeopleTales ADD TextLength AS LEN(PT.Text)
GO
CREATE INDEX IX_PeopleTales_TextLen ON PeopleTales (Text, CategoryAN)
GO

I'm including CategoryAN so there's the join is fulfilled purely by the index (and means SQL Server doesn't have to do a bookmark or key lookup).

Then, obviously, change your queries so that WHERE LEN(PT.Text) > 900 becomes WHERE TextLength > 900.

Upvotes: 1

Related Questions