Environment: SQL Server 2019 Exceuting stored procedure using sp_execute_external_script with embedded python script.
I have a SQL/Python stored procedure that passes the largest recordset as input_data_1 and the smaller recordset as a JSON parameter. When it is run for the first time it takes about 9 seconds. If another is executed it is nearly immediate. It seems as time passes it takes the initial 9 seconds to run.
The procedure takes the recordsets, processes them and returns an HTML string to be displayed on a web page.
The previous solution was to process everything using TSQL alone in the procedure, however, the dynamic nature pushed me to using an external python script due to being able to use the same code base from the sister application, (and my current limited knowledge of doing this entirely in TSQL), and using the same code within SQL and an offline application.
My thought is the first run initializes the environment then later purges this from the cache. If this is the case then can this be set to retain the intitalization from the first run.
Do you have any solutions to improve the response time of the initial run?
This is the first time I have had to make a post so apologies if I have not framed this correctly given the site requirements.
Bottom line: Why is the first run very slow?
Edit: added 9/23/2022 1:22am, This is a very scaled down version.
USE [xxx]
GO
/****** Object: StoredProcedure [xxx].[xxx] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
-- =============================================
-- Author:
-- Create date:
-- Description: Creates HTML string that gets passed to xxx.
-- =============================================
*/
ALTER PROCEDURE [xxx].[xxx]
@sourceID NVARCHAR(50)
,@plantID NVARCHAR(50)
,@lineLinkID NVARCHAR(50)
,@productCode NVARCHAR(50)
,@itemCode NVARCHAR(MAX)
,@targetWidth NVARCHAR(50)
,@targetMil NVARCHAR(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DROP TABLE IF EXISTS #socHeader;
DROP TABLE IF EXISTS #socData;
DROP TABLE IF EXISTS #html;
CREATE TABLE #socHeader(
socID INT
,sourceID INT
,plantID INT
,lineLinkID INT
,lineNumber INT
,plantCode2 VARCHAR(2)
,productCode VARCHAR(50)
,itemCode VARCHAR(50)
,itemMasterID INT
,dtrStation VARCHAR(50)
,socCreationDate DATETIME
,socCaptureDate DATETIME
,socOriginalOrderNumber INT
,socReviewer VARCHAR(50)
,socComments VARCHAR(MAX)
,targetWidth VARCHAR(10)
,targetMil VARCHAR(10)
,deleteFlag BIT
,socRevLevel INT
,socRevisionDate DATETIME
,socRevisionCaptureDate DATETIME
,socRevisionOrderNumber INT
,socRevisedBy VARCHAR(50)
,socRevisionComments VARCHAR(MAX)
);
INSERT INTO #socHeader
EXEC [soc].[getSOCHeaderRevInfoV5_SP]
@sourceID
,@plantID
,@lineLinkID
,@productCode
,@itemCode
,@targetWidth
,@targetMil;
/* Used to determine if SOC exists */
DECLARE @socID INT; SET @socID = (SELECT socID FROM #socHeader);
DECLARE @socDeleted BIT; SET @socDeleted = (SELECT COALESCE (deleteFlag, 0) FROM #socHeader);
/* If an SOC exists, query and prepare the data.
If it does not exist then frame a null value to be passed. */
IF @socID>0 AND @socDeleted=0
BEGIN
/* #socData will be passed as the InputDataSet because it is the largest */
DROP TABLE IF EXISTS #socData;
CREATE TABLE #socData(
pk_socID INT
,plantName VARCHAR(50)
,lineName VARCHAR(50)
,lineCode VARCHAR(50)
,SectionName VARCHAR(50)
,ColumnName VARCHAR(50)
,RowName VARCHAR(50)
,[value] VARCHAR(50)
,sourceID INT
,plantID INT
,lineLinkID INT
,lineNumber INT
,tagOrder INT
,paramSort INT
);
INSERT INTO #socData
EXEC [dtr].[getDataV5_SP]
@socID=@socID
/* Prepare @headerData and return as JSON, will be passed as @params */
DECLARE @headerData nvarchar(max) = (
SELECT
socID
,sourceID
,plantID
,lineLinkID
,lineNumber
,plantCode2
,productCode
,itemCode
,itemMasterID
,dtrStation
,FORMAT(socCreationDate, 'MMM dd yyyy hh:mm tt') AS socCreationDate
,FORMAT(socCaptureDate, 'MMM dd yyyy hh:mm tt') AS socCaptureDate
,socOriginalOrderNumber
,socReviewer
,socComments
,targetWidth
,targetMil
,deleteFlag
,socRevLevel
,FORMAT(socRevisionDate, 'MMM dd yyyy hh:mm tt') AS socRevisionDate
,FORMAT(socRevisionCaptureDate, 'MMM dd yyyy hh:mm tt') AS socRevisionCaptureDate
,socRevisionOrderNumber
,socRevisedBy
,socRevisionComments
FROM #socHeader FOR JSON AUTO, INCLUDE_NULL_VALUES
);
/* Declare and set Python script */
DECLARE @pscript NVARCHAR(MAX);
SET @pscript = N'
import pandas as pd
def defineCSS():
return css
def tab(level):
return tabs
def createHTML_Open():
return html
def createHTML_Close():
return html
def createTitle(header):
return html
def createHTMLheader(headerData, socData):
return headerhtml
def createTableHTML(tableData, tableName):
return htmlText
def processSocData(socData):
return sumHTML
def processSocComments(headerData):
return html
def assembleHTML(headerData, socData):
return html
socData = pd.DataFrame(InputDataSet)
headerData = pd.read_json(headerTable)
sumHTML = assembleHTML(headerData, socData)
df = pd.DataFrame( [sumHTML], columns=["FinalHTML"] )
print(df.dtypes)
OutputDataSet = df
'
/* Prepare final statement */
CREATE TABLE #html(FinalHTML NVARCHAR(MAX));
INSERT INTO #html
EXECUTE sp_execute_external_script
@language = N'Python'
,@script = @pscript
,@input_data_1 = N'SELECT * FROM #socData'
,@params = N'@headerTable nvarchar(max)'
,@headerTable = @headerData;
SELECT * FROM #html
END
ELSE
SELECT null AS FinalHTML
DROP TABLE IF EXISTS #socHeader;
DROP TABLE IF EXISTS #socData;
DROP TABLE IF EXISTS #html;
END
9/24/2022 I added OPTION(RECOMPILE) and it reduced it from 9 seconds to 3 seconds, sometimes 2.