Poor Query performance when scalar variables used in Query
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)
sql-server sql-server-2012 query-performance parameter-sniffing
edited 4 mins ago
Paul White♦
51.5k14278450
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
add a comment |
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)
sql-server sql-server-2012 query-performance parameter-sniffing
edited 4 mins ago
Paul White♦
51.5k14278450
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
Depending on how it issues queries, you could be hitting parameter sniffing or an unknown estimate. This might help.
– Erik Darling
Apr 27 '18 at 11:29
2
Cool. Head over here if you want instructions on how to get more help with that.
– Erik Darling
Apr 27 '18 at 11:52
Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected?
– Denis Rubashkin
Apr 27 '18 at 13:38
Are the actual data fields allintfields? 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.
– Laughing Vergil
Apr 27 '18 at 21:04
add a comment |
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)
sql-server sql-server-2012 query-performance parameter-sniffing
edited 4 mins ago
Paul White♦
51.5k14278450
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
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)
sql-server sql-server-2012 query-performance parameter-sniffing
sql-server sql-server-2012 query-performance parameter-sniffing
edited 4 mins ago
Paul White♦
51.5k14278450
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
edited 4 mins ago
Paul White♦
51.5k14278450
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
edited 4 mins ago
Paul White♦
51.5k14278450
edited 4 mins ago
Paul White♦
51.5k14278450
edited 4 mins ago
Paul White♦
51.5k14278450
51.5k14278450
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
asked Apr 27 '18 at 11:22
The CoderThe Coder
1967
1967
Depending on how it issues queries, you could be hitting parameter sniffing or an unknown estimate. This might help.
– Erik Darling
Apr 27 '18 at 11:29
2
Cool. Head over here if you want instructions on how to get more help with that.
– Erik Darling
Apr 27 '18 at 11:52
Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected?
– Denis Rubashkin
Apr 27 '18 at 13:38
Are the actual data fields allintfields? 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.
– Laughing Vergil
Apr 27 '18 at 21:04
add a comment |
Depending on how it issues queries, you could be hitting parameter sniffing or an unknown estimate. This might help.
– Erik Darling
Apr 27 '18 at 11:29
2
Cool. Head over here if you want instructions on how to get more help with that.
– Erik Darling
Apr 27 '18 at 11:52
Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected?
– Denis Rubashkin
Apr 27 '18 at 13:38
Are the actual data fields allintfields? 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.
– Laughing Vergil
Apr 27 '18 at 21:04
Depending on how it issues queries, you could be hitting parameter sniffing or an unknown estimate. This might help.
– Erik Darling
Apr 27 '18 at 11:29
Depending on how it issues queries, you could be hitting parameter sniffing or an unknown estimate. This might help.
– Erik Darling
Apr 27 '18 at 11:29
2
2
Cool. Head over here if you want instructions on how to get more help with that.
– Erik Darling
Apr 27 '18 at 11:52
Cool. Head over here if you want instructions on how to get more help with that.
– Erik Darling
Apr 27 '18 at 11:52
Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected?
– Denis Rubashkin
Apr 27 '18 at 13:38
Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected?
– Denis Rubashkin
Apr 27 '18 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.– Laughing Vergil
Apr 27 '18 at 21:04
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.– Laughing Vergil
Apr 27 '18 at 21:04
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f205159%2fpoor-query-performance-when-scalar-variables-used-in-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f205159%2fpoor-query-performance-when-scalar-variables-used-in-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Depending on how it issues queries, you could be hitting parameter sniffing or an unknown estimate. This might help.
– Erik Darling
Apr 27 '18 at 11:29
2
Cool. Head over here if you want instructions on how to get more help with that.
– Erik Darling
Apr 27 '18 at 11:52
Look at the execution plan in xml format. In the down of the file check ParameterList node - are the values such as expected?
– Denis Rubashkin
Apr 27 '18 at 13:38
Are the actual data fields all
intfields? 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.– Laughing Vergil
Apr 27 '18 at 21:04