Any Database people can answer a question on SQL JOIN?

[Faranim]

Highly Simplified Example:

I have 2 tables in a one-to-many relationship. Table_A is the "one" side and has roughly 1000 rows of data. Table_B is the "many" side and has roughly 500 rows of data for each line in Table_A (aka approx 500k rows total).

SELECT Table_A.Column1, Table_A.Column2, Table_B.Column1
FROM Table_A INNER JOIN Table_B ON Table_A.ID = Table_B.ID
WHERE Table_A.ID = 345; (or, WHERE Table_B.ID = 345)

For some reason, my Database is selecting all 500k rows and THEN applying the WHERE clause to filter the results.

Is this how JOIN with WHERE clause is supposed to work? Join the tables completely and then include the records that meet the WHERE condition? Is it implementation specific? How can this query be optimized?

Note that in the problem I'm trying to solve, the WHERE clause is not always the same, it's usually a large collection of conditions. But I couldn't find any information on the net about the inner-workings of JOIN statements.

[Keltiks]

The main issue here is Implicit vs. Explicit joins.

When you express a join in the WHERE clause, the join is considered implicit.

SELECT a.Employee_ID, b.Employee_Salary
FROM tableA a, tableB b
WHERE a.Employee_Title = b.Employee_Title

SQL generally runs the FROM clause before the WHERE. Therefore an implicit join would be inefficient, because it is grouping the two tables together entirely, then filtering for the WHERE clause.

When you express a join in the FROM clause, the join is considered explicit.

SELECT a.Employee_ID, b.Employee_Salary
FROM tableA a JOIN tableB b
ON a.Employee_Title = b.Employee_Title

Both queries will yield the same results, but the second is far more efficient.

[Keltiks]

Actually to be honest I don't think I answered your question, however the query you wrote is optimized. That is the way SQL works.

[Faranim]

Nope, you didn't really answer my question . Let's make it simpler.

I have an Employee Table with 4 fields: Employee_ID (auto-generated, Primary Key), First_Name, Last_Name, and Phone_Num.

SELECT First_Name, Last_Name, Phone_Num
FROM employeeTable
WHERE First_Name = 'Bob';

This query has constant performance? Regardless of whether employeeTable has 1 row or 1 million rows, this SQL statement should take (roughly) the same amount of time, correct?


Now, let's assume that we're using good Database design, and allow for a single Employee to have multiple Phone Numbers stored in another Table with a one-to-many relationship, linked by the Employee_ID.


SELECT First_Name, Last_Name, Phone_Num
FROM employeeTable INNER JOIN phoneNumberTable ON employeeTable.Employee_ID = phoneNumberTable .Employee_ID
WHERE First_Name = 'Bob';


Is the performance still the same? or does performing the JOIN and the WHERE clause somehow cause a large temporary table (containing records whose First_Name is NOT 'Bob') to be created as an intermediate step?


I actually fixed my problem by going another route, but this would be nice to know for the future.

[Rafbacca]

would 'SELECT DISTINCT' help?

[Faranim]

All of the records being returned are unique, so DISTINCT would not help.

Everything I have now works, but when the tables get a lot of data, the performance slows to a crawl. I'm trying to optimize my queries, I just don't know where/how to start optimizing.

This might just be because MS Access sucks, but it's what we're using for report generation. The queries underlying the Reports were written by me when I knew next to nothing about Access (I do know SQL, but never used Access before). Now it's kicking me in the butt because 2 months have gone by and I'm finally stress testing the Database only to discover that all of the reports don't generate fast enough once too much data is entered.

Is it better to make lots of small queries (with only 1-2 joins) and then join those queries together at the end, or just make a couple queries that joins 6-8 tables together all at once?

[Raineer]

Is it better to make lots of small queries (with only 1-2 joins) and then join those queries together at the end, or just make a couple queries that joins 6-8 tables together all at once?

I have done that in the past, sort of chained queries together when too much data gets involved. Make one table from a certain query, then used that information in another query if I need to work with it. Joins with lots of data get pretty boggy on the system after while.

Our system is archaic and MASSIVE (it's ibm's actual records DB), so I have to do alot of bullshit when I need to pull data from 8 tables to generate one report.

[Keltiks]

Performance will decrease when joining multiple tables together. Anything in the WHERE clause will not be looked at until all the JOINs are properly executed.

In my experience, it doesn't matter if you make 10 queries or just one long one with multiple JOINs, it will take the same amount of time.

To increase efficiency you could create preliminary queries that shrink each table before using the join. For example select everything from the employee table where the name = 'Bob'. Then have a second query that pulls all phone numbers relating to bob. Then create a third query referencing both the previous queries and joining that. This would make the query run faster because the data is being filtered before the join. However you would have to modify both the prelimiary queries to filter for the correct data before running the main query. Which in itself is somewhat inefficient.

I would join both table a and table b and store that as a temp table. Then run all your searches off that temp table so you don't have to join everytime you want to run a search. You just do the join once then you are good to go for future searches.

[Justice]

Another way to improve performance when doing queries is set up indexes.