1

Facing a strange issue that a query runs incredibly slow (worse) when scalar variables are used as parameter.

Actual Query (Faster) - 9 secs

select
  sum(v.sale_value) as col_0_0_,
  sum(v.vat_value)  as col_1_0_,
  sum(v.sale_fcy)   as col_2_0_,
  sum(v.vat_fcy)    as col_3_0_
from my_view v
where v.company_id = 1 and v.filing_year = 2018 and v.filing_month = 1;

Parameterized Query (Worse) - 16 mins

DECLARE @P0 int, @P1 int, @P2 int;
SET @P0 = 1;
SET @P1 = 2018;
SET @P2 = 1;
select
  sum(v.sale_value) as col_0_0_,
  sum(v.vat_value)  as col_1_0_,
  sum(v.sale_fcy)   as col_2_0_,
  sum(v.vat_fcy)    as col_3_0_
from my_view v
where v.company_id = @P0 and v.filing_year = @P1 and v.filing_month = @P2;

Execution times for both queries were calculated by executing in command line client (IntelliJ Database console).

Query with this scalar variable is generated by our Hibernate Application as we've used setParameter(String name, Object value) to set the param values.

First, we suspected issue in hibernate generated query, but narrowed down to SQL Server query execution after identifying it as parameterized query using sys.dm_exec_cached_plans. Does query optimiser get confused when scalar variables are used?

In java code, we're setting the values as query parameters. In sys.dm_exec_cached_plans, the actual query is:

(@P0 int,@P1 int,@P2 int)select <columns> from my_view v
where v.company_id= @P0 and v.filing_year= @P1 and v.filing_month= @P2

Note: my_view is pretty complex (nested views, group by, joins, order by, where clause...). We tried to compare the execution plans, but they're incredibly huge We are looking into it but might take some time.

If I run the slow query with OPTION (RECOMPILE), it executes in 9 secs.

SQL Server Version

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
    Feb 10 2012 19:39:15 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
2
  • Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected? Apr 27, 2018 at 13:38
  • Are the actual data fields all int fields? I have encountered problems with query plan generation in this type of query when the values passed in are not the same data type as the values in the database. If the field is tinyint, use a tinyint parameter, and so on. Apr 27, 2018 at 21:04

0