Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vish
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 orderany 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) |
|
|
Kristen
Test
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"!!!Kristen |
 |
|
|
druer
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,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
|
|
|
|
query slowness