Multiple queries vs join

Question

2.50/5 (2 votes)

See more:

I have 10 tables. Each table depends on the other. I am using joins (more than 10 joins) in a single query to fetch result.
Problems are:

1. I find lots of repeated data for a single primary key column since for each primary key for eg. there are 3 matches in table 1 whereas 2 matches in table and so on. Thus data repeatation is 3*2*..*n times. Does this really matters in case I am fetching a single record?

2. So many joins create a bit of complexity(not an issue) but will it be better to use multiple queries instead of joins on comparing them on fetching speed?

Posted 8-Aug-15 20:56pm

Member 11040029

Add a Solution

Comments

PIEBALDconsult 9-Aug-15 3:12am

It's not about speed. What does your application actually need?

Member 11040029 9-Aug-15 3:26am

My app needs to fetch lots of data about a single person which is distributed among normalized tables. I am using joins to retrieve them.

I was just wondering, if will it be better to use multiple queries against joins?

PIEBALDconsult 9-Aug-15 12:27pm

"normalized tables"
Unless you're writing a report, why flatten it?

Sergey Alexandrovich Kryukov 9-Aug-15 10:37am

How can you compare incomparable things? It's not about speed, it's about the state if your database and your applications. Also, a set of separate queries is non-transactional.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-08-09T21:03:00

Solution 2

In addition to solution 1 by Advy Pandya[^], i'd suggest to read this: Visual Representation of SQL Joins[^]

Posted 9-Aug-15 21:03pm

Maciej Los

Comments

Advay Pandya 10-Aug-15 3:57am

Nice article Maciej.... :)

Maciej Los 10-Aug-15 4:09am

Yeah...
You can share your opinion via voting for this article.

Advay Pandya 10-Aug-15 5:00am

yup. sure. It deserves....

Advay Pandya · Accepted Answer · 2015-08-09T19:43:00

Hello,

In your query you are getting repeated data.The reason behind the repetition is the tables might be connected with one to many relation. So, you will need to put separate condition in the join to filter out the repeated data.The separate condition depends on the output that you are trying to get.

Multiple queries are never replacement of join. I am not sure about the result that you are trying to get, but multiple queries may not be your solution. and even if you will get output, of course the performance will get refused using multiple queries.

The best solution for your problem is to fix the one to many relation by filter out the repeated data. separate condition after "ON" will help you to filter out the repetition.

Please let me know if you have any concern or query or if you need more information on this.

Thanks