RE: SQL Date Math

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Thanks for posting this Michael. It goes to prove little performance tuning
can go a long way.

As for the difference between UDF & inline code, I knew there is some
overhead to call a UDF but it's hard to quantify without benchmarks.  Some
of it can be eased by choosing certain CREATE FUNCTION arguments carefully.
See page 4 of this Centerfield newsletter for an example:

http://www.centerfieldtechnology.com/publications/archive/Oct%202006.pdf

Bottom line is that going through 56M records in 2 seconds is pretty darn
good. Way to go DB2 for i5/OS!

Elvis

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
http://centerfieldtechnology.com/training.asp
 

-----Original Message-----
Subject: Re: SQL Date Math

That was interesting. Here are the results.

All run in interactive SQL, approximately 56M records. Index built
over the numeric field (which took a freaking long time btw - like 10
minutes).

Using CvtDateToNumISO function with no index - 45 seconds
Using function code with no index - 38 seconds
Using CvtDateToNumISO function with index - 11 seconds
Using function code with index - 2 seconds