Re: date(year to second field)

In article <35474E2C.7715@bloomberg.com>, Art S. Kagel <kagel@bloomberg.com> writes >Sukumar Konduru wrote: >> >> Hi >> >> I have table with field "year to second". I need to prepare reports >> quiet >> oftern the transactions on day basis. >> >> I would like to know which is efficient to use. >> >> select * from taskresult >> where date(startedat) = today>> >> or >> select * from taskresult >> where startedat >="1998-04-29 00:00:00" and>> startedat <="1998-04-29 59:59:59" > >You would have to benchmark both to be sure but the second is faster in >theory because in the second query the two date strings will each be >converted to datetime year to second and each row will be compared >natively as a datetime. In the first version every row will have to be >converted to type date before comparing to the value of today. (BTW >for 7.xx versions prior to 7.13 this does not hold. In early 7.1x >versions comparisons between datetime and strings were done by >converting each row to a string, 7.13 fixed this.) > >Factors affecting actual performance include: >- the number of rows, for a smaller table the overhead of the repeated > conversions may not matter. >- whether an index can be used for the second query, the first query > cannot use an index for the date comparison and so will do a table > scan unless other keys can use indexes. Even then every matching row > will have to be fetched and tested (unless a key-only index is > available). Note the phrasing; versions before 7.24 WILL NOT use > index keys beyond the third indexed column for index filtering and > will fetch the datapage anyway (again except for key-only searches). Where did you hear this? > >These and other complications mean that you will have to benchmark it >yourself. > >Art S. Kagel -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care