sql server 2000

  • if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_Sl_CustomerTransactionDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[USP_Sl_CustomerTransactionDetails]

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    --drop proc USP_Sl_CustomerTransactionDetails

    CREATE proc dbo.USP_Sl_CustomerTransactionDetails

    (

    @inConsumerID int=-1

    output

    )

    as

    begin

    select 1 Transcategory,1 As TransTypeCode, ConsumerID,b.RegdId,ReservedOn,LName+' '+FName as ConsumerName,'Registration' As TransType,SwimSessionName as TransInfo,b.FeeAmount,b.currentBalance as Balance ,'1' as Item, ' ' As NavigateUrl, PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryswimsessionregistrations b on a.BillID=b.BillID where b.ConsumerID = @inConsumerID

    union select 1 Transcategory,2 As TransTypeCode, b.ConsumerID,b.RegdId + .1,a.CancelledDate as ReservedOn,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType,SwimSessionName As TransInfo,a.Amount,a.Balance,'1' as Item, '' As NavigateUrl, -1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryswimsessionregistrations b on a.regdID=b.RegdID and TransactionCategoryID=1 where b.ConsumerID =@inConsumerID

    union select 2 Transcategory,1 As TransTypeCode, ConsumerID,RentalId as regdid,Reservedon,Name as ConsumerName,'Reservation' As TransType, dbo.iif(dbo.trim(Title),dbo.trim(FacilityName+': '+convert(varchar,firstbookingstartdatetime,101)+'-'+(RIGHT(CAST(slotstarttime AS varchar), 7))),dbo.trim(Title),dbo.trim(Title)+'-'+dbo.trim(FacilityName+': '+convert(varchar,firstbookingstartdatetime,101)+'-'+(RIGHT(CAST(slotstarttime AS varchar), 7)))),a.BillAmount,a.Balance ,'2' as Item, ' ' As NavigateUrl,b.PaymentTypeID,b.BillID,

    b.Title from tblBillDetails a inner join qryFacilityReservations b on a.BillID=b.BillID and a.regdid=b.rentalid where b.ConsumerID = @inConsumerID

    union select 2 Transcategory,2 As TransTypeCode, b.ConsumerID,RentalId + .1 as regdid ,a.CancelledDate as Reservedon,Name as ConsumerName,'Cancellation/With Drawl' As TransType,dbo.iif(dbo.trim(Title),dbo.trim(FacilityName+': '+convert(varchar,firstbookingstartdatetime,101)+'-'+(RIGHT(CAST(slotstarttime AS varchar), 7))),dbo.trim(Title),dbo.trim(Title)+'-'+dbo.trim(FacilityName+': '+convert(varchar,firstbookingstartdatetime,101)+'-'+(RIGHT(CAST(slotstarttime AS varchar), 7)))),a.Amount,a.Balance,'2' as Item,'' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    b.Title from tblCancellation a inner join qryFacilityReservations b on a.regdID=b.RentalID and TransactionCategoryID=2 where b.ConsumerID =@inConsumerID

    union select 3 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,DateStamp as Reservedon,LName+' '+FName as ConsumerName,'Registration' As TransType, TournamentName ,BillAmount FeeAmount,Balance,'3' as Item, ' ' As NavigateUrl,PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryTournamentsRegistration b on a.regdID=b.RegdID and TransactionCategoryID=3 where b.ConsumerID =@inConsumerID

    union select 3 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid+.1,a.CancelledDate as Reservedon,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType,TournamentName,a.Amount,a.Balance ,'3' as Item, '' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryTournamentsRegistration b on a.regdID=b.RegdID and TransactionCategoryID=3 where b.ConsumerID =@inConsumerID

    Union select 4 Transcategory,1 As TransTypeCode, b.ConsumerID,b.Regdid,b.Datestamp,LName+' '+FName as ConsumerName,'Registration' As TransType, SeasonPassName ,BillAmount,Balance ,'4' as Item, ' ' As NavigateUrl ,PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qrySeasonPassRegd b on a.regdID=b.RegdID and TransactionCategoryID=4 where b.ConsumerID =@inConsumerID

    Union select 4 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid,a.CancelledDate as ReservedOn,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType, SeasonPassName ,a.Amount,a.Balance ,'4' as Item, '' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qrySeasonPassRegd b on a.regdID=b.RegdID and TransactionCategoryID=4 where b.ConsumerID =@inConsumerID

    Union select 5 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,Reservedon,ConsumerName,'Reservation',ProgramName + ' - ' + Convert(varchar,ReservedFor,101),BillAmount FeeAmount,Balance,'5' as Item, ' ' As NavigateUrl, b.PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryProgramRegistrations b on a.BillID=b.BillID and a.regdid=b.regdid and TransactionCategoryID=5 where b.ConsumerID =@inConsumerID

    Union select 5 Transcategory,2 As TransTypeCode, a.ConsumerID,a.Regdid+.1,a.CancelledDate as Reservedon,ConsumerName,'Cancellation/With Drawl' As TransType,ProgramName + ' - ' + Convert(varchar,ReservedFor,101),a.Amount,a.Balance ,'5' as Item, '' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryProgramRegistrations b on a.regdID=b.RegdID and a.BillID=b.BillID and TransactionCategoryID=5 where b.ConsumerID =@inConsumerID

    Union select 6 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,Reservedon,ConsumerName,'Reservation',CampDescription ,BillAmount FeeAmount,Balance,'6' as Item, ' ' As NavigateUrl, PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryCampPrograms b on a.BillID=b.BillID and TransactionCategoryID=6 where b.ConsumerID = @inConsumerID

    Union select 6 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid+.1,a.CancelledDate as Reservedon,ConsumerName,'Cancellation/With Drawl' As TransType, CampDescription,a.Amount,a.Balance ,'6' as Item, '' As NavigateUrl ,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryCampPrograms b on a.regdID=b.RegdID and TransactionCategoryID=6 where b.ConsumerID =@inConsumerID

    Union select 7 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,Reservedon,ConsumerName,'Reservation',ThemeName + ' - ' + Convert(varchar,Bookingdate,101),BillAmount FeeAmount,Balance,'7' as Item, ' ' As NavigateUrl,1 PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryPoolPartyBooking b on a.BillID=b.BillID and a.regdid=b.regdid and TransactionCategoryID=7 where b.ConsumerID =@inConsumerID

    Union select 7 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid+.1,a.CancelledDate as Reservedon,ConsumerName,'Cancellation/With Drawl' As TransType, ThemeName + ' - ' + Convert(varchar,Bookingdate,101),a.Amount,a.Balance ,'7' as Item, '' As NavigateUrl ,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryPoolPartyBooking b on a.regdID=b.RegdID and a.BillID=b.BillID and TransactionCategoryID=7 where b.ConsumerID = @inConsumerID

    Union select 8 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,DateStamp as ReservedOn,LName+' '+FName as ConsumerName,'Registration'As TransType,ActivityName as TransInfo,TotalAmount FeeAmount,Balance ,'8' as Item,' ' As NavigateUrl, PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryActivityRegistrations b on a.BillID=b.BillID and a.regdid=b.Regdid and TransactionCategoryID=8 where b.ConsumerID =@inConsumerID

    Union select 8 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid+.1,a.CancelledDate as ReservedOn,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType,ActivityName as TransInfo,a.Amount,a.Balance ,'8' as Item, '' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryActivityRegistrations b on a.regdID=b.RegdID and TransactionCategoryID=8 where b.ConsumerID = @inConsumerID

    Union select 9 Transcategory,3 As TransTypeCode, tblRefunds.ConsumerID,RefundID+.1,RefundDate,LName+' '+FName as ConsumerName,'Refunded' As TransType, PaymentMethod+' Refund ID:'+ Cast(RefundID as Varchar),RefundAmount,-RefundAmount as Balance ,'9' as Item, '' As NavigateUrl,RefundMode,-1 BillID,

    '1' as Title from tblRefunds inner join tblConsumer on tblRefunds.ConsumerId=tblConsumer.ConsumerId inner join tblLookupPaymentMethod on tblRefunds.PaymentMethodID=tblLookupPaymentMethod.PaymentMethodID where tblRefunds.ConsumerID =@inConsumerID

    Union select 10 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,DateStamp as ReservedOn,LName+' '+FName as ConsumerName,'Registration'As TransType,'Marathon Races/'+''+racedescription as TransInfo,TotalAmount FeeAmount,Balance ,'9' as Item,' ' As NavigateUrl, PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join QryRaceRegistrationDetails b on a.BillID=b.BillID and a.regdid=b.Regdid and TransactionCategoryID=9 where b.ConsumerID =@inConsumerID

    Union select 10 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid+.1,a.CancelledDate as ReservedOn,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType, 'Marathon Races/#'+''+racedescription as TransInfo ,a.Amount,a.Balance ,'9' as Item, '' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join QryRaceRegistrationDetails b on a.regdID=b.RegdID and a.BillID=b.BillID and TransactionCategoryID=9 where b.ConsumerID =@inConsumerID

    union select 12 Transcategory,1 As TransTypeCode, ConsumerID,b.Regdid,DateStamp as Reservedon,LName+' '+FName as ConsumerName,'Miscellaneous Revenue' As TransType,convert(varchar(100),b.Information) as TransInfo ,TotalAmount FeeAmount,Balance,'12' as Item, ' ' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryMiscellaneousRegistrations b on a.regdID=b.RegdID and a.BillID=b.BillID and TransactionCategoryID=12 where b.ConsumerID = @inConsumerID

    union select 12 Transcategory,2 As TransTypeCode, b.ConsumerID,b.Regdid+.1,a.CancelledDate as Reservedon,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType,convert(varchar(100),b.Information)as TransInfo ,a.Amount,a.Balance ,'12' as Item, '' As NavigateUrl,-1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryMiscellaneousRegistrations b on a.regdID=b.RegdID and a.BillID=b.BillID and TransactionCategoryID=12 where b.ConsumerID =@inConsumerID

    Union select 13 Transcategory,1 As TransTypeCode, ConsumerID,b.RegdId,ReservedOn,LName+' '+FName as ConsumerName,'Registration' As TransType,'Private Swim Lessons - '+SwimSessionName as TransInfo,b.FeeAmount,b.currentBalance as Balance ,'13' as Item, ' ' As NavigateUrl, PaymentTypeID,b.BillID,

    '1' as Title from tblBillDetails a inner join qryPrivateswimsessionregistrations b on a.BillID=b.BillID and a.regdID=b.RegdID where b.ConsumerID =@inConsumerID

    union select 13 Transcategory,2 As TransTypeCode, b.ConsumerID,b.RegdId + .1,a.CancelledDate as ReservedOn,LName+' '+FName as ConsumerName,'Cancellation/With Drawl' As TransType,'Private Swim Lessons - '+SwimSessionName as TransInfo,a.Amount,a.Balance,'13' as Item, '' As NavigateUrl, -1 PaymentTypeID,b.BillID,

    '1' as Title from tblCancellation a inner join qryPrivateswimsessionregistrations b on a.regdID=b.RegdID and a.BillID=b.BillID and TransactionCategoryID=13 where b.ConsumerID =@inConsumerID

    Order by ReservedOn

    end

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • Hi Satish,

    the very first thing that strikes me is that you are using unions. Are you sure you cannot get away with "union all"?

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • See the query execution plan. Create suitable covering index.

  • Looking through the query, it looks like you've got some design issues. You're doing joins between the billing & cancellation tables to a whole slew of other tables that all look like some type of registration details. Instead of tuning this monster of a query, I'd look at starting to refactor the design. A few more joins and a lot less UNION operations will seriously improve performance.

    Also, what kind of clustered indexes do you have on those tables? That's something else that's going to affect performance pretty radically in this type of query.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply