• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

best way of querying?

 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 156
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks guys
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic