July 25, 2008 at 7:15 am
Attached is the table creation script, one record of table data and a query along with it's execution plan.
July 25, 2008 at 10:46 am
From a look at it, the performance you have is probably the best you're going to get. Between the SELECT * and the complex where clause, you're looking at an index scan to resolve.
Sorry I can't help more.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 25, 2008 at 1:02 pm
Say, Gail, I got this from Mark Willis on Experts-Exchange. Basically it's building dynamic SQL.
ALTER PROCEDURE [dbo].[Select_Payments]
@PaymentID as int,
@PaymentCode as int,
@PaymentDtTmStart as datetime,
@PaymentDtTmEnd as datetime,
@PaymentNumber as nvarchar(50),
@PaymentAmt as decimal,
@DepositDtTmStart as datetime,
@DepositDtTmEnd as datetime,
@LedgerDtTmStart as datetime,
@LedgerDtTmEnd as datetime,
@Void as tinyint,
@ReceiptID as int
AS
declare @sql varchar(max)
set @sql = 'SELECT * FROM Payments
WHERE (DeletedDtTm IS NULL)'
+ case when isnull(@paymentid,0) > 0 then ' and (PaymentID = @PaymentID) ' else '' end
+ case when isnull(@paymentCode,0) > 0 then ' and (PaymentCode = @PaymentCode) ' else '' end
+ case when isnull(@paymentDtTmStart,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm >= @PaymentDtTmStart) ' else '' end
+ case when isnull(@paymentDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm <= @PaymentDtTmEnd) ' else '' end
+ case when isnull(@paymentNumber,'*') <> '*' and len(rtrim(@paymentNumber)) > 0 then ' and (PaymentNumber = @PaymentNumber) ' else '' end
+ case when isnull(@paymentAmt,0) > 0 then ' and (PaymentCode = @PaymentAmt) ' else '' end
+ case when isnull(@depositDtTmStart,'01/01/1900') > '01/01/1900' then ' and (depositDtTm >= @depositDtTmStart) ' else '' end
+ case when isnull(@depositDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (depositDtTm <= @depositDtTmEnd) ' else '' end
+ case when isnull(@ledgerDtTmStart,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm >= @ledgerDtTmStart) ' else '' end
+ case when isnull(@ledgerDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm <= @ledgerDtTmEnd) ' else '' end
+ case when isnull(@void,0) > 0 then ' and (PaymentCode = @void) ' else '' end
+ case when isnull(@receiptid,0) > 0 then ' and (PaymentCode = @receiptid) ' else '' end
print @sql
--exec(@sql)
GO
However, something isn't working right when I run it with the exec and not the print.
I get 'Must declare the scalar variable "@PaymentID".'
Am I going to have to dynamically DECLARE my variables, they ARE inputs to the stored procedure, why would they not be in scope here?
DECLARE @PaymentID as int
DECLARE @PaymentCode as int
DECLARE @PaymentDtTmStart as datetime
DECLARE @PaymentDtTmEnd as datetime
DECLARE @PaymentNumber nvarchar(50)
DECLARE @PaymentAmt as decimal(18,2)
DECLARE @DepositDtTmStart as datetime
DECLARE @DepositDtTmEnd as datetime
DECLARE @LedgerDtTmStart as datetime
DECLARE @LedgerDtTmEnd as datetime
DECLARE @Void as int
DECLARE @ReceiptID as int
----------------------------------------
-- Set scalar values based on table data
----------------------------------------
SET @PaymentID = 6437941
SET @PaymentCode = NULL
SET @PaymentDtTmStart = NULL
SET @PaymentDtTmEnd = NULL
SET @PaymentNumber = NULL
SET @PaymentAmt = NULL
SET @DepositDtTmStart = NULL
SET @DepositDtTmEnd = NULL
SET @LedgerDtTmStart = NULL
SET @LedgerDtTmEnd = NULL
SET @Void = NULL
SET @ReceiptID = NULL
exec Select_Payments @PaymentID, @PaymentCode, @PaymentDtTmStart, @PaymentDtTmEnd, @PaymentNumber, @PaymentAmt, @DepositDtTmStart, @DepositDtTmEnd, @LedgerDtTmStart, @LedgerDtTmEnd, @Void, @ReceiptID
-- Even tried this way, Same error
--exec Select_Payments 6437941,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
-- This is the SQL printed:
SELECT * FROM ptPayments
WHERE (DeletedDtTm IS NULL) AND (PaymentID = @PaymentID)
July 26, 2008 at 7:05 am
Your parameter names seem to be different when you build your dynamic SQL.
@paymentID and @PaymentID are not the same.
July 26, 2008 at 9:33 am
Dynamic SQL is about the best solution for performance for this type of query. Though it does bring its own complexities. Apologies, I was under the impression that you were not allowed to change the generated code.
From what I can see, you're referring to the variable within the string. That is your problem. Dynamic SQL executes in a different scope, just as if you'd called another stored proc. You wouldn't expect expect variables declared in one proc to be visible in another. Same thing here.
You do have to declare any variables used inside dynamic SQL and you do have to pass in the vales. It's not as bad as it sounds, but it can't be done with a simple EXEC.
Please tell Mark from Expert Exchange that he should pay a little more attention to the rules of variable scoping 😛 😀 , then try this:
ALTER PROCEDURE [dbo].[Select_Payments]
@PaymentID as int,
@PaymentCode as int,
@PaymentDtTmStart as datetime,
@PaymentDtTmEnd as datetime,
@PaymentNumber as nvarchar(50),
@PaymentAmt as decimal,
@DepositDtTmStart as datetime,
@DepositDtTmEnd as datetime,
@LedgerDtTmStart as datetime,
@LedgerDtTmEnd as datetime,
@Void as tinyint,
@ReceiptID as int
AS
declare @sql nvarchar(max)
set @sql = 'SELECT * FROM Payments
WHERE (DeletedDtTm IS NULL)'
+ case when isnull(@paymentid,0) > 0 then ' and (PaymentID = @Payment_ID) ' else '' end
+ case when isnull(@paymentCode,0) > 0 then ' and (PaymentCode = @Payment_Code) ' else '' end
+ case when isnull(@paymentDtTmStart,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm >= @Payment_DtTmStart) ' else '' end
+ case when isnull(@paymentDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (PaymentDtTm <= @Payment_DtTmEnd) ' else '' end
+ case when isnull(@paymentNumber,'*') <> '*' and len(rtrim(@paymentNumber)) > 0 then ' and (PaymentNumber = @Payment_Number) ' else '' end
+ case when isnull(@paymentAmt,0) > 0 then ' and (PaymentCode = @Payment_Amt) ' else '' end
+ case when isnull(@depositDtTmStart,'01/01/1900') > '01/01/1900' then ' and (depositDtTm >= @deposit_DtTmStart) ' else '' end
+ case when isnull(@depositDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (depositDtTm <= @deposit_DtTmEnd) ' else '' end
+ case when isnull(@ledgerDtTmStart,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm >= @ledger_DtTmStart) ' else '' end
+ case when isnull(@ledgerDtTmEnd,'01/01/1900') > '01/01/1900' then ' and (ledgerDtTm <= @ledger_DtTmEnd) ' else '' end
+ case when isnull(@void,0) > 0 then ' and (PaymentCode = @void_) ' else '' end
+ case when isnull(@receiptid,0) > 0 then ' and (PaymentCode = @receipt_id) ' else '' end
print @sql
exec sp_executeSQL @sql,
N'@Payment_ID int,
@Payment_Code as int,
@Payment_DtTmStart as datetime,
@Payment_DtTmEnd as datetime,
@Payment_Number as nvarchar(50),
@Payment_Amt as decimal,
@Deposit_DtTmStart as datetime,
@Deposit_DtTmEnd as datetime,
@Ledger_DtTmStart as datetime,
@Ledger_DtTmEnd as datetime,
@Void_ as tinyint,
@Receipt_ID as int',
@Payment_ID = @PaymentID,
@payment_Code = @paymentCode,
@payment_DtTmStart = @paymentDtTmStart,
@payment_DtTmEnd=@paymentDtTmEnd,
@payment_Number=@paymentNumber,
@payment_Amt = @paymentAmt,
@deposit_DtTmStart = @depositDtTmStart,
@deposit_DtTmEnd = @depositDtTmEnd,
@ledger_DtTmStart = @ledgerDtTmStart,
@ledger_DtTmEnd=@ledgerDtTmEnd,
@void_= @void,
@receipt_id = @receiptid
GO
sp_executesql allows the passing of variables into the dynamic SQL. You will notice that the variables used within the dynamic SQL have slightly different names from the variables declared and used outside. It's intentional to avoid confusion. It's not actually as bad as it looks
Please test. I don't get syntax errors and I do get a result set back, but I don't have your data to test for validity.
@paymentID and @PaymentID are not the same.
They are unless the DB is in a case-sensitive collation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2008 at 2:59 pm
I'm sorry for the confusion about edit capabilities. I *am* allowed to make changes and it looks like this *breakthrough* is going to be a big feather in my cap with the higher ups. My only being there for 3 wks is a good thing because I can recommend changes that make things work better, faster and not be chastised.
Thanks again, Gail, that actually looks like it has more promise and I won't have to worry about doing any converts.
July 28, 2008 at 3:12 am
MrBaseball34 (7/27/2008)
I'm sorry for the confusion about edit capabilities. I *am* allowed to make changes and it looks like this *breakthrough* is going to be a big feather in my cap with the higher ups.
Congratulations. That's a fantastic way to start out at a new company.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 8:23 am
Now, based on your post, I have added all the parameters for my Sp and am getting an error:
The parameterized query '(@Payment_ID as int,
@PaymentBatchID' expects the parameter '@PaymentBatchID', which was not supplied.
Can you see anything I'm doing wrong here?
{This is the SQL printed:
SELECT * FROM ptPayments
WHERE (DeletedDtTm IS NULL) AND (PaymentID = @Payment_ID)
}
Changed SP:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[rr_Collect_ptPayments]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[rr_Collect_ptPayments]
GO
CREATE PROCEDURE [dbo].[rr_Collect_ptPayments]
@PaymentID as int,
@PaymentBatchID as int,
@MRN as nvarchar(25),
@PayerContactID as int,
@InsPlanID as int,
@PayerType as int,
@PaymentCode as int,
@PaymentType as nvarchar(50),
@PaymentNumber as nvarchar(50),
@PaymentAmt as decimal(18,2),
@PaymentDtTmStart as datetime,
@PaymentDtTmEnd as datetime,
@DepositDtTmStart as datetime,
@DepositDtTmEnd as datetime,
@LedgerDtTmStart as datetime,
@LedgerDtTmEnd as datetime,
@Void as tinyint,
@ReceiptID as int
AS
BEGIN
DECLARE @sql nvarchar(max)
SET @sql = 'SELECT * FROM ptPayments
WHERE (DeletedDtTm IS NULL)'
+ CASE WHEN ISNULL(@PaymentID,0) > 0 THEN ' AND (PaymentID = @Payment_ID) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentBatchID,0) > 0 THEN ' AND (PaymentBatchID = @PaymentBatch_ID) ' ELSE '' END
+ CASE WHEN ISNULL(@MRN,0) > 0 THEN ' AND (MRN = @MRN_) ' ELSE '' END
+ CASE WHEN ISNULL(@PayerContactID,0) > 0 THEN ' AND (PayerContactID = @PayerContact_ID) ' ELSE '' END
+ CASE WHEN ISNULL(@InsPlanID,0) > 0 THEN ' AND (InsPlanID = @InsPlan_ID) ' ELSE '' END
+ CASE WHEN ISNULL(@PayerType,0) > 0 THEN ' AND (PayerType = @Payer_Type) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentCode,0) > 0 THEN ' AND (PaymentCode = @Payment_Code) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentType,'*') <> '*' AND LEN(RTRIM(@PaymentType)) > 0
THEN ' AND (PaymentType = @Payment_Type) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentNumber,'*') <> '*' AND LEN(RTRIM(@PaymentNumber)) > 0
THEN ' AND (PaymentNumber = @Payment_Number) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentAmt,0) > 0 THEN ' AND (PaymentAmt = @Payment_Amt) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentDtTmStart,'01/01/1900') > '01/01/1900'
THEN ' AND (PaymentDtTm >= @Payment_DtTmStart) ' ELSE '' END
+ CASE WHEN ISNULL(@PaymentDtTmEND,'01/01/1900') > '01/01/1900'
THEN ' AND (PaymentDtTm <= @Payment_DtTmEND) ' ELSE '' END
+ CASE WHEN ISNULL(@DepositDtTmStart,'01/01/1900') > '01/01/1900'
THEN ' AND (DepositDtTm >= @Deposit_DtTmStart) ' ELSE '' END
+ CASE WHEN ISNULL(@DepositDtTmEND,'01/01/1900') > '01/01/1900'
THEN ' AND (DepositDtTm <= @Deposit_DtTmEND) ' ELSE '' END
+ CASE WHEN ISNULL(@LedgerDtTmStart,'01/01/1900') > '01/01/1900'
THEN ' AND (LedgerDtTm >= @Ledger_DtTmStart) ' ELSE '' END
+ CASE WHEN ISNULL(@LedgerDtTmEND,'01/01/1900') > '01/01/1900'
THEN ' AND (LedgerDtTm <= @Ledger_DtTmEND) ' ELSE '' END
+ CASE WHEN ISNULL(@Void,0) > 0 THEN ' AND (Void = @Void_) ' ELSE '' END
+ CASE WHEN ISNULL(@ReceiptID,0) > 0 THEN ' AND (ReceiptID = @Receipt_ID) ' ELSE '' END
--print @sql
exec sp_executeSQL @sql,
N'@Payment_ID as int,
@PaymentBatchID as int,
@MRN as nvarchar(25),
@PayerContactID as int,
@InsPlanID as int,
@PayerType as int,
@PaymentCode as int,
@PaymentType as nvarchar(50),
@Payment_Number as nvarchar(50),
@Payment_Amt as decimal,
@Payment_DtTmStart as datetime,
@Payment_DtTmEnd as datetime,
@Deposit_DtTmStart as datetime,
@Deposit_DtTmEnd as datetime,
@Ledger_DtTmStart as datetime,
@Ledger_DtTmEnd as datetime,
@Void_ as tinyint,
@Receipt_ID as int',
@Payment_ID = @PaymentID,
@PaymentBatch_ID = @PaymentBatchID,
@MRN_ = @MRN,
@PayerContact_ID = @PayerContactID,
@InsPlan_ID = @InsPlanID,
@Payer_Type = @PayerType,
@Payment_Code = @PaymentCode,
@Payment_DtTmStart = @PaymentDtTmStart,
@Payment_DtTmEnd = @PaymentDtTmEnd,
@Payment_Number = @PaymentNumber,
@Payment_Amt = @PaymentAmt,
@Deposit_DtTmStart = @DepositDtTmStart,
@Deposit_DtTmEnd = @DepositDtTmEnd,
@Ledger_DtTmStart = @LedgerDtTmStart,
@Ledger_DtTmEnd = @LedgerDtTmEnd,
@Void_ = @Void,
@Receipt_ID = @ReceiptID
END
GO
Test code:
DECLARE @PaymentID as int
DECLARE @PaymentBatchID as int
DECLARE @MRN as nvarchar(25)
DECLARE @PayerContactID as int
DECLARE @InsPlanID as int
DECLARE @PayerType as int
DECLARE @PaymentCode as int
DECLARE @PaymentType nvarchar(50)
DECLARE @PaymentNumber nvarchar(50)
DECLARE @PaymentAmt as decimal(18,2)
DECLARE @PaymentDtTmStart as datetime
DECLARE @PaymentDtTmEnd as datetime
DECLARE @DepositDtTmStart as datetime
DECLARE @DepositDtTmEnd as datetime
DECLARE @LedgerDtTmStart as datetime
DECLARE @LedgerDtTmEnd as datetime
DECLARE @Void as int
DECLARE @ReceiptID as int
SET @PaymentID = 6437952
SET @PaymentBatchID = NULL
SET @MRN = NULL
SET @PayerContactID = NULL
SET @InsPlanID = NULL
SET @PayerType = NULL
SET @PaymentCode = NULL
SET @PaymentType = NULL
SET @PaymentNumber = NULL
SET @PaymentAmt = NULL
SET @PaymentDtTmStart = NULL
SET @PaymentDtTmEnd = NULL
SET @DepositDtTmStart = NULL
SET @DepositDtTmEnd = NULL
SET @LedgerDtTmStart = NULL
SET @LedgerDtTmEnd = NULL
SET @Void = NULL
SET @ReceiptID = NULL
exec rr_Collect_ptPayments @PaymentID, @PaymentBatchID, @MRN, @PayerContactID, @InsPlanID, @PayerType, @PaymentCode, @PaymentType, @PaymentNumber, @PaymentAmt, @PaymentDtTmStart, @PaymentDtTmEnd, @DepositDtTmStart, @DepositDtTmEnd, @LedgerDtTmStart, @LedgerDtTmEnd, @Void, @ReceiptID
July 28, 2008 at 10:02 am
Yup. I can see the problem.
Look over the example I posted above and look carefully at which variable have the _ and which don't. While I prefer the names more different, that was the easiest at the time.
What I did was to have all the variables used inside the dynamic SQL having an underscore and the ones declared and used in the proc not having an underscore. Perhaps it would be better to prefix them with Inner_ or something to make it clearer.
The variables declared in the sp_executesql must match the names used inside the dynamic SQL. Here's an excerpt of your proc, with the errors highlighted.
exec sp_executeSQL @sql,
N'@Payment_ID as int,
@PaymentBatchID as int,
@MRN as nvarchar(25),
@PayerContactID as int,
@InsPlanID as int,
@PayerType as int,
@PaymentCode as int,
@PaymentType as nvarchar(50),
@Payment_Number as nvarchar(50),
@Payment_Amt as decimal,
@Payment_DtTmStart as datetime,
@Payment_DtTmEnd as datetime,
@Deposit_DtTmStart as datetime,
@Deposit_DtTmEnd as datetime,
@Ledger_DtTmStart as datetime,
@Ledger_DtTmEnd as datetime,
@Void_ as tinyint,
@Receipt_ID as int',
Do you understand why its wrong?
Does the select have to be a SELECT *? Do you need all the columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 10:22 am
Thanks, I've got it now. Yes, all columns are required and it's easier to write. 😉
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply