1 Reply Latest reply on Aug 14, 2013 8:56 PM by Joshua Milligan

    joining and db efficiency tips please

    giles.somers

      Hi all

       

      I'm looking to reduced the query times in my Tableau Viz's and would be grateful for any tips

       

      There's a few options I have, so can anyone say which are best?

       

      1) Multiple join criteria when adding a table vs one join in each?

      2) adding data as joined tables or instead using secondary data sources and blending in with relationships?

      3) Plain join, or also create relationships (if that's possible - I cant check at present since Tableau is importing)

      4) Do the joins make much difference to the efficiency or does Tableau essentially produce a master sheet with everfield linked to every field anyway?

       

      I've got about 6 tables joined.

      Some have more than 500k rows

       

      I attempted to restructure the tables yesterday to see if that allowed fewer rows.

      more than 24 hours later, the import is still going and has hit more than 68 million tubles (it looks like it'll be going to 117 million if I'm reading the log right!).

      My hope is that the massive number of rows is just an interim thing, as the db had around 6 million before

       

      I'd be grateful if someone could please let me know if Tableau multiplies out Table rows

      ie. if you have:

      Table 1: 5 rows

      Table 2: 10 rows

      Table 3: 3 rows

      Then does that mean needing 150 rows in the end result??

      If so, can one 'separate out' some tables to prevent this happening

       

      Thanks for any help and advice!

        • 1. Re: joining and db efficiency tips please
          Joshua Milligan

          Giles,

           

          Some of the questions you ask should be answered by the structure of the data and not so much in terms of efficiency.  For example, (1) whether or not you need multiple join criteria will depend on the relationship between tables.  Best practice (and most efficient) would be a single foreign key for the join, but your data may not allow for that.

           

          In terms of (2) joining versus blending, this thread gives some good detail (and is still relevant for version 8): Custom SQL vs Data blending - if all things are equal which will yield better performance?

           

          I'm not sure what you mean by (3) "plain join".  Defining referential integrity in the database can definitely help performance as it may allow for join culling.

           

          Finally, (4) Tableau is going to join the tables exactly as you define the joins.  The joining will work exactly as it would in a relational database -- so you might want to do a quick Google search on how joins work.  For example, you might want to look at this: SQL Joins.  It definitely doesn't seem right that 6 million records are turning into tens or hundreds of millions.  Likely, your joins are not resulting in one-to-one matches.

           

          I realize that most of this is very generic.  If you are able to share some specifics such as the schema of your tables, I'd be happy to take a look.

           

          Regards,

          Joshua