0

We have a table [adj].[Assortment Snapshot] in our SQL Server database with the follwing data types:

[Customer UUID] [uniqueidentifier] NOT NULL,
[Branch Number] [int] NOT NULL,
[Article Number] [int] NOT NULL,
[Valid On] [date] NOT NULL,
[Comment] [nvarchar](max) NULL

I send a query to this table with dbSendStatement()...

<OdbcResult>
  SQL  SELECT [Customer UUID] AS [Customer],
[Branch Number] AS [Branch],
[Valid On] AS [Date],
[Article Number] AS [Article] FROM adj.[Assortment Snapshot] WHERE [Customer UUID] = ? AND [Branch Number] IN (?) AND [Valid On] >= ? AND [Valid On] <= ?
  ROWS Fetched: 0 [complete]
       Changed: 0

...followed by dbBind() to bind the following parameters:

> print(lstParams)
$param_1_crit_1
[1] "10fb9494-8bc0-11eb-8dcd-0242ac130003"

$param_2_crit_2
[1] 100

$param_3_crit_3
[1] "2021-03-24"

$param_4_crit_4
[1] "2021-04-06"

The parameters are of the following classes:

> sapply(lstParams, class)
param_1_crit_1 param_2_crit_2 param_3_crit_3 param_4_crit_4 
   "character"      "integer"         "Date"         "Date"

Checking the server logs, this is the query that I get:

declare @p1 int
set @p1=12
exec sp_prepexec @p1 output,

N'@P1 uniqueidentifier,@P2 int,@P3 nvarchar(10),@P4 nvarchar(10)',

N'SELECT [Customer UUID] AS [Customer],
[Branch Number] AS [Branch],
[Valid On] AS [Date],
[Article Number] AS [Article] 

FROM adj.[Assortment Snapshot] WHERE [Customer UUID] = @P1 AND [Branch Number] IN (@P2) AND [Valid On] >= @P3 AND [Valid On] <= @P4','ED5FADA7-90E9-409F-B210-85E61528E120',3038,N'2021-03-24',N'2021-04-06'
select @p1

While the parameter [Customer UUID] is correctly recognized as uniqueidentifier despite having been passed as character by dbBind(), the dates are turned into nvarchar(10). Queries involving table-valued SQL functions even result in all parameters being turned into varchar(255).

These conversions mean a huge performance problem: Executing the above SQL query in SQL Server Management Studio returns results almost immediately. The same query executed via dbSendStatement() and dbBind() takes several seconds to minutes.

How can the conversions described above be avoided?

  • SQL Server version: 15.00.4102
  • R version: Microsoft R Open 4.0.2
  • DBI package version: 1.1.1
scn
  • 11
  • 2
  • Have you read through [Implicit data type conversions](https://learn.microsoft.com/en-us/sql/machine-learning/r/r-libraries-and-data-types#implicit-data-type-conversions)? Perhaps you could try `lstParams <- list(param_1_crit_1 = "10fb9494-8bc0-11eb-8dcd-0242ac130003", param_1_crit_2 = as.integer(100), param_1_crit_3 = as.POSIXct("2021-03-24"), param_1_crit_4 = as.POSIXct("2021-04-06") )`? – AlwaysLearning Mar 23 '21 at 10:52
  • Thanks @AlwaysLearning for your quick reply. If I am not mistaken, the documentation you linked to describes the conversion of data retrieved from SQL and imported into R. The problem at hand takes place *before* that point: a query performed by SQL Server with the wrong data types. NB: Using `as.POSIXct()` to convert the dates results in an error `[Microsoft][ODBC SQL Server Driver]Numeric values out of range`. – scn Mar 23 '21 at 11:19

0 Answers0