Optimizing this 40+ second select query on MSSQL 2012?

Question

I'm no DBA and I'm out of ideas on optimizing this query. It's taking roughly 40+ seconds to run. Any glaring newbie mistakes where I could optimize?

USE [deskcal2014]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[proc_AdminRegisteredCards]
(
   @Take         AS INT,
   @Skip         AS INT,
   @FilterColumn AS NVARCHAR(max),
   @FilterOrder AS NVARCHAR(max))
AS
BEGIN
    SELECT TOP(@Take) 
      ISNULL( ROW_NUMBER() OVER(ORDER BY ta.CreatedOn, ta.ItemId), -1000) AS AdminRegisteredCardsId,
      ta.ItemId,
      ta.CardNumber, 
      ta.FirstName, 
      ta.LastName, 
      ta.Birthday, 
      ta.PostalCode, 
      ta.[Description], 
      ta.CardActivated, 
      ta.ContactInfo, 
      ta.PhoneNumber,
      ta.ReceiveCalendarReminders, 
      ta.ReceiveGeneralMails, 
      ta.ReceivePrefStoreMails, 
      ta.CardStatus, 
      ta.SamoaCardId, 
      ta.CalendarUserId, 
      ta.LiveOpsRegistrantId,
      ta.UseType,
      ta.CreatedOn,
      ta.ModifiedBy, 
      ta.ModifiedOn from (
          SELECT CalendarUser.CalendarUserId as ItemId,
          SamoaCard.CardNumber,
          SamoaCard.FirstName,
          SamoaCard.LastName,
          CalendarUser.Birthday,
          CalendarUser.PostalCode,
          RegisterSourceType.[Description],
          CalendarUserCard.CardActivated,
          CalendarUser.EmailAddress as ContactInfo,
          CalendarUser.PhoneNumber,
          CalendarUser.ReceiveCalendarReminders,
          CalendarUser.ReceiveGeneralMails,
          CalendarUser.ReceivePrefStoreMails,
          CASE WHEN CalendarUserCard.CardDeactivated IS NOT NULL THEN 'Deactivated' ELSE 'Activated' END AS CardStatus,
          SamoaCard.SamoaCardId,
          CalendarUser.CalendarUserId,
          null as LiveOpsRegistrantId,
          SamoaCard.CreatedOn,
          'C' as UseType,
          CalendarUser.ModifiedBy,
          CalendarUser.ModifiedOn     
              FROM (
                  (dbo.CalendarUser CalendarUser 
                  INNER JOIN dbo.RegisterSourceType RegisterSourceType ON (CalendarUser.RegisterType = RegisterSourceType.RegisterType))
                  INNER JOIN dbo.CalendarUserCard CalendarUserCard     ON (CalendarUserCard.CalendarUserId = CalendarUser.CalendarUserId)
              )
              INNER JOIN dbo.SamoaCard SamoaCard ON (CalendarUserCard.SamoaCardId = SamoaCard.SamoaCardId)
              ORDER BY
                  case when @FilterColumn = 'FirstName' and @FilterOrder = 'ASC' 
                    then CalendarUser.Firstname end asc, 
                  case when @FilterColumn = 'FirstName' and @FilterOrder = 'DESC' 
                    then CalendarUser.Firstname end desc, 
                  case when @FilterColumn = 'LastName' and @FilterOrder = 'ASC' 
                    then CalendarUser.Lastname end asc, 
                  case when @FilterColumn = 'LastName' and @FilterOrder = 'DESC' 
                    then CalendarUser.Lastname end desc,
                  case when @FilterColumn = 'CardNumber' and @FilterOrder = 'ASC' 
                    then CalendarUser.CardNumber end asc, 
                  case when @FilterColumn = 'CardNumber' and @FilterOrder = 'DESC' 
                    then CalendarUser.CardNumber end desc,
                  case when @FilterColumn = 'Birthday' and @FilterOrder = 'ASC' 
                    then CalendarUser.Birthday end asc, 
                  case when @FilterColumn = 'Birthday' and @FilterOrder = 'DESC' 
                    then CalendarUser.Birthday end desc,  
                  case when @FilterColumn = 'Description' and @FilterOrder = 'ASC' 
                    then RegisterSourceType.[Description] end asc, 
                  case when @FilterColumn = 'Description' and @FilterOrder = 'DESC' 
                    then RegisterSourceType.[Description] end desc,
                  case when @FilterColumn = 'ContactInfo' and @FilterOrder = 'ASC' 
                    then CalendarUser.EmailAddress end asc, 
                  case when @FilterColumn = 'ContactInfo' and @FilterOrder = 'DESC' 
                    then CalendarUser.EmailAddress end desc,
                  case when @FilterColumn = 'CardActivated' and @FilterOrder = 'ASC' 
                    then CalendarUserCard.CardActivated end asc, 
                  case when @FilterColumn = 'CardActivated' and @FilterOrder = 'DESC' 
                    then CalendarUserCard.CardActivated end desc,
                  case when @FilterColumn = 'PostalCode' and @FilterOrder = 'ASC' 
                    then CalendarUser.PostalCode end asc, 
                  case when @FilterColumn = 'PostalCode' and @FilterOrder = 'DESC' 
                    then CalendarUser.PostalCode     end desc
              OFFSET @Skip ROWS -- skip N rows
              FETCH NEXT @Take ROWS ONLY


    union all
    SELECT TOP(10)
        LiveOpsRegistrant.LiveOpsRegistrantId as ItemId, 
        LiveOpsRegistrant.CardNumber,
        'Registered' as FirstName,
        'Card' as LastName,
        LiveOpsRegistrant.Birthday,
        null as PostalCode,
        'LiveOps' as Description,
        LiveOpsRegistrant.CreatedOn as CardActivated,
        LiveOpsRegistrant.PhoneNumber as ContactInfo,
        LiveOpsRegistrant.PhoneNumber,
        CONVERT(bit,0) as ReceiveCalendarReminders,
        CONVERT(bit,0) as ReceiveGeneralMails,
        CONVERT(bit,0) as ReceivePrefStoreMails,
        'Activated' AS CardStatus,
        SamoaCard.SamoaCardId,
        null as CalendarUserId,
        LiveOpsRegistrant.LiveOpsRegistrantId,
        SamoaCard.CreatedOn,
        'L' as UseType,
        SamoaCard.ModifiedBy,
        SamoaCard.ModifiedOn
    FROM dbo.LiveOpsRegistrant LiveOpsRegistrant
         INNER JOIN dbo.SamoaCard SamoaCard ON (LiveOpsRegistrant.CardNumber = SamoaCard.CardNumber)) ta
END
GO

[Get an execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan), also there are [better ways](http://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/) to implement paging in SQL Server 2012. — Justin, Feb 10 '14 at 13:57
Does your query runs faster if you run it as query and not as procedure? — kostas ch., Feb 10 '14 at 13:59
I don't see a where clause anywhere. You might be selecting the entire database. — Dan Bracuk, Feb 10 '14 at 14:01
If the query is faster than the procedure use FORCE ORDER http://technet.microsoft.com/en-us/library/ms181714.aspx — kostas ch., Feb 10 '14 at 14:06
@Justin: What better ways are there? The SQL 2012 example in the link you pasted is doing the same thing I am with the `offset` and `fetch next` functions. — sergserg, Feb 10 '14 at 14:06
@DanBracuk I thought that too, but he's using `TOP` on both halves of the union. He is *scanning* the entire table though. — Bohemian, Feb 10 '14 at 14:06
@Serg Sorry, I missed the middle part of your query and thought you were doing another sort of paging. — Justin, Feb 10 '14 at 14:08
Could the select-case for the filter and the sortorder be messing with the query plan? What happens if you remove the dynamic parts there? — OlleR, Feb 10 '14 at 14:14
Similar to a prior question and you did not even clean up the SQL syntax that was done on the prior. Same process - examine the query plan. Break it down. Is the first query or the second the problem? Show some effort. — paparazzo, Feb 10 '14 at 15:36
Paging a dynamic sorted query is a hard task. OlleR suggestion is good to see if that dynamic sort is the culprint (and most times is). But what to do to better dynamic sort that mess? Long ago in a SQL 7 environment I managed to put the select in a temp table, (dynamicly) create indexes after table populated and sort/page by the index. It was a age ago and I don't know that can help you, but you can make a try. — jean, Feb 10 '14 at 15:48

score 0 · Answer 1 · answered Feb 13 '14 at 06:57

Echoing some of the comments already given: Having a bunch of logic in an ORDER BY clause usually doesn't work well. ROW_NUMBER() can be nasty when used in a query with many joins and other complexities as in your case.

Temp tables are probably your first best option here. Reading your code, I think the first one is for CalendarUser.CalendarUserId, and you'll want to populate it with a bunch of nested if ... else if statements:

if @FilterColumn = 'FirstName' and @FilterOrder = 'ASC' 
begin
    insert into #CalendarUser 
    select top(@Take) CalendarUserId
    order by Firstname asc
    offset @Skip rows
    fetch next @Take rows only
end
else
begin
    if .....

Populate a second temp table #DataOut with all the fields you want to output, using an inner join on #CalendarUser to filter the result set. Exclude the field you're calculating with ROW_NUMBER(). Leave the UNION ALL out of this query, append the data from that into #DataOut table as a separate step.

The final output query will be

select 
  ISNULL( ROW_NUMBER() OVER(ORDER BY CreatedOn, ItemId), -1000) 
    AS AdminRegisteredCardsId,
  #DataOut.*
from #DataOut

Not pleasant to write, feels brute force, but I'm fairly sure you'll see a dramatic performance improvement.

score 0 · Answer 2 · answered Feb 14 '14 at 18:18

I've been trying to optimize a similar query and came to the conclusion that ordering and filtering across joins has a big impact on performance - my advice would be to denormalize everything that you sort by or filter on using an indexed view and seeing what impact this has on the performance.

Optimizing this 40+ second select query on MSSQL 2012?

2 Answers2