|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.