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 !!
Joined: Apr 28, 2006
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)
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.
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
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.
Joined: Apr 28, 2006
ya. sure. i will try with both
embedded where clause options and try all the queries atomically.