July 24, 2008 at 7:16 am
In the procedure below. I need help with the date ranges in the WHERE clause.
If a start is passed but no end, I also want to check the range between
the start and Now. Everything else looks OK, it's just the data ranges
I'm having problems with. Thanks for looking...
CREATE PROCEDURE [dbo].[Select_Payments]
@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),
@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
SELECT * FROM Payments
WHERE
((@PaymentID IS NULL)
OR (@PaymentID = 0)
OR (PaymentID = @PaymentID))
AND ((@PaymentBatchID IS NULL)
OR (@PaymentBatchID = 0)
OR (PaymentBatchID = @PaymentBatchID))
AND ((@MRN IS NULL)
OR (@MRN = '*')
OR (MRN = @MRN))
AND ((@PayerContactID IS NULL)
OR (@PayerContactID = 0)
OR (PayerContactID = @PayerContactID))
AND ((@InsPlanID IS NULL)
OR (@InsPlanID = 0)
OR (InsPlanID = @InsPlanID))
AND ((@PayerType IS NULL)
OR (@PayerType = 0)
OR (PayerType = @PayerType))
AND ((@PaymentCode IS NULL)
OR (@PaymentCode = 0)
OR (PaymentCode = @PaymentCode))
AND ((@PaymentType IS NULL)
OR (@PaymentType = '*')
OR (PaymentType = @PaymentType))
AND ((@PaymentDtTmStart IS NULL)
OR (@PaymentDtTmEnd IS NULL)
OR (PaymentDtTm BETWEEN @PaymentDtTmStart AND @PaymentDtTmEnd))
AND ((@PaymentNumber IS NULL)
OR (@PaymentNumber = '*')
OR (PaymentNumber = @PaymentNumber))
AND ((@PaymentAmt IS NULL)
OR (@PaymentAmt = 0)
OR (PaymentAmt = @PaymentAmt))
-- Need help with these columns as date ranges, too
AND ((@DepositDtTm IS NULL)
OR (@DepositDtTm IS NULL)
OR (DepositDtTm = @DepositDtTm))
AND ((@LedgerDtTm IS NULL)
OR (@LedgerDtTm IS NULL)
OR (LedgerDtTm = @LedgerDtTm))
AND ((@Void IS NULL)
OR (@Void = 0)
OR (Void = @Void))
AND ((@ReceiptID IS NULL)
OR (@ReceiptID = 0)
OR (ReceiptID = @ReceiptID))
AND (@DeletedDtTm IS NULL)
GO
July 24, 2008 at 7:27 am
Since this is a stored procedure, you could detect a null in the input variable and assign a value to the variable accourdingly ( if start date is null you could use '1/1/1900' , end date is null you could use 1/1/9999 or today and you wont have to change your stored proc.
If that is not a possibility you could use either isNull or Coalesce functions to replace the null value with the appropriate date of your choosing
good luck
July 24, 2008 at 7:32 am
HUH? I just need to know how to check the ranges.
July 24, 2008 at 7:40 am
Would this work correctly?
AND (((@PaymentDtTmStart IS NULL)
AND (@PaymentDtTmEnd IS NULL))
OR (IF (@PaymentDtTmStart IS NOT NULL) AND (@PaymentDtTmEnd IS NULL)
PaymentDtTm BETWEEN @PaymentDtTmStart AND CURRENT_TIMESTAMP)
OR (PaymentDtTm BETWEEN @PaymentDtTmStart AND @PaymentDtTmEnd))
July 24, 2008 at 7:46 am
Sorry I should have been more specific
This will replace the null PaymentDtTmStart in your stored proc with a minimum date and your
PaymentDtTmEnd with today's date .
PaymentDtTm BETWEEN isnull(@PaymentDtTmStart,'1/1/1900') AND isnull(@PaymentDtTmEnd,getdate())
July 24, 2008 at 7:55 am
Just beware of parameter sniffing. Taht format of where clause tends to have very bad plan reuse and intermittent bad performance.
For structures like that, WITH RECOMPILE may be necessary.
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 24, 2008 at 7:59 am
if I include both dates, it returns all rows instead of only records in the range
if I set the end date as null, it returns all rows.
There are 946000 records in the table and it take 30+ seconds to return results. I'm pretty sure that I need an index.
July 24, 2008 at 8:00 am
MrBaseball34 (7/24/2008)
Would this work correctly?
AND (((@PaymentDtTmStart IS NULL)
AND (@PaymentDtTmEnd IS NULL))
OR (IF (@PaymentDtTmStart IS NOT NULL) AND (@PaymentDtTmEnd IS NULL)
PaymentDtTm BETWEEN @PaymentDtTmStart AND CURRENT_TIMESTAMP)
OR (PaymentDtTm BETWEEN @PaymentDtTmStart AND @PaymentDtTmEnd))
Don't use "IF" in Where. Doesn't work that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 8:10 am
MrBaseball34 (7/24/2008)
There are 946000 records in the table and it take 30+ seconds to return results. I'm pretty sure that I need an index.
Probably. The problem is with the multiple (variable is null or column is null or variable = column) constructs in the where clause you're highly unlikely to get effective index usage. You will probably get a table scan to evaluate that query.
Worse, if you do get an efficient plan for the first execution, that plan will get cached and will be ineffective for subsequent calls with different parameters.
With that kind of search query, you may need to create the proc with recompile, or you may need dynamic SQL.
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 24, 2008 at 8:14 am
I'm really kind of a newbie. The SP was generated by a tool that is used here and I am only modifying it to try to make it more efficient and work correctly because it was not generated to use a range of dates on the PaymentDtTm, DepositDtTm, and LedgerDtTm columns.
I do not know what you are talking about and don't know what RECOMPILE does or means AFA the SP is concerned, I don't understand what it is for.
July 24, 2008 at 8:17 am
GilaMonster (7/24/2008)
Just beware of parameter sniffing. Taht format of where clause tends to have very bad plan reuse and intermittent bad performance.For structures like that, WITH RECOMPILE may be necessary.
This place, I just joined here less than a month ago, has tons of SPs structured like that.
They have a tool that generates their SP code from their tables and they don't modify them much at all.
July 24, 2008 at 11:04 am
Any more tips on optimizing SP's where the WHERE clause is formatted like that?
July 24, 2008 at 11:11 am
MrBaseball34 (7/24/2008)
GilaMonster (7/24/2008)
Just beware of parameter sniffing. Taht format of where clause tends to have very bad plan reuse and intermittent bad performance.For structures like that, WITH RECOMPILE may be necessary.
This place, I just joined here less than a month ago, has tons of SPs structured like that.
They have a tool that generates their SP code from their tables and they don't modify them much at all.
My condolences to you, your server and your users. 🙂 😉
Since there's no changing that, ignore my comments. Maybe in the future if the system hits performance problems you can be the hero by telling them a possible cause. 😀
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 24, 2008 at 3:41 pm
GilaMonster (7/24/2008)
Since there's no changing that, ignore my comments. Maybe in the future if the system hits performance problems you can be the hero by telling them a possible cause. 😀
I guess I could if I knew *what* to tell them.
Any more hints on how to optimize this thing?
July 25, 2008 at 12:59 am
MrBaseball34 (7/24/2008)
I guess I could if I knew *what* to tell them.
🙂 Basically, that form of query has no single optimal plan and it tends to confuse the query optimiser. Basically, you'll probably get table scans when it's being evaluated.
Any more hints on how to optimize this thing?
If you can't change the code, probably not.
Can you post table design and current indexes please? Also, if possible, save the execution plan as a .sqlplan file, zip it and attach it to your post
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
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply