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    #outputfrom    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.TicketIDorder   by a.TicketID, d.SignatureOrderOn 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    #outputfrom    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.TicketIDorder   by a.TicketID, d.SignatureOrderSorry 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. 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. 
Query for a ticket system involving cases and casts on many attributes
performance;sql;sql server