Tuesday, 6 April 2021

sp_execute_external_script Python In Memory Variable for Faster Process

Is there a way to make a variable saved in memory (like global variable) without loading using pickle.loads every time executing a script using sp_execute_external_script?

I have a Python script that process a data using preprocessed matrix. I have the matrix saved in a table once using script A.

--Script A
DECLARE @matrix VARBINARY(MAX)
EXECUTE sp_execute_external_script @language = N'Python'
  , @script = N'
...
matrix = pickle.dumps(processed_matrix)
'
  , @input_data_1 = N'SOME SELECT QUERY'
  , @params = N'@matrix VARBINARY(MAX) OUTPUT'
  , @matrix = @matrix OUTPUT

DELETE FROM MatrixTable
INSERT INTO MatrixTable(matrix) VALUES(@matrix)

Then sending the matrix through a parameter every time running script B.

--Script B
DECLARE @matrix VARBINARY(MAX)
SELECT @matrix = matrix
FROM MatrixTable

EXECUTE sp_execute_external_script @language = N'Python'
  , @script = N'
preprocessed_matrix = pickle.loads(matrix)
...
'
  , @input_data_1 = N'SOME SELECT QUERY'
  , @params = N'@matrix VARBINARY(MAX)'
  , @matrix = @matrix

Because the matrix is processed only once and it loads multiple times, so I think it could be great if script A runs on server starts and stored the resulting matrix in sql memory that can be accessed from script B without save and load from a table. Is there a way to store the matrix in memory so I don't need to save it to a table and load it using pickle to make it faster?



from sp_execute_external_script Python In Memory Variable for Faster Process

No comments:

Post a Comment