• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

best way of querying?

 
Jack Daniel
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

well, it is regarding best ways of creating SQL queries.Plz don't suggest ORM tools like Hibernate for this small project..

You know, i have to do a big query, kind of involves 12 tables,.. when a user submits a form.
So i am just doing it in a single query, involving some Outer Joins and all; it takes quite some time.I do show "processing.. Please wait .. " page. Only one access to the database gets me the results from about 12 tables. Is this the best approach?? or do smaller but more number of queries??

Which is faster and more efficient??

Also i felt putting everything in one query, for making further changes, just changing that one query is good enough. If it is more than 1 query, you have to make changes everywhere, i mean all queries.

Just eager to listen to, what the JDBC gurus would say. Thanks
I of-course use DataSource connection Pooling and only Prepared Statements.
[ April 07, 2005: Message edited by: Bear Bibeault ]
 
Loren Rosen
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's usually better to do one big query than to, in effect, do joins in your application code. But a complicated, slow query like this one will take some work on your part to optimize. You'll need to add the right indexes, and you may need to prod the database a bit to get it to choose the best join order.
 
Kash Mhai
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depends on how your query is.. I mean, there are two ways to write queries,
1. GEt all the data from 12 tables AND then filter it or
2. Filter the data AND get the result.

I would suggest the second alternative.
 
Jack Daniel
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks guys
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35269
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jack,
Also try tuning the query if you haven't already done so. You can run "explain" to see the execution path. This shows if you need a missing index or tweaking the query will make it faster.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic