Speeding up date range searches
Here has been my experience with date range searches
with OnLine 5 on an NCR box:
With the tables appropriately indexed so lookups for a
single date are quick, the following lookups where slow:
select *
from table
where date1 between value1 and value2
or
select *
from table
where date1 >= value1
and date1 <= value2
even after updating statistics on the table.
The following idea increased by lookup speed several fold,
I know it's ugly but it works and it's fast.
Change the lookup to use individual dates by selecting the
date value in a list as follows:
select *
from table
where date1 in (value1,.....,value2)
This is a pain to do in SQL, but the speed is worth it.
As for 4GL, I do the following:
When a fixed number of days is needed, a week for example,
I prepare and declare a cursor as follows:
let sql_string = "select * from table",
" where date1 in (?,?,?,?,?,?,?)"
and then prepare/declare and open the cursor with the
appropriate dates.
When a variable number of days is needed, I write a function
that assigns the needed dates to a string then prepare/declare
and open the cursor.
call set_date_string()
returning date_string
let sql_string = "select * from table where date in ",
date_string
I know they are not very elegant, but they are both generic
and work well and fast, well faster than the original select;^).
Any question postem or e-mail'em to me
=====================================================================
Paul Watje Database Something or Another
watjep@hasting.com Hastings Books, Music, & Video