1
\$\begingroup\$

I need to improve performance for query

DECLARE @Take INT, @WhatSearch NVARCHAR(MAX);
SET @WhatSearch = '%' + @WhatSearch + '%';

WITH [PublicNewsFilter] AS
(
    SELECT DISTINCT 
        [N].[Title],
        ROW_NUMBER() OVER (ORDER BY [N].[Title]) AS [RowNumber]
    FROM [dbo].[News] [N] WITH(NOLOCK)
    WHERE ([N].[Title] LIKE @WhatSearch) OR ([N].[Description] LIKE @WhatSearch)
)
SELECT [Title]
FROM [PublicNewsFilter]
WHERE [RowNumber] <= @Take
ORDER BY [RowNumber];

Here I'm trying to get only X records ( @Take ) where Title or Description is like search text ( @WhatSearch ).

What do you think about performance and how can I improve it?

Will it more performance to use:

SELECT DISTINCT TOP (@Take) [N].[Title]
FROM [dbo].[News] [N] WITH(NOLOCK)
WHERE ([N].[Title] LIKE @WhatSearch) OR ([N].[Description] LIKE @WhatSearch)
\$\endgroup\$
  • \$\begingroup\$ I'm pretty sure that SELECT TOP (@Take) will give an error, have you tested it? \$\endgroup\$ – Phrancis Aug 18 '16 at 17:00
  • \$\begingroup\$ What kind of indexes, if any, are on Title and Description columns? \$\endgroup\$ – Phrancis Aug 18 '16 at 17:01
  • \$\begingroup\$ Yes, @Take is a number. I've just updated the Q. It work ok with TOP (@Take). No, there isn't indexes because we doesn't use indexes on varchar fields :( \$\endgroup\$ – Roman Marusyk Aug 18 '16 at 17:03
  • 1
    \$\begingroup\$ Is [dbo].[News] a very large table? Are we talking hundreds, thousands or millions of rows? \$\endgroup\$ – Phrancis Aug 18 '16 at 17:05
  • \$\begingroup\$ Did you run the query plan in SSMS? If SSMS is telling you "hey, if you had an index on [Title] and [Description] here, I could do this 45% faster", then "we don't use indexes on varchar fields" is the problem, not the query. That said I'd try to find a way to avoid SELECT DISTINCT. \$\endgroup\$ – Mathieu Guindon Aug 18 '16 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.