• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Multi joins or Views ?

 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I'm creating a query page for an online b-to-c company, through which they will be able to view and access all their customer info. I have to access lots of tables, create long joins, to get the related info. I'd like to know what are the options for accessing many tables, and getting info connecting them. I'm not familiar with performance issues regarding using multi joins or long joins, and views. If someone has used them before,what are the advantages and disadvantages of using them ?
Thanks a lot.
 
martin samm
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i'd use views for two reasons.
Firsly the statement in the java would be more succinct (i.e. selecting some coulmns from a view, rather than a long complicated series of joins)
Secondly, should you need to alter the logic behind the query, you can easily alter the view within the database, but to change the Java will involve code changes, recompilation and packaging, deployment etc, etc

------------------
martin samm
m_sam@rroom.net
 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Martin. I'll try it out.
 
bill bozeman
Ranch Hand
Posts: 1070
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would use Stored Procedures if your database supports them. A SP has the advantage of once it is compiled, it will know how to run better for future calls. Unlike a direct call using SQL, it will have to optimize it and run it all at once. Besides, it is cleaner to separate out the duties between the two.
Bill
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by bill bozeman:
I would use Stored Procedures if your database supports them. A SP has the advantage of once it is compiled, it will know how to run better for future calls

A view is pre-parsed (and, depending on the databased, pre-optimised) as well. Well-designed views perform well and do an fine job isolating responsibility. They are IMHO preferable to stored procedures if all you need is a (fairly) straightforward feed of data from the database.
I'd use stored procedures where significant processing or logic is needed, especially if the problem can only be solved using procedural code in the first place.
- Peter
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic