Anybody Knows The Black Magic Behin SPL Performance?

Sounds like you've done quite some research into the whole SPL speed issue. You discovered the 'time' bug - namely that 'CURRENT' in an SPL is likely to be some time near when you set it, but if you set a start time and an end time in the procedure they will most likely be the same times if not reversed. You also don't mention whether you've been doing update statistics on the SPL itself. An SPL is optimized at the time it is created and when statistics are updated for it. So if you create an SPL against a table which has 1 row the optimizer will probably figure it's fastest to read the whole table rather than navigate the indices. This can be interesting when you get up to a million rows in the same table. Someone, who's name shall remain blameless here, mentioned to me early on that SPL's don't scale well. We have certainly had some culprits here which I've had to go back over and optimize or create indices for. Even so our use of SPLs has been for convenience and not for performance. In your testing you mention ensuring that the SPL had nothing in it. I assume this means you dropped and re-created the procedure? Be careful here. You may drop a procedure, but it does not necessarily clean it out of memory. You may create the new procedure and then execute it only to have the procedure you just dropped run. About the only way we have found to be safe here is to end the session which originally invoked the SPL - that doesn't make a lot of sense. You might also consider limiting what you do with SPL. It's great for those hard to reach places, but designing a system around extensive use of SPLs is not a great idea. This concludes my rambling on this topic for the next several minutes. cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com <--- New address Currently on loan to PLD/PE _____________________________________________________________________________ If anything can go wrong, fix it. To hell with Murphy. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________