Re: Query takes 895ms in TOAD but 42s in DBI

I don't think you are comparing like with like here.

Outputting data to the terminal is never a good test of speed. Try it without the print to take this out of the equation.

Also, why bother doing all those tests of NULL to change the returned value to Unknown when you can use coalesce in your SQL select and let oracle do it.

Comment on Re: Query takes 895ms in TOAD but 42s in DBI

Replies are listed 'Best First'.
Re^2: Query takes 895ms in TOAD but 42s in DBI by joec_ (Scribe) on Dec 02, 2008 at 16:31 UTC
Thanks for the replies. I will get round to profiling it when i can get the module installed. I cant use coalesce as i dont have access to the schema. Even just doing the query without printing to stdout or files takes 42 seconds. regards, Joe	[reply]
Re^3: Query takes 895ms in TOAD but 42s in DBI by dragonchild (Archbishop) on Dec 02, 2008 at 22:38 UTC
COALESCE is a function you put into your query that takes the first non-NULL value. As for what's going on ... You said this returns a cursor. What happens in TOAD if you subsequently run through all the rows in the cursor? I suspect that DBD::Oracle is doing just that while TOAD isn't. My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply]