Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statement issue

 
Vishravars Ramasubramanian
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Vishravars Ramasubramanian
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 531
Java Mac OS X Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34837
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya. sure. i will try with both

embedded where clause options
and try all the queries atomically.

thanks for the response
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic