BETWEEN operation on TIMESTAMP fileds

[Dave]

The following function is called repeatedly from a stored proc.

CREATE FUNCTION GetTaxRate
(
v_AsOfDate TIMESTAMP,
v_FilingStatusI d INT)
RETURNS DOUBLE
BEGIN ATOMIC
DECLARE v_retVal DOUBLE;
SET v_retVal =(select min(taxrate) FROM Tax_Table
WHERE (v_AsOfDate BETWEEN TaxYearStart AND TaxYearEnd) );

RETURN v_retVal;
END

The stored proc takes about 2 minutes to run (expected response time : 1
sec)
If I comment out the WHERE clause in the above function (just for testing),
the proc runs under 1 second.
If I run the function directly, it is quite fast (fraction of a second).

Tax_table has got just 70 rows in it. TaxYearStart & TaxYearEnd or timestamp
fields.
Not able to figure out what is going on with 'BETWEEN' operation on
timestamp filelds.

Ver : DB2 V8.1 FP3 (WIN)

[Serge Rielau]

Hi,

This should be fixed in FP4. The problem is that access to local
variables/arguments was not (always) considered to be eligible as
index-start-stop key.

Sidenote: You don't need BEGIN..END for this trivial function.
There is a fat chance that:

CREATE FUNCTION GetTaxRate
(
v_AsOfDate TIMESTAMP,
v_FilingStatusI d INT)
RETURNS DOUBLE
RETURN (select min(taxrate) FROM Tax_Table
WHERE (v_AsOfDate BETWEEN TaxYearStart AND TaxYearEnd));

will work fine in FP3 (and get nicely optimized along the way :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Dave]

Serge, thanks for your reply.
I tried installing FP4 but it didn't make any difference.
Tax_Table is too small a table (just < 70 rows) and even if the optimizer
chooses to do tablescans, it shouldn't make any difference.
What is puzzling me is that if I replace v_AsOfDate by a constant (eg.
timestamp('2003-10-10-00.00.00')) in the WHERE clause,
it is very fast.

Thanks
"Serge Rielau" <sr*****@ca.e ye-bee-m.com> wrote in message
news:bt******** **@hanover.toro lab.ibm.com...

Hi,

This should be fixed in FP4. The problem is that access to local
variables/arguments was not (always) considered to be eligible as
index-start-stop key.

Sidenote: You don't need BEGIN..END for this trivial function.
There is a fat chance that:

CREATE FUNCTION GetTaxRate
(
v_AsOfDate TIMESTAMP,
v_FilingStatusI d INT)
RETURNS DOUBLE
RETURN (select min(taxrate) FROM Tax_Table
WHERE (v_AsOfDate BETWEEN TaxYearStart AND TaxYearEnd));

will work fine in FP3 (and get nicely optimized along the way :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

Dave wrote:

Serge, thanks for your reply.
I tried installing FP4 but it didn't make any difference.
Tax_Table is too small a table (just < 70 rows) and even if the optimizer
chooses to do tablescans, it shouldn't make any difference.
What is puzzling me is that if I replace v_AsOfDate by a constant (eg.
timestamp('2003-10-10-00.00.00')) in the WHERE clause,
it is very fast.

This is probably a dumb question, but do you have accurate statistics
on your table TAX_TABLE?

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Serge Rielau]

That is odd.
General comment:
Is this an Oracle migration/port? I'm asking because your names suggest
DATE, but you chosen datatype is TIMESTAMP which is the default MTK
mapping. If you have a chance you should use DATE and remove all this
funny code required in Oracle to normalize the time component.
DB2 will thank you for it is decent performance :-)

W.r.t. you specific problem we need more context. Hwo is thsi fucntion
used? Can you provide an explain?
To get an explain of all statements (save SESSION referencing ones):
db2set DB2_SQLROUTINE_ PREPOPTS="EXPLA IN ALL"
(restart DB2)
db2 connect to ....
db2 -tvf sqllib/misc/EXPLAIN.DDL
re-CREATE PROCEDURE

db2exfmt -d <dbname>
chose default for all input except for "output file"

Find the bad statement in the generated file and we'll take a peek.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab