C# SQLite Bulk parameterized DELETE slow even when using a transaction?
0
The following code is taking ~2 minutes to delete 30k records which I am sure is too long. Most of the similar questions I have seen on here have been solved by using a SQLiteTransaction object, but I am already doing that.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
using (var cmd = new SQLiteCommand("DELETE FROM Proxy WHERE IP=@ip AND Port=@port", conn, trans))
{
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
cmd.Parameters.AddWithValue("@ip", proxy.IP);
cmd.Parameters.AddWithValue("@port", proxy.Port);
cmd.ExecuteNonQuery();
proxies.Remove(proxy);
}
}
trans.Commit();
}
}
}
And here is the CREATE statement for the Proxy table.
CREATE TABLE "Proxy"
(
`ProxyID` INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
`Status` TEXT,
`IP` TEXT,
`Port` INTEGER,
`Country` TEXT,
`Speed` INTEGER,
`DateAdded` TEXT
)
c# sqlite
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
add a comment |
0
The following code is taking ~2 minutes to delete 30k records which I am sure is too long. Most of the similar questions I have seen on here have been solved by using a SQLiteTransaction object, but I am already doing that.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
using (var cmd = new SQLiteCommand("DELETE FROM Proxy WHERE IP=@ip AND Port=@port", conn, trans))
{
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
cmd.Parameters.AddWithValue("@ip", proxy.IP);
cmd.Parameters.AddWithValue("@port", proxy.Port);
cmd.ExecuteNonQuery();
proxies.Remove(proxy);
}
}
trans.Commit();
}
}
}
And here is the CREATE statement for the Proxy table.
CREATE TABLE "Proxy"
(
`ProxyID` INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
`Status` TEXT,
`IP` TEXT,
`Port` INTEGER,
`Country` TEXT,
`Speed` INTEGER,
`DateAdded` TEXT
)
c# sqlite
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
You should build DELETE statement that includes information about all rows to delete at once and make sure your deletes does not trigger UI update until all rows are deleted
– MoreThanChaos
Nov 17 '18 at 5:38
@MoreThanChaos Can you post example code as an answer and if it works I can mark it as solved? I believe I follow what you are saying, like using a StringBuilder to create one giant DELETE statement? As far as your second statement, yes the 'proxies' object is a BindingList that is a DataSource of a DGV, but even when I comment out proxies.Remove(proxy) it is still very slow.
– David Stampher
Nov 17 '18 at 5:40
1
Yes it is simple as that, If one giant statement will be too much make it into parts of 500 or 200. Sorry but for now i'm unable to provide you with more complete answer.
– MoreThanChaos
Nov 17 '18 at 5:47
@MoreThanChaos So I just tried your suggestion and it seems to have made a significant difference to just build one large statement. Thanks!
– David Stampher
Nov 17 '18 at 6:11
add a comment |
0
0
0
The following code is taking ~2 minutes to delete 30k records which I am sure is too long. Most of the similar questions I have seen on here have been solved by using a SQLiteTransaction object, but I am already doing that.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
using (var cmd = new SQLiteCommand("DELETE FROM Proxy WHERE IP=@ip AND Port=@port", conn, trans))
{
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
cmd.Parameters.AddWithValue("@ip", proxy.IP);
cmd.Parameters.AddWithValue("@port", proxy.Port);
cmd.ExecuteNonQuery();
proxies.Remove(proxy);
}
}
trans.Commit();
}
}
}
And here is the CREATE statement for the Proxy table.
CREATE TABLE "Proxy"
(
`ProxyID` INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
`Status` TEXT,
`IP` TEXT,
`Port` INTEGER,
`Country` TEXT,
`Speed` INTEGER,
`DateAdded` TEXT
)
c# sqlite
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
The following code is taking ~2 minutes to delete 30k records which I am sure is too long. Most of the similar questions I have seen on here have been solved by using a SQLiteTransaction object, but I am already doing that.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
using (var trans = conn.BeginTransaction())
{
using (var cmd = new SQLiteCommand("DELETE FROM Proxy WHERE IP=@ip AND Port=@port", conn, trans))
{
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
cmd.Parameters.AddWithValue("@ip", proxy.IP);
cmd.Parameters.AddWithValue("@port", proxy.Port);
cmd.ExecuteNonQuery();
proxies.Remove(proxy);
}
}
trans.Commit();
}
}
}
And here is the CREATE statement for the Proxy table.
CREATE TABLE "Proxy"
(
`ProxyID` INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
`Status` TEXT,
`IP` TEXT,
`Port` INTEGER,
`Country` TEXT,
`Speed` INTEGER,
`DateAdded` TEXT
)
c# sqlite
c# sqlite
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
edited Nov 17 '18 at 8:48
marc_s
573k12811071254
573k12811071254
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
asked Nov 17 '18 at 5:29
David StampherDavid Stampher
1,31541541
1,31541541
You should build DELETE statement that includes information about all rows to delete at once and make sure your deletes does not trigger UI update until all rows are deleted
– MoreThanChaos
Nov 17 '18 at 5:38
@MoreThanChaos Can you post example code as an answer and if it works I can mark it as solved? I believe I follow what you are saying, like using a StringBuilder to create one giant DELETE statement? As far as your second statement, yes the 'proxies' object is a BindingList that is a DataSource of a DGV, but even when I comment out proxies.Remove(proxy) it is still very slow.
– David Stampher
Nov 17 '18 at 5:40
1
Yes it is simple as that, If one giant statement will be too much make it into parts of 500 or 200. Sorry but for now i'm unable to provide you with more complete answer.
– MoreThanChaos
Nov 17 '18 at 5:47
@MoreThanChaos So I just tried your suggestion and it seems to have made a significant difference to just build one large statement. Thanks!
– David Stampher
Nov 17 '18 at 6:11
add a comment |
You should build DELETE statement that includes information about all rows to delete at once and make sure your deletes does not trigger UI update until all rows are deleted
– MoreThanChaos
Nov 17 '18 at 5:38
@MoreThanChaos Can you post example code as an answer and if it works I can mark it as solved? I believe I follow what you are saying, like using a StringBuilder to create one giant DELETE statement? As far as your second statement, yes the 'proxies' object is a BindingList that is a DataSource of a DGV, but even when I comment out proxies.Remove(proxy) it is still very slow.
– David Stampher
Nov 17 '18 at 5:40
1
Yes it is simple as that, If one giant statement will be too much make it into parts of 500 or 200. Sorry but for now i'm unable to provide you with more complete answer.
– MoreThanChaos
Nov 17 '18 at 5:47
@MoreThanChaos So I just tried your suggestion and it seems to have made a significant difference to just build one large statement. Thanks!
– David Stampher
Nov 17 '18 at 6:11
You should build DELETE statement that includes information about all rows to delete at once and make sure your deletes does not trigger UI update until all rows are deleted
– MoreThanChaos
Nov 17 '18 at 5:38
You should build DELETE statement that includes information about all rows to delete at once and make sure your deletes does not trigger UI update until all rows are deleted
– MoreThanChaos
Nov 17 '18 at 5:38
@MoreThanChaos Can you post example code as an answer and if it works I can mark it as solved? I believe I follow what you are saying, like using a StringBuilder to create one giant DELETE statement? As far as your second statement, yes the 'proxies' object is a BindingList that is a DataSource of a DGV, but even when I comment out proxies.Remove(proxy) it is still very slow.
– David Stampher
Nov 17 '18 at 5:40
@MoreThanChaos Can you post example code as an answer and if it works I can mark it as solved? I believe I follow what you are saying, like using a StringBuilder to create one giant DELETE statement? As far as your second statement, yes the 'proxies' object is a BindingList that is a DataSource of a DGV, but even when I comment out proxies.Remove(proxy) it is still very slow.
– David Stampher
Nov 17 '18 at 5:40
1
1
Yes it is simple as that, If one giant statement will be too much make it into parts of 500 or 200. Sorry but for now i'm unable to provide you with more complete answer.
– MoreThanChaos
Nov 17 '18 at 5:47
Yes it is simple as that, If one giant statement will be too much make it into parts of 500 or 200. Sorry but for now i'm unable to provide you with more complete answer.
– MoreThanChaos
Nov 17 '18 at 5:47
@MoreThanChaos So I just tried your suggestion and it seems to have made a significant difference to just build one large statement. Thanks!
– David Stampher
Nov 17 '18 at 6:11
@MoreThanChaos So I just tried your suggestion and it seems to have made a significant difference to just build one large statement. Thanks!
– David Stampher
Nov 17 '18 at 6:11
add a comment |
1 Answer
1
active
oldest
votes
0
Building one long SQLite statement with a StringBuilder and only executing the SQLiteCommand object once sped things up significantly.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
var sb = new StringBuilder();
sb.Append("DELETE FROM Proxy WHERE ProxyID IN (");
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
sb.Append(proxy.ProxyID + ",");
}
sb[sb.Length - 1] = ')';
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
cmd.ExecuteNonQuery();
}
}
}
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
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%2f53348531%2fc-sharp-sqlite-bulk-parameterized-delete-slow-even-when-using-a-transaction%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
0
Building one long SQLite statement with a StringBuilder and only executing the SQLiteCommand object once sped things up significantly.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
var sb = new StringBuilder();
sb.Append("DELETE FROM Proxy WHERE ProxyID IN (");
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
sb.Append(proxy.ProxyID + ",");
}
sb[sb.Length - 1] = ')';
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
cmd.ExecuteNonQuery();
}
}
}
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
add a comment |
0
Building one long SQLite statement with a StringBuilder and only executing the SQLiteCommand object once sped things up significantly.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
var sb = new StringBuilder();
sb.Append("DELETE FROM Proxy WHERE ProxyID IN (");
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
sb.Append(proxy.ProxyID + ",");
}
sb[sb.Length - 1] = ')';
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
cmd.ExecuteNonQuery();
}
}
}
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
add a comment |
0
0
0
Building one long SQLite statement with a StringBuilder and only executing the SQLiteCommand object once sped things up significantly.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
var sb = new StringBuilder();
sb.Append("DELETE FROM Proxy WHERE ProxyID IN (");
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
sb.Append(proxy.ProxyID + ",");
}
sb[sb.Length - 1] = ')';
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
cmd.ExecuteNonQuery();
}
}
}
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
Building one long SQLite statement with a StringBuilder and only executing the SQLiteCommand object once sped things up significantly.
private void removeProxiesButton_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Properties.Settings.Default.dbConnectionString))
{
conn.Open();
var sb = new StringBuilder();
sb.Append("DELETE FROM Proxy WHERE ProxyID IN (");
foreach (DataGridViewRow row in proxiesDataGridView.SelectedRows)
{
var proxy = proxies[row.Index];
sb.Append(proxy.ProxyID + ",");
}
sb[sb.Length - 1] = ')';
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
cmd.ExecuteNonQuery();
}
}
}
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
answered Nov 17 '18 at 6:16
David StampherDavid Stampher
1,31541541
1,31541541
add a comment |
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%2f53348531%2fc-sharp-sqlite-bulk-parameterized-delete-slow-even-when-using-a-transaction%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
You should build DELETE statement that includes information about all rows to delete at once and make sure your deletes does not trigger UI update until all rows are deleted
– MoreThanChaos
Nov 17 '18 at 5:38
@MoreThanChaos Can you post example code as an answer and if it works I can mark it as solved? I believe I follow what you are saying, like using a StringBuilder to create one giant DELETE statement? As far as your second statement, yes the 'proxies' object is a BindingList that is a DataSource of a DGV, but even when I comment out proxies.Remove(proxy) it is still very slow.
– David Stampher
Nov 17 '18 at 5:40
1
Yes it is simple as that, If one giant statement will be too much make it into parts of 500 or 200. Sorry but for now i'm unable to provide you with more complete answer.
– MoreThanChaos
Nov 17 '18 at 5:47
@MoreThanChaos So I just tried your suggestion and it seems to have made a significant difference to just build one large statement. Thanks!
– David Stampher
Nov 17 '18 at 6:11