Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Postgres ExecuteQuery

 
Mary Cole
Ranch Hand
Posts: 362
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I am using postgres sql and JDBC3 driver of Postgres( pg74.216.jdbc3).
While my query executes in 1 sec on PGAdmin tool. I had put the elapsed time for executeQuery() and it takes 34 sec running from the app.

Can anybody tell me whats the issue in this...is it a bug in the driver?

Thx in advance
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mary,
That's a big difference! Does this happen repeatably (in other words, maybe there was someone with a lock on the table.)

Often timing differences have to do with network issues. Try a query that returns one row and see how long that takes through the driver.

Also, can you post the query?
 
Mary Cole
Ranch Hand
Posts: 362
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yep.....thats really a big difference...yep it happens often.....

This is the query-----------

SELECT distinct irv.ir_id,irv.doc_id,irv.doc_version FROM (( ( Ir_Temp INNER JOIN ir_search2_view irv ON Ir_Temp.ir_id=irv.ir_id AND ( irv.ir_type_id IN ( 1, 0, 2, 3) AND ( (irv.ir_severity_id IN ( 0 , 1 , 2 ) AND irv.ir_type_id = 0) OR (irv.ir_severity_id IN ( 0 , 1 , 2 ) AND irv.ir_type_id = 1) OR (irv.ir_severity_id IN ( 0 , 1 , 2 ) AND irv.ir_type_id = 2) OR (irv.ir_severity_id IN ( 0 , 1 , 2 ) AND irv.ir_type_id = 3) ) )) INNER JOIN ir_tag ON Ir_Temp.ir_id=ir_tag.ir_id) INNER JOIN vendor_tech ON ir_tag.vendor_tech_id=vendor_tech.vendor_tech_id AND vendor_tech.vendor_id IN ( 221, 32,12,65,9,46,32,72,28,103 )) INNER JOIN tech ON vendor_tech.tech_id=tech.tech_id AND ( tech.prod_id IN ( 1284, 6670, 5616, 424, 1046, 1048, 900,5672,5674,5676 ) OR ( vendor_tech.vendor_id= 261 AND tech.prod_id= 5616 ) OR ( vendor_tech.vendor_id= 72 AND tech.prod_id= 8905 ) OR ( vendor_tech.vendor_id= 28 AND tech.prod_id= 3794 ) OR ( vendor_tech.vendor_id= 103 AND tech.prod_id= 6670 ) ) WHERE irv.ir_id IN ( select ir_id from IR_VERSIONS_VIEW where Vuln_Ver_Patch = 1 OR Vuln_Ver_fix = 1 )
[ July 05, 2005: Message edited by: Mary Cole ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you using preparedstatement or statement ?
if you are using statement then try it with prepared statement

You have a big query to execute it might be possible query parsing is taking a major part of execution time.

Did you try using view ?

Shailesh
 
Mary Cole
Ranch Hand
Posts: 362
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes am using a prepared stmt..... actually the IR_temp is a temporary dynamic table which queries with the view ir_search2_view..... temp table creation doesn't take much time....but executeQuery takes most of the time.......... when I execute the stmt with my eclipse....it takes 1.5 sec......donno whts the prob
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not sure what is the problem, could you put code here so we can suggest if there is any other problem

Shailesh
 
Mary Cole
Ranch Hand
Posts: 362
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Shailesh,
Thx for the reply.....after indexing...everything worked out well..
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic