1 Reply Latest reply on Apr 30, 2012 11:49 AM by Tracy Rodgers

    Custom SQL small extract takes hours to complete.  Only takes 3 mins in SQL.

    Zachary Fraile

      I use this query as my custom SQL.

       

      select Project, Age = case

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 31 then '< 30'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 61 then '31 to 60'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 91 then '61 to 90'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 121 then '91 to 120'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 151 then '121 to 150'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 181 then '151 to 180'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 366 then '181 to 365'

      else '366+'

      end, ReportDate = DATEADD(dd, DATEDIFF(dd, 0, getdate()), -1),

      Count = count(*), SizeIn = sum(t.SizeIn), SizeNow = sum(t.SizeNow)

      from t (nolock)         

      join u (nolock) on u.unitid=t.unitid        

      join a (nolock) on a.accountid=u.accountid        

      join p (nolock) on t.projectid = p.projectid

      where t.status <> 5

      and p.type = 3

      group by p.Project,

      case

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 31 then '< 30'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 61 then '31 to 60'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 91 then '61 to 90'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 121 then '91 to 120'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 151 then '121 to 150'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 181 then '151 to 180'

      when datediff(dd,coalesce(a.dischargedate, a.admitdate),dateadd(dd,-1,getdate())) < 366 then '181 to 365'

      else '366+'

      end

       

      I can execute that in 3 minutes directly in SQL.  I was able to extract it into Tableau in about an hour (roughly 30 minutes to make the connection and another 30 for the full extract).  The dataset is under 400 records with only six fields so the size is quite minimal.  The intent is to run an incremental refresh each day, which will effectively append a whole new set of data with the current date tagged as Report Date.

       

      However, I cannot get the extract to incrementally refresh based on ReportDate. I have used this technique on other Tableau's without problem, but for whatever reason this particular extract simply will not complete.  I've let it run for as long as 12 hours, at which point I start getting timed out from various server resources so I have no idea if it would ever complete.

       

      Why would it take so much longer to run the same query out of Tableau?

       

      Thanks