3
\$\begingroup\$

I have this SELECT statement that inserts into a temp table and it is slow! Right now it takes ~1500 ms to run and only deals with ~7000 rows. As the data grows larger the longer it will take.

I am using SQL server 2014.

Is there anyway to improve performance of all these case and casts in the statement?

select  a.TicketID as [Ticket ID],
    b.TicketTypeText as [Type],
    b.TicketTypeID as [*TypeID],
    isnull(a.TicketDescriptionReport, a.TicketDescriptionUser) + isnull(' [' + h.AcuDocTypeText + ' ' + g.DocUserFirst + ' ' + g.DocUserLast + ']','') as [Description],
    k.Names as [Assigned To],
    i.name as [Requested By],
    CAST(DateRequested as Date) as [Date Requested],
    case when InScope = 1 then 'Yes' when InScope = 0 then 'No' else '???' end as [Scpe],
    c.ticketStatusText as [Status],
    d.SignatureUserName  as [Pending User Sign],
    case when a.ticketstatusid = 4 then CAST(a.LastModifiedDate as date) else null end as [*CompleteDate],
    cast(case when @p_Clock = d.SignatureUserClock
                or (b.TicketTypeID = 3 and (exists(select assign.AssignClock from #assignments assign where assign.TicketID = a.TicketID and assign.AssignClock = @p_Clock))) then 1 else 0 end as bit) as [*PendingCurrentUser],
    CAST(case when @p_Clock = a.UserRequested then 1 else 0 end as bit) as [*RequestedCurrentUser],
    CAST(case when a.TicketStatusID = 4 then 1 else 0 end as bit) as [*TicketCompleted],
    CAST(case when a.TicketStatusID = 10 then 1 else 0 end as bit) as [*TicketRefused],
    CAST(case when @isIT = 1 or @p_Clock = d.SignatureUserClock or @p_Clock = a.UserRequested  then 1 else 0 end as bit) as [*ShowMe],
    CAST(case when a.TicketStatusID < 4 and @isIT = 1 then 1 else 0 end as bit) as [*AllowRejectEdit],
    @isIT as [*isIT],
    d.SignatureID as [*SigID],
    CAST(case when a.TicketTypeID in (0,3) and @isIT = 1 then 1 else 0 end as bit) as [*CanConvertITRF],
    CAST(case when a.TicketTypeID IN (0,2,3) and @isIT = 1 and a.TicketStatusID < 4 then 1 else 0 end as bit) as [*CanEditSigs],
    a.TicketDescriptionReport as [*TDR],
    CAST(case when a.TicketTypeID IN (1,2) then 1 else 0 end as bit) as [*CanPrint],
    case when a.TicketTypeID = 1 then 'AddChangeUser' when a.TicketTypeID = 2 then 'ITRF' else '' end [*RptName],
    case when a.TicketTypeID = 1 then 1 else 0 end [*IsACU],
    case when a.TicketTypeID = 2 then 1 else 0 end [*IsITRF],
    isnull(j.NoteCount,0) as [*NoteCount],
    isnull(j.AllNotes,'') as [*AllNotes],
    CASE WHEN a.TicketTypeID <> 1 then a.TicketDescriptionUser else
+ N'ACU Type: ' + h.AcuDocTypeText + char(13)
+ N'User Clock: ' + cast((DocUser) as varchar(32)) + char(13)
+ N'User Last Name: ' + (DocUserLast) + char(13)
+ N'User First Name: ' + (DocUserFirst) + char(13)
+ case when BadgePinNumber is not null then N'Badge Pin: ' + cast((BadgePinNumber) as varchar(32)) + char(13) else '' end
+ case when SimilarToUser is not null then N'Similar User: ' + cast((SimilarToUser) as varchar(32)) + char(13) else '' end
+ N'Changes Effective: ' + cast((ChangesEffectiveDate) as varchar(32)) + char(13)
+ case when ActiveDirectory = 1 then N'Yes' + char(13) else '' end
+ case when InternetAccess = 1 then N'Yes' + char(13) else '' end
+ case when EmailAccess = 1 then N'Yes' + char(13) else '' end
+ case when ShareAccess is not null then N'(Read): ' + char(13) else '' end
+ case when ShareAccessWrite is not null then N'(Write): ' + case when ShareAccessWrite = 1 then 'Yes' else 'No' end + char(13) else '' end
+ case when PhoneExtension is not null then N'Yes' + char(13) else '' end
+ case when Voicemail = 1 then N'Yes' + char(13) else '' end
+ case when MsOffice = 1 then N'Yes' + char(13) else '' end
+ case when MsProject = 1 then N'Yes' + char(13) else '' end
+ case when MsVisio = 1 then N'Yes' + char(13) else '' end
+ case when isnull(AutoCAD, 0) = 1 then N'Yes' + char(13) else '' end
+ case when CAccess = 1 then N'Yes' + char(13) else '' end
+ case when SoftwareUG = 1 then N'Yes' + char(13) else '' end
+ case when SoftwareSolidWorks = 1 then N'Yes' + char(13) else '' end
+ case when RequirePC = 1 then N'Yes' + char(13) else '' end
+ case when RequireLaptop = 1 then N'Yes' + char(13) else '' end
+ case when isnull(RequireMonitorCount, 0) > 0 then N'Yes' + char(13) else '' end
+ case when RemoteAccess = 1 then N'Yes' + char(13) else '' end
+ case when RequirePrinter = 1 then N'Yes' + char(13) else '' end
+ case when isnull(RequireBadgeScanner, 0) = 1 then N'Yes' + char(13) else '' end
+ case when isnull(RequireI, 0) = 1 then N'Yes' + char(13) else '' end
+ case when RequireOther is not null then N'Requires Other Equipment: ' + RequireOther + char(13) else '' end
+ case when ChangeClock is not null then N'Change Clock to: ' + cast(ChangeClock as varchar(32)) + char(13) else '' end
+ case when ChangeDept is not null then N'Change Department to: ' + ChangeDept + char(13) else '' end
+ case when AddRm = 1 then N'Yes' + char(13) else '' end
+ case when ChangeB = 1 then N'Yes' + char(13) else '' end
+ case when isnull(timeOverride, 0) = 1 then N'Yes' + char(13) else '' end
+ case when isnull(Receipt, 0) = 1 then N'Yes' + char(13) else '' end
+ case when CpMove = 1 then N'Yes' + char(13) else '' end
+ case when isnull(DSProf, 0) = 1 then N'Yes' + char(13) else '' end
+ case when isnull(EQProf, 0) = 1 then N'Yes' + char(13) else '' end
+ case when EtchP = 1 then N'Yes' + char(13) else '' end
+ case when FixB = 1 then N'Yes' + char(13) else '' end
+ case when isnull(GoalEditor, 0) = 1 then N'Yes' + char(13) else '' end
+ case when isnull(WaxD, 0) = 1 then N'Yes' + char(13) else '' end
+ case when PieceM = 1 then N'Yes' + char(13) else '' end
+ case when isnull(TSBo, 0) = 1 then N'Yes' + char(13) else '' end
+ case when isnull(ScrapM, 0) = 1 then N'Yes' + char(13) else '' end
+ case when Shipping = 1 then N'Yes' + char(13) else '' end
+ case when isnull(WIAl, 0) = 1 then N'Yes' + char(13) else '' end
+ case when isnull(WhatIf, 0) = 1 then N'Yes' + char(13) else '' end
+ case when Allo = 1 then N'Yes' + char(13) else '' end
+ case when AlloH = 1 then N'Yes' + char(13) else '' end
+ case when AlloCharges = 1 then N'Yes' + char(13) else '' end
+ case when AlloReleaseCharges = 1 then N'Yes' + char(13) else '' end
+ case when AlloBalanceInv = 1 then N'Yes' + char(13) else '' end
+ case when AlloShipment = 1 then N'Yes' + char(13) else '' end
+ case when AlloAdj = 1 then N'Yes' + char(13) else '' end
+ case when AlloHTabs = 1 then N'Yes' + char(13) else '' end
+ case when AlloCustSpec = 1 then N'Yes' + char(13) else '' end
+ case when AlloViewMH = 1 then N'Yes' + char(13) else '' end
+ case when AlloReportsInv = 1 then N'Yes' + char(13) else '' end
+ case when AlloChargeWeights = 1 then N'Yes' + char(13) else '' end
+ case when AlloOrder = 1 then N'Yes' + char(13) else '' end
+ case when AlloBalance = 1 then N'Yes' + char(13) else '' end
+ case when AlloSpecial = 1 then N'Yes' + char(13) else '' end
+ case when AlloCharges = 1 then N'Yes' + char(13) else '' end
+ case when AlloCast = 1 then N'Yes' + char(13) else '' end
+ case when AlloReportsUsage = 1 then N'Yes' + char(13) else '' end
+ case when AlloConsumeCharges = 1 then N'Yes' + char(13) else '' end
+ case when AlloRev = 1 then N'Yes' + char(13) else '' end
+ case when AlloChargeMaint = 1 then N'Yes' + char(13) else '' end
+ case when AlloCloseH = 1 then N'Yes' + char(13) else '' end
+ case when TimecardApprovalDept is not null then N'Timecard Approval for Department: ' + TimecardApprovalDept + char(13) else '' end
+ case when eReqCreate = 1 then N'Yes' + char(13) else '' end
+ case when eReqApprove = 1 then N'Yes' + char(13) else '' end
+ case when OtherRights is not null then N'Other Rights: ' + OtherRights + char(13) else '' end
+ case when OsbView = 1 then N'Yes' + char(13) else '' end
+ case when OsbPoChange = 1 then N'Yes' + char(13) else '' end
+ case when OsbPoApprove = 1 then N'Yes' + char(13) else '' end
+ case when OsbShip = 1 then N'Yes' + char(13) else '' end
+ case when OsbManualInvoice = 1 then N'Yes' + char(13) else '' end
+ case when OsbCreditDebitMemo = 1 then N'Yes' + char(13) else '' end
+ case when OsbCustomerReturns = 1 then N'Yes' + char(13) else '' end
+ case when OsbCustomersEngines = 1 then N'Yes' + char(13) else '' end
+ case when OsbViewCrCdSales = 1 then N'Yes' + char(13) else '' end
+ case when OsbBillingFinance = 1 then N'Yes' + char(13) else '' end
+ case when OsbInvoice = 1 then N'Yes' + char(13) else '' end
+ case when SytAccountsPayable = 1 then N'Yes' + char(13) else '' end
+ case when SytAccountsReceivable = 1 then N'Yes' + char(13) else '' end
+ case when SytCycleCount = 1 then N'Yes' + char(13) else '' end
+ case when SytDock = 1 then N'Yes' + char(13) else '' end
+ case when SytGL = 1 then N'[Syteline] GL: Yes' + char(13) else '' end
+ case when SytGlPost = 1 then N'Yes' + char(13) else '' end
+ case when SytInventory = 1 then N'Yes' + char(13) else '' end
+ case when SytItem = 1 then N'Yes' + char(13) else '' end
+ case when SytJobRoute = 1 then N'Yes' + char(13) else '' end
+ case when SytMiscIssueReceipt = 1 then N'Yes' + char(13) else '' end
+ case when SytPurchasing = 1 then N'Yes' + char(13) else '' end
+ case when SytViewOnly = 1 then N'Yes' + char(13) else '' end
+ case when SytReports = 1 then N'Yes' + char(13) else '' end
+ case when SytSalesMaintenance = 1 then N'Yes' + char(13) else '' end
+ case when SytSalesOrders = 1 then N'Yes' + char(13) else '' end
+ case when SytVendors = 1 then N'Yes' + char(13) else '' end
+ case when SytVouchersAuthorization = 1 then N'Yes' + char(13) else '' end
+ case when SytEnterOutOfDateRange = 1 then N'Yes' + char(13) else '' end
+ case when SytPostOutOfCurrentPeriod = 1 then N'Yes' + char(13) else '' end
+ case when AccountingShareAccess = 1 then N'Yes' + char(13) else '' end
+ case when AccountingShareAccessWrite = 1 then N'Yes' + char(13) else '' end
    end as [*TDU],
    CAST(case when (@isIT = 1 or @p_Clock = d.SignatureUserClock) and 

a.TicketStatusID < 4 and a.TicketTypeID = 1 then 1 else 0 end as bit) as [*AllowEdit]
into    #output
from    db.dbo.Main a join
        db.dbo.TicketType b on a.TicketTypeID = b.TicketTypeID join
        db.dbo.TicketStatus c on a.TicketStatusID = c.TicketStatusID join
        db.dbo.employee i on i.clock = a.UserRequested left join
        PendingSig d on d.TicketID = a.TicketID left join
        db.dbo.OrderReport f on f.TicketTypeID = a.TicketTypeID and d.SignatureOrder = f.SignatureOrder left join
        db.dbo.AcuDetail g on g.TicketID = a.TicketID left join
        db.dbo.AcuDocType h on h.AcuDocTypeID = g.AcuDocTypeID left join
        TicketNotes j on j.TicketID = a.TicketID left join
        #assignmentList k on k.TicketID = a.TicketID
order   by a.TicketID, d.SignatureOrder

On a side note, the statement below take less than 100ms to run as a test to make sure it was all the case and cast that were causing this performance.

select  a.TicketID as [Ticket ID]
into    #output
from    db.dbo.Main a join
        db.dbo.TicketType b on a.TicketTypeID = b.TicketTypeID join
        db.dbo.TicketStatus c on a.TicketStatusID = c.TicketStatusID join
        db.dbo.employee i on i.clock = a.UserRequested left join
        PendingSig d on d.TicketID = a.TicketID left join
        db.dbo.OrderReport f on f.TicketTypeID = a.TicketTypeID and d.SignatureOrder = f.SignatureOrder left join
        db.dbo.AcuDetail g on g.TicketID = a.TicketID left join
        db.dbo.AcuDocType h on h.AcuDocTypeID = g.AcuDocTypeID left join
    TicketNotes j on j.TicketID = a.TicketID left join
    #assignmentList k on k.TicketID = a.TicketID
order   by a.TicketID, d.SignatureOrder

Sorry guys, I got derailed off this project for a little while, I'll update with more code and tables schema soon when I get the time, for now I'll give you this screenshot I had from yesterday. I'm not completely sure how to read execution plans but it shows the Table Insert taking 92% and the every other steps are 0-1%. The execution plan was lengthy to fit on one page, so I took a snippet of it. I'll post more of the stored procedure and and tables when I get the time to make it look nice and make sense.

ToolTipMessage

Here is what the execution plan shows if I comment all the Case and Casts out of the SELECT, as shown in the second block of code in this thread, and it will execute in ~100 ms.

ToolTipNoCaseAndCasts

\$\endgroup\$
5
  • 3
    \$\begingroup\$ As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles. \$\endgroup\$
    – syb0rg
    Jul 18, 2016 at 15:39
  • 1
    \$\begingroup\$ If you've got some kind of schema showing all the DB tables and their structures, that would be really useful. \$\endgroup\$
    – Kaz
    Jul 18, 2016 at 16:08
  • 1
    \$\begingroup\$ Like @Zak said A schema will be helpful - You are allowed to insert an image into your post or link to an external resource e.g github where the image is saved \$\endgroup\$
    – Tolani
    Jul 18, 2016 at 17:13
  • \$\begingroup\$ Your 2nd statement has probably a totally different plan, as the optimizer might remove some joins. Add all the columns you need in #1 and check runtime again. All those CASE and CAST should only add a bit more CPU, but not increase runtime 15 times. \$\endgroup\$
    – dnoeth
    Jul 18, 2016 at 19:05
  • 2
    \$\begingroup\$ This query strikes me as the tip of an iceberg — an indication that there are a lot of unconventional designs in this application. Tell us everything that might be relevant: the schema, the purpose of this query, what the various ticket type codes mean, what you will do with the result, … \$\endgroup\$ Jul 19, 2016 at 5:50

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.