executeQuery taking six times as long to run as opposed to when query is run in TOAD

I inherited a...well, I guess I can call it a piece-of-#### Struts application, and am tasked with optimizing a Levey-Jennings process that checks if our quality control standards are up to snuff.

The process itself runs fine, but there has always been a huge spike in performance time even if the dat开发者_开发技巧aset is small. I tested time between each part of the algorithm and discovered that the big time hog was Java's executeQuery() method.

Most recently I ran the application and logged the execution time to be 10 seconds. The executeQuery() took six of those seconds by itself. Curious to see what the problem was, I took the query into TOAD and ran it verbatim -- it only took 1 second to run.

I ran an even larger dataset, which took 60 seconds to run in the Levey-Jennings application -- however, in TOAD, it took 10.

Is this a problem with the query at all, or is using executeQuery() typically a precursor to extreme slowdown?

When you run a query in TOAD (or any other IDE), this tool wants to provide you with the results you can see, as fast as possible. Typically they show you a grid with between 10 or 40 rows. To show you those first 10-40 rows as fast as possible, they hint the query or change the optimizing environment to produce those first rows as fast as possible.

Here you can see more information about the FIRST_ROWS hint: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF50302

The query in your application likely doesn't use a FIRST_ROWS hint. It wants ALL the rows as fast as possible. It doesn't care if the first row shows up immediately. So, the optimizing environment for those two queries is different.

It also doesn't help that TOAD displays the time it took to produce the first rows, because it leads you to think that that's the time it takes to get all the rows. There is an option to navigate to the last row, though. Press that and you'll see that it now takes longer.

Hope this helps.

Regards,
Rob.