File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes prepared statement issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepared statement issue" Watch "prepared statement issue" New topic
Author

prepared statement issue

Vishravars Ramasubramanian
Greenhorn

Joined: Apr 28, 2006
Posts: 7
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 M$ 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 !!
Vishravars Ramasubramanian
Greenhorn

Joined: Apr 28, 2006
Posts: 7
sorry for the continuation. i missed a small point.

i have about 48 inputs (?) in the WHERE clause and query is quite long (about 100 liner)
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

Vishravars,
Welcome to JavaRanch!

You could use the database's statistics to see the query runtime when executed through the Java program. A common cause is a huge result set. The network traffic of a large response eats up a lot of time.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Vishravars Ramasubramanian
Greenhorn

Joined: Apr 28, 2006
Posts: 7
Yes. I used the M$ sql profiler and took the trace. it shows 40secs for the query executed through java pgm and 3 secs through query analyser. and there is not much n/w traffic to my amazement. i tried that with a linux tool.

will there be any prob in the case of too much arguements (43 in total) and it has many sub queries in the select list awaiting input at runtime
Masoud Kalali
Author
Ranch Hand

Joined: Jul 08, 2004
Posts: 531

Which JDBC driver and MSSQL server version you use ?
as Jeanne said, How much big your result set is ?

can you check one of queries that you execute in q analyzer
by using an statement instead of a prepared statement ?
I mean fill in all parameters and let the JDBC just execute the statement.

ps : i do not think network traffic cause this problem.


Masoud Kalali
Software Engineer - My Weblog - GlassFish Security
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30580
    
154

Originally posted by Masoud Kalali:
i do not think network traffic cause this problem.

I agree. If the SQL profiler showed 3 sec and it took 40 sec to return to Java, it would be the network. But since the profiler is showing 40 sec, it has to be something else.
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Possibly the query plan stinks. It might be building a better query plan for the one off execution vs a generic one.

Here are some things I would look at and play with:

- One by one test the various parts of your where clause. Just to see. I suspect that one or two of your clauses are the issue.

- What driver are you using? How is it actually executing the query? If you execute the same prepared statement twice is the executing faster the second time? I wonder how much time might be being lost in preparation here.

But I suspect the problem is likely the query plan. Sounds like the database is using an index where a table scan would be faster or vice versa. A crazy thing about indexes is that too many can really make things bad. Especially if the statistics for the table/index are out of date.

Try and see what the problem column or columns are. Then see if you have indexes on them.
Vishravars Ramasubramanian
Greenhorn

Joined: Apr 28, 2006
Posts: 7
ya. sure. i will try with both

embedded where clause options
and try all the queries atomically.

thanks for the response
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: prepared statement issue