SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 query slowness
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vish
Starting Member

1 Posts

Posted - 04/28/2006 :  08:44:42  Show Profile  Reply with Quote
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)

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/28/2006 :  09:13:39  Show Profile  Reply with Quote
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
Go to Top of Page

druer
Constraint Violating Yak Guru

USA
314 Posts

Posted - 04/28/2006 :  14:33:26  Show Profile  Reply with Quote
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,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000