sql query joins multiple tables – too slow (8 tables)

by

I had a similar problem with several lookup tables joining to a large table with all id fields indexed. To monitor the effect of the joins on query time execution, I ran my query several times (limiting to first 100 rows), adding a Join to an additional table each time. After joining 12 tables, there was no significant change in query execution time. By the time I had joined the 13th table the execution time jumped to a 1 second; 14th table 4 seconds, 15th table 20 s, 16th 90 seconds.

Keijro’s suggestion to use a correlated subqueries instead of joins e.g.

SELECT t1_id, 
        (select t2_name from t2 where t1_id = t2_id), 
        (select t3_name from t3 where t1_id = t3_id), 
        (select t4_name from t4 where t1_id = t4_id), 
        (select t5_name from t5 where t1_id = t5_id), 
        (select t6_name from t6 where t1_id = t6_id), 
        (select t7_name from t7 where t1_id = t7_id), 
        (select t8_name from t8 where t1_id = t8_id), 
        (select t9_name from t9 where t1_id = t9_id)  FROM t1

improved query performance dramatically. In fact the subqueries did not seem to lengthen the time to execute the query (the query was almost instanteous).

I am a little suprised as I thought correlated subqueries perform worse than joins.

More Related Contents:

  1. SQL JOIN: is there a difference between USING, ON or WHERE?
  2. Subqueries vs joins
  3. Difference between Oracle’s plus (+) notation and ansi JOIN notation?
  4. SQL Joins Vs SQL Subqueries (Performance)?
  5. Is a JOIN faster than a WHERE?
  6. IN vs. JOIN with large rowsets
  7. Explicit vs implicit SQL joins
  8. Inner join vs Where
  9. INNER JOIN vs LEFT JOIN performance in SQL Server
  10. Equals(=) vs. LIKE
  11. Does the join order matter in SQL?
  12. Multiple INSERT statements vs. single INSERT with multiple VALUES
  13. What is the difference between JOIN and UNION?
  14. Left Outer Join Not Working?
  15. Does the order of where clauses matter in SQL?
  16. Performance of inner join compared to cross join
  17. Counting DISTINCT over multiple columns
  18. Inner Join vs Natural Join vs USING clause: are there any advantages?
  19. Including null values in an Apache Spark Join
  20. SQL primary key: integer vs varchar
  21. MySQL: GROUP_CONCAT with LEFT JOIN
  22. Joining multiple tables in SQL
  23. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  24. Huge performance difference when using GROUP BY vs DISTINCT
  25. sql sum data from multiple tables
  26. Get execution time of PostgreSQL query
  27. Performance of Cross join with WHERE clause compared to Inner join
  28. Left Join without duplicate rows from left table
  29. SQL select join: is it possible to prefix all columns as ‘prefix.*’?
  30. Removing duplicate rows (based on values from multiple columns) from SQL table

Leave a Comment