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)
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.