preload package to improve performance while using sp_execute_external_script RRS feed

  • Question

  • Hello,

    We use sp_execute_external_script to access run r scripts as follow:

     DECLARE @rscript NVARCHAR(MAX);
      SET @rscript = N'
    model <- prophet(weekly.seasonality = TRUE, yearly.seasonality = FALSE, seasonality.mode = "additive")
      EXEC sp_execute_external_script
        @language = N'R',
        @script = @rscript

    We need to load the library prophet every single time, which takes about 40sec . Is there a way to preload a given library to decrease the overhead?

    => I have tried to add the line " library(prophet) " at the end of but it is throwing an error after I restart SQL Server.

    => I have also tried to add prophet to the defaultPackages as follow:

     options(defaultPackages=c(getOption("defaultPackages"), "rpart", "lattice", "RevoScaleR", 
                if(!identical(system.file(package="mrsdeploy"), "") && identical(.Platform$OS, "windows")) "mrsdeploy", # we have MicrosoftML package

    if(!identical(system.file(package="MicrosoftML"),"") && isMMLSupported) "MicrosoftML", 
    "RevoMods", "RevoUtils", "RevoUtilsMath","prophet"))

    But I am getting the following error:

    During startup - Warning message:
    package 'prophet' in options("defaultPackages") was not found 

    Thank you for your help!

    • Edited by dirichle Wednesday, January 30, 2019 7:14 PM
    Wednesday, January 30, 2019 7:04 PM

All replies

  • Hi,

    Your first approach of editing should work and it should not take that long to load the library. Can you run a quick test please - please launch 'R.exe' from "C:\Program Files\Microsoft SQL Server\<YourInstance>\R_SERVICES\bin" after modifying the and see if there are any errors or if that still takes 40 secs?

    Also, please verify that the prophet and its dependencies are correctly installed under "C:\Program Files\Microsoft SQL Server\<YourInstance>\R_SERVICES\library"?

    What is the error that you mention above? And do you get that only when you restart SQL Server?



    Thursday, January 31, 2019 7:17 PM