November 19, 2007 at 2:11 am
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
November 19, 2007 at 2:17 am
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
November 19, 2007 at 3:58 am
See the query execution plan. Create suitable covering index.
November 19, 2007 at 5:38 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct 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