AW: AW: SQL - Calculate the number of hours between 2 timestamps

Hi,

I suspect something else.

Matt, could you please post your select statement with the UDFs?

It depends on how the statement looks like. It could be that instead of
using an index a table scan gets performed. Indexes can only be built over
original columns of the table. If a column gets changed, i.e. using a UDF,
an index over the column cannot be used anymore.

In the following example and index over MyDate cannot be used and a tables
scan gets performed:
Select * From MyFile 
   Where CvtNumToDate(MyDate) = Current_Date

In the following example an index could be used, but the UDF to convert the
Current Date must be called for each row.
Select * From MyFile 
   Where MyDate = CvtDateToNum(Current_Date)

If you use these statements in embedded SQL, it's much better to determine
the numeric date that is compared with MyDate in a separate statement. Also
a convertion of the selected dates should be performed in separate
statements:

Example in a SQL described stored procedure:

Set NumDate = CvtDateToNum(Current_Date);
Declare MyCsr Cursor For
   Select  MyDate, MyFld1, MyFld2
      From MyTable 
      Where MyDate = NumDate;
Open MyCsr;

If a table scan will be performed instead of an index access, creating a
view won't help either. When using a view the SQL-Statement gets rewritten
with the information stored in the view and after a temporary table (view
materialization) may be built and populated. 

Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
Gesendet: Donnerstag, 29. Juni 2006 19:37
An: Midrange Systems Technical Discussion
Betreff: RE: AW: SQL - Calculate the number of hours between 2 timestamps

An alternative might be to create a view that uses that formula.  Then you 
could just query that view.

Rob Berendt