| Author |
Topic |
|
k_cire0426
Yak Posting Veteran
63 Posts |
 Posted - 2009-06-18 : 13:15:16
|
| Hi, There's a weird thing happening in my filtering.If I used the query below it runs so fast.SELECT * FROM vyu_Sample WHERE dtmDate BETWEEN '1/1/1900' AND '6/18/2009'but when I used this, it takes too long to execute until it hangs.SELECT * FROM vyu_Sample WHERE CAST(FLOOR(CAST(dtmDate AS FLOAT)) AS DATETIME) BETWEEN '1/1/1900' AND '6/18/2009'Is there a difference between those queries? Even if i just "Select * from vyu_Sample" the view runs smoothly.Any idea? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-18 : 13:25:47
|
Yes, you are applying a function to a column which makes it impossible for SQL to use an index. Are you trying to strip off the time portion from the date? If so you should change your criteria to something like: SELECT * FROM vyu_Sample WHERE dtmDate >= '19000101' AND dtmDate < '20090619' |
 |
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2009-06-18 : 13:49:51
|
| oh i see..If that's the case I think I should convert the date to the format you did.. I tried to remove the FLOOR function and it run smoothly. I guess it's on the FLOOR function which I don't know why. What do you think the reason?SELECT * FROM vyu_Sample WHERE CAST(CAST(dtmDate AS FLOAT) AS DATETIME) BETWEEN '1/1/1900' AND '6/18/2009'Thank you lamprey. :) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-18 : 14:28:25
|
| Exactly, the FLOOR function has to be applied to EVERY row in the view. So, assuming there is an index on that date column, sql cannot use that index to look up that date range. Thus, sql is table/clustered index scanning and you are seeing poor performance. |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2009-06-18 : 14:46:32
|
| here's another, i tried to add another filter which is ysnPaid = 'False' and the script run smoothlySELECT * FROM vyu_Sample WHERE CAST(FLOOR(CAST(dtmDate AS FLOAT)) AS DATETIME) BETWEEN '1/1/1900' AND '6/18/2009' AND ysnPaid = 'False'but if ysnPaid = 'True' it hangs again T_TSELECT * FROM vyu_Sample WHERE CAST(FLOOR(CAST(dtmDate AS FLOAT)) AS DATETIME) BETWEEN '1/1/1900' AND '6/18/2009' AND ysnPaid = 'True'This thing is really confusing. I don't know what to change,is it the filtering or the VIEW itself? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-18 : 16:17:18
|
Casting as FLOAT and then back to DATETIME has no effect on time portion!Why persist in doing things more complicated than necessary?SELECT * FROM vyu_Sample WHERE dtmDate >= '1/1/1900' AND dtmDate < '6/19/2009' AND ysnPaid = 'True'
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-18 : 16:52:48
|
quote:
Originally posted by Peso
Casting as FLOAT and then back to DATETIME has no effect on time portion!
The FLOOR does get rid of the time portion. But agreed, keep it simple! :) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-18 : 16:58:27
|
I referred to the post made 06/18/2009 : 13:49:51
E 12°55'05.63"N 56°04'39.26" |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2009-06-18 : 19:11:06
|
| I tried to remove the floor but there are dates that doesn't remove the time. |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2009-06-18 : 19:30:40
|
| I guess there is something wrong with the data because when i ran the query on larger database it execute smoothly. thank's for all your help if you have some idea please tell me. :) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-19 : 13:17:11
|
quote:
Originally posted by k_cire0426
I tried to remove the floor but there are dates that doesn't remove the time.
Can you give more detail on that? I'm not sure I understand.If you want to get a range of rows based on dates, there is no need to strip of the time from the column. Rather you change the range you are searching for to accomodate the time. So for example, you wanted dates from '1/1/1900' to '6/18/2009'. Assuming that you want ALL the rows on 6/18/2009 instead of using BETWEEN use >= '1/1/1900' and < 6/19/2009. Notice that we are looking for dates less than 6/19. That will pick up every date in your range without having to manipulate the date column.Unless of course you are trying to retun the date in the result set with the time portion removed.. Then that is a different issue. |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2009-06-19 : 14:15:54
|
| Hi guys I'm here again with weird thing or maybe i'm still noob in the SQL :) please bear with me. My problem before is the poor performance of this query(30 mins to execute T_T)SELECT * FROM vyu_Sample WHERE cast(floor(cast(dtmdate AS float)) AS datetime) BETWEEN '1/1/1900' AND '6/19/2009'which is my purpose is to print all the paid and not paid transaction.Here's another question, why does this query executes smoothlySELECT * FROM vyu_Sample WHERE cast(floor(cast(dtmdate AS float)) AS datetime) BETWEEN '1/1/1900' AND '6/19/2009' AND ysnPaid = 0 or ysnPaid = 1They are the same data output. Why is it like that? Expert out there please advise.. @_@ lamprey, my purpose of that casting is to eliminate the time. Because if we have a criteria like this between cast(floor(cast(dtmdate AS float)) AS datetime) BETWEEN '6/19/2009' and '6/19/2009' the query will not returned all the records that has a time on it. That is the usual way we are doing in our application when creating criteria if the user chose the criteria "TODAY" on our application. :) |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-06-19 : 14:30:00
|
| I'm assuming the startdate and enddate are being passed in as parmatersInstead of between use >= and <WHERE dtmdate >= dateadd(day,datediff(day,0,@startdate),0) and dmtdate < dateadd(day,datediff(day,0,@enddate),1)Jim |
|
|
|
k_cire0426
Yak Posting Veteran
63 Posts |
Posted - 2009-06-19 : 14:38:46
|
| Hi Jim, No, we're not passing any parameter. We're just building the query string by concatenating in VS. ^_^ |
|
|
|
|
Weird Criteria