Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 query slowness

Author  Topic 

Starting Member

1 Post

Posted - 2006-04-28 : 08:44:42
Hi. I am basically executing a query from a java pgm. I stored the query in String buffer and conv. it to a string and using prepared stmnt to executeQuery() (no loop). the query took 40 secs to exec. but when i ran the same in Microsoft SQL query analyser, it took only 3 secs.

I dont use views, i use tables.
with all the keys in tact and in proper order

any guess what could be wrong.. because i dont feel there could be a huge diff in time b/w query analyser and a java pgm DB execution !!

i have about 48 inputs (?) in the WHERE clause and query is quite long (about 200 liner)


22859 Posts

Posted - 2006-04-28 : 09:13:39
If you run the query in Java, and then run the exact same query in Query Analyser (or anything else - including again in Java, then the query plan will be cached, the data will be in memory, etc., and this can make the query faster (sometimes even an order of magnitude faster!).

Do you think that could be the situation here?

If not then perhaps the communications layer from Java is doing some ghastly cursoring deal, which Query Analyser isn't - I'm not familiar with Java, but in the languages that I do use it is possible to influence how a recordset is processed by the communications layer - e.g. "Get the whole lot as quick as you can" or "Get it one row at a time, using lots of overhead"!!!

Go to Top of Page

Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-28 : 14:33:26
If the result set is huge and has to be transmitted from the server to the Java machine, whereas the Query Analyzer session is on the server then you could probably also see a pretty significant difference.

Other idea might be that when the command is executed by Java with as many where criteria as you have it may have wrapped something in quotes that you didn't specifically say to do so the optimizer can't use an index or something that it can perhaps the Query Analyzer.

I would recommend that you turn on the SQL Profiler and try running them both, and then run the Query Analyzer first and the Java second and see what kind of data you are getting in the profiler.

Hope it helps,

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page

- Advertisement -