October 1, 2007 at 8:31 am
Nilssond (9/27/2007)
I have a table (MyTable) with multiple eligibility segments for members. Typical data looks like this:MemberNumber StartDate EndDate Role
12345 20070101 20070131 A
12345 20070201 20070331 A
12345 20070401 0 A
98765 19991201 0 A
Is there any inherent problem with the following TSQL which attempts to find all members who were in Role A for a specific date. The UDF dbo.ZtoEOT transforms 0 to 20991231, if present, otherwise returns the input date/ The criterion date is 20070801
Select MemberNumber, StartDate, EndDate from Mytable where
20070801 between StartDate and dbo.ZtoEOT(EndDate) or StartDate 20070801
I would expect the last 2 records to appear in the results
One thing you need to remember is that using a function on a column in the Where clause will likely cause a table or index scan instead of a seek. I would think you would be better off NOT using between in this case and doing something like:
Where
StartDate = 20070801)
I would think that this would meet any case you encounter and that you do not need the "or StarDate = 20070801" because EndDate is either going to be 0 or at least the same as the start date.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2007 at 9:22 am
Since you seem to be stuck with the date in a non-date format...
What if you had your where statement like this:
WHERE 20070801 > StartDate
AND (20070801 <= EndDate or EndDate = 0).
There would be no need to convert the end date to a date in the future, since it would pull them if the EndDate = 0.
October 1, 2007 at 9:25 am
Doesn't the or in the end kill any change of an index seek?
October 1, 2007 at 9:59 am
If both columns involved in the OR are indexed (separate indexes), there's still a chance of a seek. Not a guarantee.
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
October 1, 2007 at 10:07 am
In this case : Where MyCol < 'whatever' OR MyCol = 0
One implies a range seek and the other one an index seek (rid lookup). I don't see how the optimizer can figure out an easy seek for this one... I'm sure it can figure this out as an ok plan, but it doesn't jump in your face that it's the best plan for all possible values, all the time. the "easy" plan for the optimizer would seem to be a scan as it will always return the correct results with at worst a index scan. This is far less costly than making a lot of rid lookups.
October 1, 2007 at 11:40 am
Ninja's_RGR'us (10/1/2007)
In this case : Where MyCol < 'whatever' OR MyCol = 0One implies a range seek and the other one an index seek (rid lookup). I don't see how the optimizer can figure out an easy seek for this one... I'm sure it can figure this out as an ok plan, but it doesn't jump in your face that it's the best plan for all possible values, all the time. the "easy" plan for the optimizer would seem to be a scan as it will always return the correct results with at worst a index scan. This is far less costly than making a lot of rid lookups.
My post was supposed to include the same where clause as Carla has posted with the exception that I used ">= StartDate". I also wondered about the query plan using the "OR", but I knew the function would cause a scan and also eliminating the function makes the query more readable (in my opinion).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2007 at 12:02 pm
I wish I could answer that one without testing but I can't say for sure.
One thing I can confirm is that would not be surprised at all to see this code force a scan of some sort. Maybe someone else has time to test or knowledge to confirm this one...
October 1, 2007 at 3:06 pm
I think you guys scared off the OP.
The comment was already made that it appears you are using integer values for datetime values and that this is generally a Bad Idea. However, your use of the function does not effect performance as it does not affect the SARGability of the Where clause. If StartDate is indexed, it is the only index that is required and the only one that would be used.
However, in my testing, that index was not used -- the optimizer insisted on doing an index scan on the primary key. But, the use of a hint worked well:
Select MemberNumber, StartDate, EndDate
from Mytable with (index (IDX_MyTable_StartDate))
where 20070801 between StartDate and dbo.ZtoEOT(EndDate)
Actually, my query ended with "between StartDate and IsNull( EndDate, GetDate() )" but you get the idea.
So the answer is yes, your query works fine -- except for the "or StartDate 20070801" that you included in your example. I don't know what that is, I just assume it was pasted in your post by mistake.
However, if I may offer a suggestion. Consider the following query:
Select MemberNumber, StartDate, EndDate
from Mytable with (index (IDX_MyTable_StartDate))
where StartDate <= 20070801 and (EndDate = 0 or EndDate >= 20070801)
This has the same execution plan and produces the same results. But it eliminates the use of a function altogether (a little savings in overhead) and it makes the clause more readable. It is a little more clear to us humans what you are trying to do. It also eliminates the use of an arbitrary date that specifies "the future stops here!":P
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 1, 2007 at 3:11 pm
If I may add something. It is not wise to counter-guess the execution plans made by the optimizer. I know we sometimes get better results, but we don't always have enough data to make that decision final.
I'm curious, which query gets the most reads overall?
October 1, 2007 at 6:38 pm
... and what about the EndDates that are NULL? 😉
--Jeff Moden
October 2, 2007 at 10:27 am
... and what about the EndDates that are NULL? 😉
I answered that already. I defined my test table with a nullable datetime field. However, the OP seemed to be using a integer with zero meaning null so that's how I tailored my answer. It's a minor modification to go from one to the other.
Not that it matters. OP seems to have vanished. We're just talking to ourselves now.:unsure:
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 2, 2007 at 10:50 am
This is still important for the next DBAs who will need this thread.
This is what makes the difference between a good site and a great community.
October 2, 2007 at 6:08 pm
Tomm Carr (10/2/2007)
I answered that already. I defined my test table with a nullable datetime field. However, the OP seemed to be using a integer with zero meaning null so that's how I tailored my answer.
Ah... thanks Tom.
--Jeff Moden
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply