2 Replies Latest reply on Sep 6, 2013 1:24 PM by Josh Friedlander

    Tableau Passing multiple MSSQL functions in WHERE clause

    Josh Friedlander

      Im rather new to Tableau but as a DBA I have been getting complaints about extremely slow data extracts from our Production SQL server.  While investigating the problem I noticed this: 

       

      ([FactProductPrice].[DateTimePriceCollected] >= DATEADD(week, (- 4), (DATEADD(day, DATEDIFF(day, 0, {ts '2013-09-05 21:32:30.843' }), 0) - (DATEPART(weekday, CAST({ts '2013-09-05 21:32:30.843' } AS DATETIME)) - 1))))

        AND ([FactProductPrice].[DateTimePriceCollected] < DATEADD(week, 1, (DATEADD(day, DATEDIFF(day, 0, {ts '2013-09-05 21:32:30.843' }), 0) - (DATEPART(weekday, CAST({ts '2013-09-05 21:32:30.843' } AS DATETIME)) - 1))))

       

       

       

       

      its passing writing its queries in Oracle/Postgres syntax which is causing it to be non-performant.  when I remove the functions form the where clause the extract works quite fast. 

       

       

      is there a work around for this or am I going to have to customize all the code?

        • 1. Re: Tableau Passing multiple MSSQL functions in WHERE clause
          Russell Christopher

          This SQL was probably generated by Tableau in response to a user's (complex-ish) filter or calculated field that is part of the workbook associated with the extract. If you can identify / remove the filter then your problem goes away...but the user who created this filter probably won't be very happy with you

           

          Do you use Tableau Server, too? If so, I'd suggest you create a "virgin extract" with very few of these filters in place. This data source can be published to Tableau Server as a "Data Server Data Source",and then everyone can use it. They can also "decorate it" with additional stuff (like the filter)...The beauty of this will be that their "decoration filter" will only hit the EXTRACT. When the extract populates itself during your batch window, it'll be firing your clean SQL.

           

          What about scheduling these extracts at night when no one is around? That would help, too.

          1 of 1 people found this helpful
          • 2. Re: Tableau Passing multiple MSSQL functions in WHERE clause
            Josh Friedlander

            yes I thought about that.  its a few hundred million rows of data which takes quite a long time to chew through. 

             

            still much faster than seven functions in the where clause. 

             

             

            Thanks