ExecuteNonQuery Takes more time to delete nearly 7000 records, How can I improve performance?
1
I am executing below query using ADO.NET. It takes nearly 60s to execute. At the same time when I run the query in SQL Server management studio, it takes only 1 second. Why is the huge difference and How can I improve the performance?
I tried the Stored procedure also. it is also taking nearly 60s.
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
c#
sql-server ado.net asked Nov 14 '18 at 8:25
Irf92Irf92
15810
|
show 6 more comments
1
I am executing below query using ADO.NET. It takes nearly 60s to execute. At the same time when I run the query in SQL Server management studio, it takes only 1 second. Why is the huge difference and How can I improve the performance?
I tried the Stored procedure also. it is also taking nearly 60s.
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
c#
sql-server ado.net asked Nov 14 '18 at 8:25
Irf92Irf92
15810
then use the store procedure instead. revise the code, add a new parameter @id to the store procedure, and call the store procedure from your code with the list of ids. rather than inserting the query inside a loop to be executed on each id.
– iSR5
Nov 14 '18 at 8:33
@iSR5 I don't see a loop; I see a PK/FK-based delete - looks fine; OP already says they've tried an SP, and hint: SPs don't change anything major (since something like 2003?)
– Marc Gravell♦
Nov 14 '18 at 8:34
2
Is it possible that this is a bad plan cache due to parameter sniffing? can you try addingOPTION (OPTIMIZE FOR (@Id UNKNOWN))to the three deletes?
– Marc Gravell♦
Nov 14 '18 at 8:37
1
7000is actually not that much to take that long.
– SeM
Nov 14 '18 at 8:49
2
@SeM I'd be disappointed if it took 6 seconds... 7000 rows should be sub-second
– Marc Gravell♦
Nov 14 '18 at 8:59
|
show 6 more comments
1
1
1
I am executing below query using ADO.NET. It takes nearly 60s to execute. At the same time when I run the query in SQL Server management studio, it takes only 1 second. Why is the huge difference and How can I improve the performance?
I tried the Stored procedure also. it is also taking nearly 60s.
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
c#
sql-server ado.net asked Nov 14 '18 at 8:25
Irf92Irf92
15810
I am executing below query using ADO.NET. It takes nearly 60s to execute. At the same time when I run the query in SQL Server management studio, it takes only 1 second. Why is the huge difference and How can I improve the performance?
I tried the Stored procedure also. it is also taking nearly 60s.
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
c#
sql-server ado.net c#
sql-server ado.net asked Nov 14 '18 at 8:25
Irf92Irf92
15810
asked Nov 14 '18 at 8:25
Irf92Irf92
15810
edited Nov 14 '18 at 8:27
Irf92
asked Nov 14 '18 at 8:25
Irf92Irf92
15810
asked Nov 14 '18 at 8:25
Irf92Irf92
15810
asked Nov 14 '18 at 8:25
Irf92Irf92
15810
15810
then use the store procedure instead. revise the code, add a new parameter @id to the store procedure, and call the store procedure from your code with the list of ids. rather than inserting the query inside a loop to be executed on each id.
– iSR5
Nov 14 '18 at 8:33
@iSR5 I don't see a loop; I see a PK/FK-based delete - looks fine; OP already says they've tried an SP, and hint: SPs don't change anything major (since something like 2003?)
– Marc Gravell♦
Nov 14 '18 at 8:34
2
Is it possible that this is a bad plan cache due to parameter sniffing? can you try addingOPTION (OPTIMIZE FOR (@Id UNKNOWN))to the three deletes?
– Marc Gravell♦
Nov 14 '18 at 8:37
1
7000is actually not that much to take that long.
– SeM
Nov 14 '18 at 8:49
2
@SeM I'd be disappointed if it took 6 seconds... 7000 rows should be sub-second
– Marc Gravell♦
Nov 14 '18 at 8:59
|
show 6 more comments
then use the store procedure instead. revise the code, add a new parameter @id to the store procedure, and call the store procedure from your code with the list of ids. rather than inserting the query inside a loop to be executed on each id.
– iSR5
Nov 14 '18 at 8:33
@iSR5 I don't see a loop; I see a PK/FK-based delete - looks fine; OP already says they've tried an SP, and hint: SPs don't change anything major (since something like 2003?)
– Marc Gravell♦
Nov 14 '18 at 8:34
2
Is it possible that this is a bad plan cache due to parameter sniffing? can you try addingOPTION (OPTIMIZE FOR (@Id UNKNOWN))to the three deletes?
– Marc Gravell♦
Nov 14 '18 at 8:37
1
7000is actually not that much to take that long.
– SeM
Nov 14 '18 at 8:49
2
@SeM I'd be disappointed if it took 6 seconds... 7000 rows should be sub-second
– Marc Gravell♦
Nov 14 '18 at 8:59
then use the store procedure instead. revise the code, add a new parameter @id to the store procedure, and call the store procedure from your code with the list of ids. rather than inserting the query inside a loop to be executed on each id.
– iSR5
Nov 14 '18 at 8:33
then use the store procedure instead. revise the code, add a new parameter @id to the store procedure, and call the store procedure from your code with the list of ids. rather than inserting the query inside a loop to be executed on each id.
– iSR5
Nov 14 '18 at 8:33
@iSR5 I don't see a loop; I see a PK/FK-based delete - looks fine; OP already says they've tried an SP, and hint: SPs don't change anything major (since something like 2003?)
– Marc Gravell♦
Nov 14 '18 at 8:34
@iSR5 I don't see a loop; I see a PK/FK-based delete - looks fine; OP already says they've tried an SP, and hint: SPs don't change anything major (since something like 2003?)
– Marc Gravell♦
Nov 14 '18 at 8:34
2
2
Is it possible that this is a bad plan cache due to parameter sniffing? can you try adding
OPTION (OPTIMIZE FOR (@Id UNKNOWN)) to the three deletes?– Marc Gravell♦
Nov 14 '18 at 8:37
Is it possible that this is a bad plan cache due to parameter sniffing? can you try adding
OPTION (OPTIMIZE FOR (@Id UNKNOWN)) to the three deletes?– Marc Gravell♦
Nov 14 '18 at 8:37
1
1
7000 is actually not that much to take that long.– SeM
Nov 14 '18 at 8:49
7000 is actually not that much to take that long.– SeM
Nov 14 '18 at 8:49
2
2
@SeM I'd be disappointed if it took 6 seconds... 7000 rows should be sub-second
– Marc Gravell♦
Nov 14 '18 at 8:59
@SeM I'd be disappointed if it took 6 seconds... 7000 rows should be sub-second
– Marc Gravell♦
Nov 14 '18 at 8:59
|
show 6 more comments
1 Answer
1
active
oldest
votes
1
I tried this below. This is happening because of Parameter Sniffing. To overcome this issue, There are 4 methods
- OPTION (RECOMPILE)
- OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- Use local variables
Below I have used the 4th method to fix this issue. referred
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
Declare @Id int = @PId ; -- passing the parameter only here
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
Thanks for everyone to make the better answer
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
1
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use theUNKNOWNoption
– Marc Gravell♦
Nov 14 '18 at 9:20
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
});
}
});
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53295800%2fexecutenonquery-takes-more-time-to-delete-nearly-7000-records-how-can-i-improve%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I tried this below. This is happening because of Parameter Sniffing. To overcome this issue, There are 4 methods
- OPTION (RECOMPILE)
- OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- Use local variables
Below I have used the 4th method to fix this issue. referred
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
Declare @Id int = @PId ; -- passing the parameter only here
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
Thanks for everyone to make the better answer
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
1
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use theUNKNOWNoption
– Marc Gravell♦
Nov 14 '18 at 9:20
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
add a comment |
1
I tried this below. This is happening because of Parameter Sniffing. To overcome this issue, There are 4 methods
- OPTION (RECOMPILE)
- OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- Use local variables
Below I have used the 4th method to fix this issue. referred
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
Declare @Id int = @PId ; -- passing the parameter only here
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
Thanks for everyone to make the better answer
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
1
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use theUNKNOWNoption
– Marc Gravell♦
Nov 14 '18 at 9:20
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
add a comment |
1
1
1
I tried this below. This is happening because of Parameter Sniffing. To overcome this issue, There are 4 methods
- OPTION (RECOMPILE)
- OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- Use local variables
Below I have used the 4th method to fix this issue. referred
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
Declare @Id int = @PId ; -- passing the parameter only here
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
Thanks for everyone to make the better answer
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
I tried this below. This is happening because of Parameter Sniffing. To overcome this issue, There are 4 methods
- OPTION (RECOMPILE)
- OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
- OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
- Use local variables
Below I have used the 4th method to fix this issue. referred
SqlCommand sc = new SqlCommand(@"
BEGIN TRAN
BEGIN TRY
Declare @Id int = @PId ; -- passing the parameter only here
DELETE FROM [dbo].[Table1]
WHERE ID = @Id ; -- nearly 600 records
DELETE FROM [dbo].[Table2]
WHERE ID = @Id ; -- nearly 6500 records
DELETE FROM [dbo].[Table3]
WHERE ID = @Id; -- 1 record
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
THROW
END CATCH
");
Thanks for everyone to make the better answer
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
edited Nov 19 '18 at 8:17
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
answered Nov 14 '18 at 9:07
Irf92Irf92
15810
15810
1
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use theUNKNOWNoption
– Marc Gravell♦
Nov 14 '18 at 9:20
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
add a comment |
1
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use theUNKNOWNoption
– Marc Gravell♦
Nov 14 '18 at 9:20
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
1
1
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use the
UNKNOWN option– Marc Gravell♦
Nov 14 '18 at 9:20
Note: this isn't a fix for parameter sniffing - all you've done is change the query, so now you've sniffed/cached a different initial value; the fix is - as I put in the comments - to use the
UNKNOWN option– Marc Gravell♦
Nov 14 '18 at 9:20
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
That query doesn't change anything. The parameter is passed only once in both queries.
– Panagiotis Kanavos
Nov 14 '18 at 10:23
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@PanagiotisKanavos it works for me. the execution plan of the SQL server is different for both cases.
– Irf92
Nov 14 '18 at 11:57
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@Irf92 no, it worked for that parameter you used. Those are the very symptoms of parameter sniffing. Once you use a different parameter you'll run into the same problem.
– Panagiotis Kanavos
Nov 14 '18 at 12:04
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
@PanagiotisKanavos updated answer.thanks
– Irf92
Nov 19 '18 at 8:18
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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.
draft saved
draft discarded
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%2fstackoverflow.com%2fquestions%2f53295800%2fexecutenonquery-takes-more-time-to-delete-nearly-7000-records-how-can-i-improve%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
then use the store procedure instead. revise the code, add a new parameter @id to the store procedure, and call the store procedure from your code with the list of ids. rather than inserting the query inside a loop to be executed on each id.
– iSR5
Nov 14 '18 at 8:33
@iSR5 I don't see a loop; I see a PK/FK-based delete - looks fine; OP already says they've tried an SP, and hint: SPs don't change anything major (since something like 2003?)
– Marc Gravell♦
Nov 14 '18 at 8:34
2
Is it possible that this is a bad plan cache due to parameter sniffing? can you try adding
OPTION (OPTIMIZE FOR (@Id UNKNOWN))to the three deletes?– Marc Gravell♦
Nov 14 '18 at 8:37
1
7000is actually not that much to take that long.– SeM
Nov 14 '18 at 8:49
2
@SeM I'd be disappointed if it took 6 seconds... 7000 rows should be sub-second
– Marc Gravell♦
Nov 14 '18 at 8:59