April 14, 2011 at 10:33 am
I have the following query:
SELECT mOnCallAdd.OpName,mOnCallAdd.SchedName,mOnCallAdd.FirstListing, moncallAdd.Initials,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) as OnCallDate,
DATEADD(MINUTE, mOnCallAdd.duration, DATEADD(MINUTE, mOnCallAdd.StartOnCallTime, DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) as OnCallEnd
FROM mdr.dbo.mOnCallAdd
where DATEADD(DAY, mOnCallAdd.StartOnCalldate, '12/31/1899')= @currentdate and moncalladd.schedname =@schedname
ORDER BY OnCallDate
and what I'm trying to do is that if a timestamp falls within the startoncalltime, the startoncalldate matches the currentdate that the query will present the results back to me. A web page will be supplying the variable of datetime to the query and currently I am only able to produce these results:
ONCALL3CTKACTKA- JONES, STEPHENEE2011-04-11 07:00:00.0002011-04-11 17:00:00.000
ONCALL3CTKACTKA - BIGGS EE2011-04-11 07:00:00.0002011-04-11 17:00:00.000
can someone offer me a way to match on date/time and timestamp please.
Thank you
Doug
April 14, 2011 at 12:39 pm
IMHO your query points to a design flaw.
There is no way this kind of queries will be able to produce a well performing query, based on datetime data, just because it is designed to be stored by minutes (offset 0) and days offset 1899-12-31.
Add a persisted calculated column that holds the calculated date and time or revert your @timestamp parameter to a value usable with your days offset and put an index on that column.
Store the data in the correct datatype so all nice datatype supporting functions are available to you without the need of in line conversions.
Don't use functions on columns, if you can port the function to the side that holds your @timestamp and revert that to a format/datatype so it matches your columns data type.
another thing to keep in mind is to always use convert when converting characters to datetime datatype and to specify the format you are using !
The same goes for your variable that is holding your timestamp. double check it is using a datetime data type and not accepting a string. Your content may be affected by client settings for representing datetime data !
Use the datetime data type as soon as possible !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2011 at 12:57 pm
ALZ,
Could you offer me an example to clean up my query? I'm not the dba but I'm trying to make my queries as efficient as possible.
Thank you
Doug
April 14, 2011 at 1:09 pm
One thing you can do with your current setup is alter the where clause.
where mOnCallAdd.StartOnCalldate = DATEdiff(dd, convert(datetime,'12/31/1899', 101),@currentdate)
This way, if there is an index on your column StartOnCalldate in the table mOnCallAdd, there is a chance that the engine will use that index to filter your query.
But I urge you to contact your DA/DBA to have a look at the other suggestions. Chances are you / your app will not be the only one having benefits with the suggested modifications.
(edited to use qoute in stead of code=sql because the statement didn't render well)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 14, 2011 at 1:28 pm
Johan is correct, you don't want a function on the column value or you don't use indexes and must scan the table.
For the time, people often do this:
where mydatevalue >= @currentdate
and mydatevalue < dateadd( day, 1, @currdate)
For today, Apr 14, 2011, this should result in the following if you are using calculating today's date:
where mydatevalue >= '20110414 00:00:0000'
and mydatevalue < '20110415 00:00:0000'
This gets anything with a time on Apr 14.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply