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?