We’d like you to change the specification of SPSS Modeler 18.1 around the SQL statement syntax check while accessing to Azure SQL Data Warehouse. Currently Modeler uses the stored procedure called “sp_prepare “ which has big overhead because of generating an execution plan on SQL Data Warehouse. So it is necessary to change to “SET PARSEONLY ON” + SQL statement that is only for syntax check.
Tens of our staffs deeply use SPSS Modeler and most of SQL statements which it generates are commonly very large. When we did performance test using Modeler with Azure SQL Data Warehouse, we found “sp_prepare” was called several times before executing a SQL statement and it took a long time to call the stored procedure. So we asked Microsoft to suggest us and they said below.
You should not use “sp_prepare” only for syntax check because it makes an execution plan and should change to use “SET PARSEONLY ON” + SQL statement. This “SET PARSEONLY” statement has been supported since SQL Server 2008. And it is also supported to both Azure SQL Database and Azure SQL Data Warehouse.
This issue also impacts the performance of short SQL statements which Modeler generates. The overhead of “sp_prepare” stands out since those SQL statements perform quickly.
Real Scenario + Problem Statement
|Who would benefit from this IDEA?|
How should it work?
|Geo - Use for OBDR Reporting only|
Why is it useful?
NOTICE TO EU RESIDENTS: per EU Data Protection Policy, if you wish to remove your personal information from the IBM ideas portal, please login to the ideas portal using your previously registered information then change your email to "firstname.lastname@example.org" and first name to "anonymous" and last name to "anonymous". This will ensure that IBM will not send any emails to you about all idea submissions