AnsweredAssumed Answered

ExecuteSQL

Question asked by rkappel on Feb 5, 2017
Latest reply on Feb 6, 2017 by rkappel

I am struggling with why it takes to run an ExecuteSQL step the first time it is run during a session.

I have a portal that will return a list of available inventory based on a set of parameters.  The user enters a search term and a script combines the users input with some other settings to perform a SQL query on one table which returns the keys to a series of records in a second table that populates the portal.

The text of the SQL statement is this:

 

SELECT KF_INVENTORY FROM IV_INVENTORY2 AS IV

           WHERE UPPER ( IV.Stock_Status ) LIKE '%STOCK%'

 

           AND ( UPPER ( IV.Item_No ) LIKE '%HEAT%'
                      OR  UPPER ( IV.Pattern_Name ) LIKE '%HEAT%' )

 

           AND  UPPER ( IV.Warehouse ) IN  ( '465' , '455' )

 

           AND  UPPER ( IV.Company ) IN  ( 'FFW' , 'MCS' , 'CCA' , 'CSS' , 'CTI' , 'INV' , 'CCW' )

 

When this query runs the first time it takes just over a minute to return a value.  Subsequent executions, with different or the same user input, take about 4 seconds.

 

The table that the query runs on has 11 fields and 60,000 records.  

 

Does anyone know if there is something I can do to speed up the initial execution?

 

Thank you.

Outcomes