joining lots and lots of tables in a vew

Question

joining lots and lots of tables in a vew

mark seidelin-407018

Old Hand

Points: 389
More actions
December 6, 2017 at 8:54 pm

#341215

have got a query(view) that needs to join multiple tables in a view. Currently have over 100 tables in the join - and this number could get larger. Does produce is a rather wide resultset (1 column per table)
joined tables are relatively small (c 6-8 columns) and with only c 120 rows each
join is simple one field key to one field key - and join target table IsLatest = 1
Performance is ok when joining upto c 75 tables - but then degrades quickly from that number upwards
any suggestions on how to improve perfomance ??
have been looking at HINTs / ParallelProcessing without much joy (yet)....
thx
m

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Ideally, post the execution plan as a .sqlplan file. What kind of JOINs are we talking about? INNER ? OUTER ? APPLY? Are the tables indexed? What are the indexes? Please provide more details.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Joe Torre SSChampion Points: 10248 More actions · Answer 2

Try creating intermediary indexed views. Of course this will add overhead to DML. Another approach is to create some tables to cache slow changing data to simplify the join.

mark seidelin-407018 Old Hand Points: 389 More actions · Answer 3

Thx for the suggestions.
am on SQL 2016.

have got a working solution - and it appears to scale
Code was

         SELECT
          mainT. Column1
          ,SubT1.Column1 as c1
          ,SubT2.Column1 as c2
          ,SubT3.Column1 as c3
          ,SubT4.Column1 as c4
         from MainTable mainT
         inner join Subtable1 SubT1 on SubT1.key = mainT.key and SubT1.IsLatest = 1
         inner join Subtable2 SubT2 on SubT2.key = mainT.key and SubT2.IsLatest = 1
         inner join Subtable3 SubT3 on SubT3.key = mainT.key and SubT3.IsLatest = 1
         inner join Subtable4 SubT4 on SubT4.key = mainT.key and SubT4.IsLatest = 1

Changed to be

         SELECT
          mainT.Column1
          ,(select Column1 from Subtable1 SubT1
            where SubT1.key = mainT.key and SubT1.IsLatest = 1) c1
          ,(select Column1 from Subtable2 SubT2
            where SubT2.key = mainT.key and SubT2.IsLatest = 1) c2
          ,(select Column1 from Subtable3 SubT3
            where SubT3.key = mainT.key and SubT3.IsLatest = 1) c3
          ,(select Column1 from Subtable4 SubT4
            where SubT4.key = mainT.key and SubT4.IsLatest = 1) c4
         from MainTable mainT

did this for a query with over 100 tables - didn't get the performance degradation

so far, so good

Jeff Moden SSC Guru Points: 1004421 More actions · Answer 4

mark seidelin-407018 - Saturday, December 9, 2017 9:40 PM
Thx for the suggestions.
am on SQL 2016.
have got a working solution - and it appears to scale
Code was
         SELECT
          mainT. Column1
          ,SubT1.Column1 as c1
          ,SubT2.Column1 as c2
          ,SubT3.Column1 as c3
          ,SubT4.Column1 as c4
         from MainTable mainT
         inner join Subtable1 SubT1 on SubT1.key = mainT.key and SubT1.IsLatest = 1
         inner join Subtable2 SubT2 on SubT2.key = mainT.key and SubT2.IsLatest = 1
         inner join Subtable3 SubT3 on SubT3.key = mainT.key and SubT3.IsLatest = 1
         inner join Subtable4 SubT4 on SubT4.key = mainT.key and SubT4.IsLatest = 1
Changed to be
         SELECT
          mainT.Column1
          ,(select Column1 from Subtable1 SubT1
            where SubT1.key = mainT.key and SubT1.IsLatest = 1) c1
          ,(select Column1 from Subtable2 SubT2
            where SubT2.key = mainT.key and SubT2.IsLatest = 1) c2
          ,(select Column1 from Subtable3 SubT3
            where SubT3.key = mainT.key and SubT3.IsLatest = 1) c3
          ,(select Column1 from Subtable4 SubT4
            where SubT4.key = mainT.key and SubT4.IsLatest = 1) c4
         from MainTable mainT
did this for a query with over 100 tables - didn't get the performance degradation
so far, so good

The problem with the first query may be the fact that you've included an implicit WHERE clause in each join instead of in a separate real WHERE clause. Since your second query is a collection of multiple correlated subqueries, EVERYTHING is a part of a WHERE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)