2

We're using TSQL with R on Microsoft SQL Server 2016 in a project.

The problem is the execution time, the usage of R or the connection take a random time between 200ms and 600ms.

I think it's a configuration problem but I've never used this before.

Example of request:

EXEC sp_execute_external_script    
      @language = N'R'    
    , @script = N' 
         OutputDataSet <- as.data.frame(rnorm(100, mean = 50, sd =3));'    
    , @input_data_1 = N'   ;'    
      WITH RESULT SETS (([Density] float NOT NULL));

Execute time this take between 200ms and 2000ms.

If anyone have already used this kind of technology and have some tips to optimize the performance?

N.B : In our project we're using this kind of request dynamically so, there's a lot of request executed and we're waiting all results to display them. It take a lot of time. I hope it's jsut a configuration of ssms problem

Alexis
  • 5,681
  • 1
  • 27
  • 44

1 Answers1

0

The execution time for R scripts can vary depending on whether an R process is already available for script execution or not. You can validate this by looking for running Rterm.exe processes using Task Manager (or Process Explorer).

enter image description here

So the behavior you will notice is that first request may be slow (~2000ms) and the subsequent requests will be faster (~200ms).

Arun Gurunathan
  • 306
  • 1
  • 5
  • 1
    As seen above, R scripts runs outside of SQL Server process. Due to overhead to send data between processes and additional R interpreter execution time, it is not feasible to have the same performance as native SQL. Can you explain your scenario and requirement for ~10-40ms briefly? Is your R scripts doing predictions or other kind of data analysis? – Arun Gurunathan Jun 16 '17 at 01:20
  • We're just creating dynamically the R scripts depending of a lot of parameters and using some native function of R for some calculation. Apparently, it's not the good usage, we expect use this as a api service but the perf are too bad for this – Alexis Jun 16 '17 at 07:09