Dynamic Replace: Run SQL Query For Multiple Dates

[bradonchid]

Hi, I have a query that I need to run to get data from the past 52 weeks. I don't have enough spool to run it for the whole time frame, so I created the below workflow to run it one day at a time and write the results in a Teradata table. The problem I'm facing is that it takes it takes an average of 20 minutes to for my workflow to run each day through the query and write it to the table, which obviously is way to long. When I run the query in Teradata, it only takes about 20 seconds. I have also attempted creating this workflow with the In-Database tools, but I haven't found a way to dynamically replace the dates in my sql query. Is there any way that I can make this workflow work faster?

[cmcclellan]

I'm just amazed that Teradata runs that slow :(     I know you said you've tried, but a Connect in-db -> Write Data in-db is usually really quick and you qualify the SQL to get the last year only.

The trick question is how many records are you querying ? (daily and yearly count ?)

[Claje]

Disclaimer: I don't use Teradata, so this is generic advice.

A few thoughts on improving performance:

Can you configure your process to run for each individual week instead of 1/day?  This reduces your overall queries to 52 from 365(.25)

If you don't include the table write, how long does the query take?  If it is shorter, you might be running into issues because currently this process is trying to insert ALL 365 days at once into a temp table, which might be taxing your local machine.  You could try putting this into a Batch Macro so that Alteryx only holds data locally for one day at a time.

In a similar concept to the above, you could use a batch macro, connected to an Input Data tool with a very fast "dummy" select statement (IE SELECT TOP 1 ID FROM TABLE), and a PreSQL statement which inserts into your TMP table using the SQL you are running in Teradata, and using the Batch macro to replace the date in the PreSQL statement.

From an in-database perspective, you could have a batch macro which updated a Filter tool for each date.

I'm happy to offer more details on any of these recommendations if you are looking for examples of how to implement them.

[bradonchid]

I am too, it has never been this slow for me. As I said before though, when I run the query in Teradata SQL Assistant, it is fairly quick. But for some reason it is really slow when I run it through my workflow. 

The tables I am pulling from are huge and each day probably has an average daily record count of 6 million. So that would be a little over 2 billion records for the whole year. When I attempt to run the whole year, I spool out in Teradata.

[bradonchid]

I still spool out if I run it for each week. That is a valid point though, I didn't think about it trying to write all 365 days all at once. Could you provide additional details on how I could use a batch macro to avoid this? I haven't ever created a batch macro like that. Thanks!

[cmcclellan]

So it's time for the same disclaimer from me :

Disclaimer: I don't use Teradata, so this is generic advice.

You probably know this already, but that's a disk space issue in Teradata and not something that Alteryx can solve "easily", but can you create a view in Teradata that is the "last 365 days only" and then read from that ?  Basically move your "last 365 days" logic into the database so that your first step in Alteryx is to read only the last 365 days and not have to (basically) filter it from a much larger table.  I'd recommenc definitely staying with in-db if you can, pulling 2bill records out into Designer won't be fast and you would be chewing up a ton of local disk in the process as well.

[Claje]

I've attached two example batch macros in version 2018.3 that shows how I might implement something like this.  One uses Input Data, and another uses In-DB

Note that I used a nonexistent server name, which makes Alteryx take a LONG time to refresh metadata (it times out a connection each time).  I'd recommend either quickly replacing the connection with a valid one, or turning off "auto configure" in alteryx user settings while viewing this.

[bradonchid]

I wish I could create a new view, as that would probably solve this whole problem, but I sadly don't have create view privileges.

[bradonchid]

Claje, my company computer will not allow me to download anything from this site. Would it be possible to email it to me or post a picture of it in this thread?

[Claje]

First thing I'll attempt is to send over the workflow XML.  You can save it into a new ".yxmc" file which should let you open it in alteryx.  If that doesn't work, I'll send some screenshots, but I think it is a lot more helpful for batch macros to see each individual piece.

Method 1 (Data Input to Data Output):

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2018.3">
  <Nodes>
    <Node ToolID="1">
      <GuiSettings Plugin="AlteryxBasePluginsGui.DbFileInput.DbFileInput">
        <Position x="54" y="234" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <Passwords />
          <File RecordLimit="" FileFormat="18">odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Yourdatabase;Data Source=YOURSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False|||YOUR QUERY WHERE DATE = '2019-01-10'</File>
          <FormatSpecificOptions>
            <PreSQLOnConfig>True</PreSQLOnConfig>
            <NoProgress>False</NoProgress>
            <ReadUncommitted>False</ReadUncommitted>
            <TableStyle>Quoted</TableStyle>
            <PreSQL />
            <PostSQL />
          </FormatSpecificOptions>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText>Source=YOURSERVER
Catalog=Yourdatabase
Table=YOUR QUERY WHERE DATE = '2019-01-10'</DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxDbFileInput" />
    </Node>
    <Node ToolID="2">
      <GuiSettings Plugin="AlteryxGuiToolkit.Questions.Tab.Tab">
        <Position x="0" y="0" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>
    <Node ToolID="3">
      <GuiSettings Plugin="AlteryxGuiToolkit.Questions.ControlParam.ControlParam">
        <Position x="54" y="54" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>
    <Node ToolID="4">
      <GuiSettings Plugin="AlteryxGuiToolkit.Action.Action">
        <Position x="54" y="144" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>
    <Node ToolID="5">
      <GuiSettings Plugin="AlteryxBasePluginsGui.DbFileOutput.DbFileOutput">
        <Position x="258" y="234" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <File MaxRecords="" FileFormat="18">odb:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=YOURDATABASE;Data Source=YOURSERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=;Use Encryption for Data=False;Tag with column collation when possible=False|||Yourtemptable</File>
          <Passwords />
          <FormatSpecificOptions>
            <TransactionSize>0</TransactionSize>
            <IgnoreDropTableSQLErrors>False</IgnoreDropTableSQLErrors>
            <PostSQL />
            <TableStyle>Quoted</TableStyle>
            <PreSQL />
            <TransactionMessages>False</TransactionMessages>
            <OutputOption>UpdateWarn</OutputOption>
          </FormatSpecificOptions>
          <MultiFile value="False" />
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText>Source=YOURSERVER
Catalog=YOURDATABASE
Table=Yourtemptable</DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxDbFileOutput" />
    </Node>
  </Nodes>
  <Connections>
    <Connection>
      <Origin ToolID="1" Connection="Output" />
      <Destination ToolID="5" Connection="Input" />
    </Connection>
    <Connection name="#1">
      <Origin ToolID="3" Connection="Question" />
      <Destination ToolID="4" Connection="Question" />
    </Connection>
    <Connection name="#2">
      <Origin ToolID="4" Connection="Action" />
      <Destination ToolID="1" Connection="Action" />
    </Connection>
  </Connections>
  <Properties>
    <Memory default="True" />
    <GlobalRecordLimit value="0" />
    <TempFiles default="True" />
    <Annotation on="True" includeToolName="False" />
    <ConvErrorLimit value="10" />
    <ConvErrorLimit_Stop value="False" />
    <CancelOnError value="False" />
    <DisableBrowse value="False" />
    <EnablePerformanceProfiling value="False" />
    <DisableAllOutput value="False" />
    <ShowAllMacroMessages value="False" />
    <ShowConnectionStatusIsOn value="True" />
    <ShowConnectionStatusOnlyWhenRunning value="True" />
    <ZoomLevel value="0" />
    <LayoutType>Horizontal</LayoutType>
    <Constants>
      <Constant>
        <Namespace>Question</Namespace>
        <Name>ControlParam.Control Parameter (3)</Name>
        <Value />
        <IsNumeric value="False" />
      </Constant>
    </Constants>
    <MetaInfo>
      <NameIsFileName value="True" />
      <Name>claje_batchdatefiltersql1</Name>
      <Description />
      <RootToolName />
      <ToolVersion />
      <ToolInDb value="False" />
      <CategoryName />
      <SearchTags />
      <Author>claje</Author>
      <Company />
      <Copyright>2018</Copyright>
      <DescriptionLink actual="" displayed="" />
      <Example>
        <Description />
        <File />
      </Example>
    </MetaInfo>
    <Events>
      <Enabled value="False" />
    </Events>
    <RuntimeProperties>
      <Actions>
        <NoCondition>
          <Type>NoCondition</Type>
          <Description>(Always Run)</Description>
          <True>
            <Action>
              <Type>UpdateValue</Type>
              <Description />
              <ToolId value="4" />
              <Mode>Simple</Mode>
              <Variable />
              <Replace value="True" />
              <ReplaceText>2019-01-10</ReplaceText>
              <Destination>1/File</Destination>
            </Action>
          </True>
        </NoCondition>
      </Actions>
      <Questions>
        <Question>
          <Type>Tab</Type>
          <Description>Questions</Description>
          <Name>Tab (2)</Name>
          <ToolId value="2" />
          <Questions>
            <Question>
              <Type>ControlParam</Type>
              <Description>Date</Description>
              <Name>Control Parameter (3)</Name>
              <ToolId value="3" />
            </Question>
          </Questions>
        </Question>
      </Questions>
      <ModuleType>Macro</ModuleType>
      <MacroCustomHelp value="False" />
      <MacroDynamicOutputFields value="False" />
      <MacroImageStd value="39" />
      <MacroInputs />
      <MacroOutputs />
      <BatchMacro>
        <OutputMode>AllSame</OutputMode>
        <ControlParams>
          <ControlParam>
            <Name>Control Parameter (3)</Name>
            <Description>Date</Description>
          </ControlParam>
        </ControlParams>
      </BatchMacro>
      <Wiz_CustomHelp value="False" />
      <Wiz_CustomGraphic value="False" />
      <Wiz_ShowOutput value="True" />
      <Wiz_OpenOutputTools>
        <Tool ToolId="5" Selected="True" />
      </Wiz_OpenOutputTools>
      <Wiz_OutputMessage />
      <Wiz_NoOutputFilesMessage />
      <Wiz_ChainRunWizard />
    </RuntimeProperties>
  </Properties>
</AlteryxDocument>

Method 2 (In-Database):

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2018.3">
  <Nodes>
    <Node ToolID="1">
      <GuiSettings Plugin="AlteryxGuiToolkit.Questions.Tab.Tab">
        <Position x="0" y="0" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>
    <Node ToolID="2">
      <GuiSettings Plugin="AlteryxGuiToolkit.Questions.ControlParam.ControlParam">
        <Position x="294" y="114" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>
    <Node ToolID="3">
      <GuiSettings Plugin="AlteryxGuiToolkit.Action.Action">
        <Position x="294" y="210" width="59" height="59" />
      </GuiSettings>
      <Properties>
        <Configuration />
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
    </Node>
    <Node ToolID="4">
      <GuiSettings Plugin="LockInGui.LockInInput.LockInInput">
        <Position x="282" y="354" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <Connection>Connection</Connection>
          <Query>YOUR BASE QUERY WITH NO FILTER</Query>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText>Source=Connection
Table=YOUR BASE QUERY WITH NO FILTER</DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="LockInEngine.dll" EngineDllEntryPoint="AlteryxLockInInput" />
    </Node>
    <Node ToolID="5">
      <GuiSettings Plugin="LockInGui.LockInFilter.LockInFilter">
        <Position x="402" y="354" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <Mode>Custom</Mode>
          <Expression>YOURDATEFIELD = '2019-01-10'</Expression>
          <ValidateBeforeRun value="True" />
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText>YOURDATEFIELD = '2019-01-10'</DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="LockInEngine.dll" EngineDllEntryPoint="AlteryxLockInFilter" />
    </Node>
    <Node ToolID="6">
      <GuiSettings Plugin="LockInGui.LockInOutput.LockInOutput">
        <Position x="594" y="294" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <Table>yourtemptable</Table>
          <CreateMode>Append</CreateMode>
          <AppendMode>ByName</AppendMode>
          <AppendFieldMode>All</AppendFieldMode>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText>Table=yourtemptable</DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="LockInEngine.dll" EngineDllEntryPoint="AlteryxLockInOutput" />
    </Node>
  </Nodes>
  <Connections>
    <Connection name="#1">
      <Origin ToolID="2" Connection="Question" />
      <Destination ToolID="3" Connection="Question" />
    </Connection>
    <Connection name="#1">
      <Origin ToolID="3" Connection="Action" />
      <Destination ToolID="5" Connection="Action" />
    </Connection>
    <Connection>
      <Origin ToolID="4" Connection="Output" />
      <Destination ToolID="5" Connection="Input" />
    </Connection>
    <Connection>
      <Origin ToolID="5" Connection="True" />
      <Destination ToolID="6" Connection="Input" />
    </Connection>
  </Connections>
  <Properties>
    <Memory default="True" />
    <GlobalRecordLimit value="0" />
    <TempFiles default="True" />
    <Annotation on="True" includeToolName="False" />
    <ConvErrorLimit value="10" />
    <ConvErrorLimit_Stop value="False" />
    <CancelOnError value="False" />
    <DisableBrowse value="False" />
    <EnablePerformanceProfiling value="False" />
    <DisableAllOutput value="False" />
    <ShowAllMacroMessages value="False" />
    <ShowConnectionStatusIsOn value="True" />
    <ShowConnectionStatusOnlyWhenRunning value="True" />
    <ZoomLevel value="0" />
    <LayoutType>Horizontal</LayoutType>
    <Constants>
      <Constant>
        <Namespace>Question</Namespace>
        <Name>ControlParam.Control Parameter (2)</Name>
        <Value />
        <IsNumeric value="False" />
      </Constant>
    </Constants>
    <MetaInfo>
      <NameIsFileName value="True" />
      <Name>claje_batchdatefiltersql2</Name>
      <Description />
      <RootToolName />
      <ToolVersion />
      <ToolInDb value="False" />
      <CategoryName />
      <SearchTags />
      <Author>claje</Author>
      <Company />
      <Copyright>2018</Copyright>
      <DescriptionLink actual="" displayed="" />
      <Example>
        <Description />
        <File />
      </Example>
    </MetaInfo>
    <Events>
      <Enabled value="False" />
    </Events>
    <RuntimeProperties>
      <Actions>
        <NoCondition>
          <Type>NoCondition</Type>
          <Description>(Always Run)</Description>
          <True>
            <Action>
              <Type>UpdateValue</Type>
              <Description />
              <ToolId value="3" />
              <Mode>Simple</Mode>
              <Variable />
              <Replace value="True" />
              <ReplaceText>2019-01-10</ReplaceText>
              <Destination>5/Expression</Destination>
            </Action>
          </True>
        </NoCondition>
      </Actions>
      <Questions>
        <Question>
          <Type>Tab</Type>
          <Description>Questions</Description>
          <Name>Tab (1)</Name>
          <ToolId value="1" />
          <Questions>
            <Question>
              <Type>ControlParam</Type>
              <Description>Date</Description>
              <Name>Control Parameter (2)</Name>
              <ToolId value="2" />
            </Question>
          </Questions>
        </Question>
      </Questions>
      <ModuleType>Macro</ModuleType>
      <MacroCustomHelp value="False" />
      <MacroDynamicOutputFields value="False" />
      <MacroImageStd value="39" />
      <MacroInputs />
      <MacroOutputs />
      <BatchMacro>
        <OutputMode>AllSame</OutputMode>
        <ControlParams>
          <ControlParam>
            <Name>Control Parameter (2)</Name>
            <Description>Date</Description>
          </ControlParam>
        </ControlParams>
      </BatchMacro>
      <Wiz_CustomHelp value="False" />
      <Wiz_CustomGraphic value="False" />
      <Wiz_ShowOutput value="True" />
      <Wiz_OpenOutputTools />
      <Wiz_OutputMessage />
      <Wiz_NoOutputFilesMessage />
      <Wiz_ChainRunWizard />
    </RuntimeProperties>
  </Properties>
</AlteryxDocument>