Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Last week, we had the author of TDD for a Shopping Website LiveProject. Friday at 11am Ranch time, Steven Solomon will be hosting a live TDD session just for us. See for the agenda and registration link
  • 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

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: 41086
848
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.
 
Blood pressure normal? What do I change to get "magnificent"? Maybe this tiny ad?
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter
reply
    Bookmark Topic Watch Topic
  • New Topic