January 13, 2016 at 6:49 am
Hi,
Is there a limitation on how many tables can be JOIN-ed? I have close to 20 tables to join, each of which pulls roughly 50 records.
Could you please share your thoughts on performance pitfalls?
thanks
January 13, 2016 at 7:02 am
etl2016 (1/13/2016)
Is there a limitation on how many tables can be JOIN-ed?
Nope. You can join as many tables as the resources on your server (memory most likely) allow. The limit in SQL 2000 days was 256, the limit was removed at some point after that.
https://msdn.microsoft.com/en-us/library/ms143432.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 13, 2016 at 9:27 am
I'm curious what you mean by "each of which pulls roughly 50 records"...how are you joining your tables? INNER JOIN, LEFT JOIN or UNION?
The number 20 (tables) shouldn't be a problem, providing you respect the (1 to many) relationships between the tables. i.e. that you don't start creating new rows in your result by making dodgy joins.
Does this make sense?
January 13, 2016 at 9:36 am
thanks.
The 20 tables are actually intermediate 'result sets' each of which are LEFT OUTER JOIN-ed to the other incrementally.
I believe, whether its 20 physical tables or 20 data sets resulted from respective Queries that are further joined - should be the same in the context of whether or not there will be any performance pitfalls.
From the suggestions, what I understand is - they should be fine joining that way. Am I correct? Will there be any potential Cache/Buffer memory issues I need to be aware of.
thank you
January 13, 2016 at 12:37 pm
For only 50 rows from each, you'll very likely be OK regardless. Still, before loading the intermediate tables, cluster them on the join key(s).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 13, 2016 at 1:21 pm
actually there are no intermediate physical tables, the 20 packets of data are result sets of those many queries, which are joined. Even in this scenario, can we have clustered key implemented, if so, please advise how - thanks
January 13, 2016 at 1:25 pm
No, I wouldn't worry about indexing/clustering for the other result sets if they are created as part of the main query.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 13, 2016 at 2:05 pm
etl2016 (1/13/2016)
thanks.The 20 tables are actually intermediate 'result sets' each of which are LEFT OUTER JOIN-ed to the other incrementally.
I believe, whether its 20 physical tables or 20 data sets resulted from respective Queries that are further joined - should be the same in the context of whether or not there will be any performance pitfalls.
From the suggestions, what I understand is - they should be fine joining that way. Am I correct? Will there be any potential Cache/Buffer memory issues I need to be aware of.
thank you
Are you saying that the query looks like this?
FROM (complex subquery)
LEFT OUTER JOIN (complex subquery) ON ...
LEFT OUTER JOIN (complex subquery) ON ...
(...)
In that case, there probably will be major performance issues. All the above is a single query. So if each subquery uses five tables and you have 20 of those subqueries, the optimizer will have to work on a 100-table query. That will take a LONG time to compile into an execution plan, and it's very unlikely to be a good execution plan - you are simply throwing too much complexity and too many options at the optimizer.
If you have executed the individual queries, stored the results in temporary tables, and are now joining twenty temporary tables, then you'll be fine. Probably. But do test, with realistic data, before deploying to production.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply