SQL Stmt fine tune

[newvisva]

I have a SQL stmt that someone wrote... I'm trying to make it faster... anybody can share the idea?


SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY DateModified DESC)
AS Row, * FROM activities where Location LIKE '%abcd%' or
Subject LIKE '%abcd%' or Location LIKE '%abcd%' or Availability LIKE '%abcd%' or RecurrenceXMLDetail LIKE '%abcd%' or ColorCode LIKE '%abcd%') AS ActRows
WHERE Row >= 1 AND Row <= 10

thanks

[andreasblixt]

First of all, expand the * into actual column names. You don't want to get columns you're not using, plus it adds overhead to the SQL engine if it has to expand it itself.

Second, you can just skip the whole ROW_NUMBER() OVER ... code because you're fetching the first 10 rows. There's no point in creating a whole record set with numbered rows when you can just use TOP:

SELECT TOP 10 * FROM Activities WHERE Location LIKE '%abcd' OR ...

If the reason you've got the ROW_NUMBER() OVER ... is because you want efficient pagination, here's what I usually do:

WITH List AS (
	SELECT
		TOP (<Page> * <RowsPerPage>)
		ROW_NUMBER() OVER (ORDER BY PrimaryKey) AS Row,
		ColumnA, ColumnB, ColumnC
	FROM TableA
)
SELECT
	ColumnA, ColumnB, ColumnC
FROM List
WHERE Row > (<Page> - 1) * <RowsPerPage>;

Replace <Page> and <RowsPerPage> in the query with the proper values. Basically this avoids making a temporary record set on the server with all the rows before retrieving the actual range you want.

If you're going to use ROW_NUMBER() OVER ..., unless DateModified DESC is the primary key, or you can guarantee that the rows come out in the same order, you should add an ORDER BY to your query to get the rows in the correct order.

[newvisva]

Thanks andreasblixt, let me try it out.