Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question about SQL select - Performance

 
Gilbert johnson
Ranch Hand
Posts: 45
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Everyone

I just have a simple question. I have two ways to do this task and I want to know which is the best way. So just let me know which is the best way to do this. As I'm new to this, I dont know performance wise which is better....

For simplification purpose, I'm just showing two fields in the table descriptions. But each of these tables contain 8-10 columns.

I have two tables


You get the picture....These two tables are linked by message ID. The first table contains the message details and the second table specifies all the customers who are gonna get that message.

Now if I want to retrieve information from both the tables about one particular message. Like I'll give the message_ID and I would want the message_heading and all the IDs of the customers who are gonna get that particular message.

So is it a good idea to write TWO select statements. The first one would select the message details from the message table that would return one row and do another select on the second table that would return multiple rows for that particular message_id.

Or is it better to write a ONE SELECT JOIN statement. The problem with this approach is that it will return redundant data from the first table.
For instance:

would return

The problem I see is that the message_heading is redundant data and would keep repeating. And actually in that table, I have several more fields like message_heading that would keep repeating. The only advantage I see is that It is one SELECT - that is one database operation.

And in the first option, there are two select statements which means two database operations which is considered more expensive. But in the first approach, I wont have any redundant data.

I know this is a very simple question, but i've made it sound more complicated. But I just wanted to give a more clear picture abt the situation.......

Thanks for your advices
Gublooo

[ July 30, 2005: Message edited by: gublooo johnson ]
[ July 30, 2005: Message edited by: gublooo johnson ]
 
Gilbert johnson
Ranch Hand
Posts: 45
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmmm.....I guess everybody is enjoying their weekend. I'm sure its an easy question.......maybe i just did'nt explain it properly........or maybe i explained it too much and nobody is willing to read that long of a question......

Well any feedback would really be appreciated.

Thanks
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by gublooo johnson:
And in the first option, there are two select statements which means two database operations which is considered more expensive. But in the first approach, I wont have any redundant data.


Quite right. And equi-join is a better idea in this situation.


The problem I see is that the message_heading is redundant data and would keep repeating. And actually in that table, I have several more fields like message_heading that would keep repeating. The only advantage I see is that It is one SELECT - that is one database operation.


As msg_id is in your selection criteria. You know the msg_id is 1.
So you just do like this,

 
Gilbert johnson
Ranch Hand
Posts: 45
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Adeel

Thanks for your response. So your saying join operation is a better choice even if the data from the first table is redundant.

Like even the query u gave, the same message_heading would repeat for all different customers that have the same message_id , rite.

Well someone told me , that I would be better off using two select statements rather than an equi join....

So I just wanted to find out - what other people think.
Thanks
Gublooo
 
Makarand Parab
Ranch Hand
Posts: 121
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi johnson
If given me a chance, to resolve ur issue, i would have taken the 2nd option. The second option is a good way to solve ur issue. Don't worry abt performance, if ur go for PreparedStatement or write a simple stored procedure with the simple equi join inside it. Doing this ur query will be executed only once.

Let me know if there are any concern.

Regards
Makarand Parab
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33697
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gublooo,
Welcome to JavaRanch! In general, it's good to allow 24 hours for a response since people are in timezones around the world. On a weekend, sometimes it takes 48 hours.

First write it with the join. This makes the most sense and will be easiest to maintain. If it proves to be a performance problem (which is unlikely), you can refactor the code to make two calls.
 
Gilbert johnson
Ranch Hand
Posts: 45
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hey guys

Thanks for your responses.....Yep Jeanne - ur rite....but its just that when your waiting for replies - one hour seems like a long time....but i'll learn to be patient...

anyways........I've got 2 votes for JOIN and 1 vote for writing 2 seperate SELECT statements.

I guess i'll have to go for JOIN then. I do agree with Makarand too that performance wont be that big of a deal with 2 selects. I just wanted to find out if there was a preferred way. I guess its a matter of choice then.

Thanks for your advices.
Really appreciate it
Gublooo
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33697
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by gublooo johnson:
Yep Jeanne - ur rite....but its just that when your waiting for replies - one hour seems like a long time....but i'll learn to be patient...

Oh, I know! I've been there.


I guess i'll have to go for JOIN then. I do agree with Makarand too that performance wont be that big of a deal with 2 selects. I just wanted to find out if there was a preferred way. I guess its a matter of choice then.

It is a matter of chocie, but the JOIN is easier to maintain.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic