|
|
|
Rank: Member
Groups: Member
Joined: 5/1/2009
Posts: 18
Points: 54
|
Hi,
When I perform the following query:
SELECT BookingServiceId FROM BookingServices WHERE StartTime > '10 Jan 2010'
The result is that the db runs an Index scan over StartTime and returns the results.
However, as soon as I run the below query where I add 10 minutes to the StartTime, VistaDB then uses a table scan on BookingServices:
SELECT BookingServiceId FROM BookingServices WHERE dateadd(mi, (10 * (-1)), StartTime) > '10 Jan 2010'
My issue is that the table scans are particularly slow given that there are over 10000 rows in the table. Is there a way to get VistaDB to use the Index with the result of the DATEADD function, or is there possibly some other workaround for this issue?
Thanks,
Gary
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
I cant remember the sytax off the top of my head, but I would calculate the constant and pass that to the SQL statment and have SELECT BookingServiceId FROM BookingServices WHERE StartTime < [calculated date] That way the index would be used.
|
|
|
|
Rank: Member
Groups: Member
Joined: 5/1/2009
Posts: 18
Points: 54
|
Hi David,
Thanks for the suggestion, unfortunately the additional time is not a constant (I simplified the example to make it easy to understand). Each BookingService has a definable Start and End time, to which I must add a resource recovery or prep time. So unfortunately your suggestion will not work.
Regards,
Gary
|
|
|
|
Rank: Team VistaDB
Groups: Member, Team VistaDB
Joined: 8/13/2006
Posts: 515
Points: 2,289
Location: Edinburgh, Scotland
|
Send an example of the complete SQL and surrounding code that determines the the value passed to the dateAdd function
|
|
|
|
Rank: VistaDB Staff
Groups: Administration, VistaDB Staff
Joined: 8/13/2006
Posts: 3,372
Points: 3,911
Location: Mount Dora, Florida
|
You can always do the calc before the select in a variable. Code:
declare @datetimetarget;
set @datetimetarget = ....;
select ... where @datetimetarget > 'YYYY-MM-DD';
And incidentally I think this behavior was change in VDB4 to allow temp inline variables to use indexes. Jason Short
|
|
|
|
Rank: Member
Groups: Member
Joined: 5/1/2009
Posts: 18
Points: 54
|
Hi guys,
Thanks for the feedback. I'll give that a try. In fact, I'll move to VistaDB and see what the behaviour is there.
Regards,
Gary
|
|
|
|
Guest
|
YAFPro Theme Created by Jaben Cargman (Tiny Gecko)Powered by YAF 1.9.3 |
YAF © 2003-2009, Yet Another Forum.NETThis page was generated in 0.061 seconds.