wood burning stoves*
The moose likes JDBC and the fly likes Question about SQL select - Performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Question about SQL select - Performance" Watch "Question about SQL select - Performance" New topic
Author

Question about SQL select - Performance

Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
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

Joined: Jul 10, 2005
Posts: 45
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

Joined: Aug 15, 2004
Posts: 2874
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

Joined: Jul 10, 2005
Posts: 45
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

Joined: Dec 10, 2004
Posts: 121
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

Joined: May 26, 2003
Posts: 30914
    
158

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Gilbert johnson
Ranch Hand

Joined: Jul 10, 2005
Posts: 45
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

Joined: May 26, 2003
Posts: 30914
    
158

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
 
subject: Question about SQL select - Performance