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