How to get some results mid query using SqlCommand
I am using SqlCommand to perform a query, however this query is very slow in general - taking about 50 seconds to complete - is there anyway I can read the results as they come in one by one?
using (SqlConnection connection = new SqlConnection(ExportMetrics.CreateConnectionString()))
{
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Read results
}
catch (Exception e)
{
//Exception
}
finally
{
reader.Close();
}
}
}
c# sqlconnection sqlcommand
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
add a comment |
I am using SqlCommand to perform a query, however this query is very slow in general - taking about 50 seconds to complete - is there anyway I can read the results as they come in one by one?
using (SqlConnection connection = new SqlConnection(ExportMetrics.CreateConnectionString()))
{
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Read results
}
catch (Exception e)
{
//Exception
}
finally
{
reader.Close();
}
}
}
c# sqlconnection sqlcommand
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
1
Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: docs.microsoft.com/en-us/dotnet/api/…
– paulsm4
Nov 20 '18 at 17:23
1
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group.
– Nathan Champion
Nov 20 '18 at 17:29
add a comment |
I am using SqlCommand to perform a query, however this query is very slow in general - taking about 50 seconds to complete - is there anyway I can read the results as they come in one by one?
using (SqlConnection connection = new SqlConnection(ExportMetrics.CreateConnectionString()))
{
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Read results
}
catch (Exception e)
{
//Exception
}
finally
{
reader.Close();
}
}
}
c# sqlconnection sqlcommand
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
I am using SqlCommand to perform a query, however this query is very slow in general - taking about 50 seconds to complete - is there anyway I can read the results as they come in one by one?
using (SqlConnection connection = new SqlConnection(ExportMetrics.CreateConnectionString()))
{
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
//Read results
}
catch (Exception e)
{
//Exception
}
finally
{
reader.Close();
}
}
}
c# sqlconnection sqlcommand
c# sqlconnection sqlcommand
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
edited Nov 20 '18 at 17:26
Joel Coehoorn
309k95494728
309k95494728
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
asked Nov 20 '18 at 17:21
AlfieAlfie
10010
10010
1
Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: docs.microsoft.com/en-us/dotnet/api/…
– paulsm4
Nov 20 '18 at 17:23
1
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group.
– Nathan Champion
Nov 20 '18 at 17:29
add a comment |
1
Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: docs.microsoft.com/en-us/dotnet/api/…
– paulsm4
Nov 20 '18 at 17:23
1
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group.
– Nathan Champion
Nov 20 '18 at 17:29
1
1
Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: docs.microsoft.com/en-us/dotnet/api/…
– paulsm4
Nov 20 '18 at 17:23
Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: docs.microsoft.com/en-us/dotnet/api/…
– paulsm4
Nov 20 '18 at 17:23
1
1
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group.
– Nathan Champion
Nov 20 '18 at 17:29
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group.
– Nathan Champion
Nov 20 '18 at 17:29
add a comment |
1 Answer
1
active
oldest
votes
Reading results "as they come in one by one" is what an SqlDataReader already does.
Whether this helps depends on the query. Queries with GROUP BY+aggregate functions or ORDER BY1 clauses generally have to materialize the entire result set in server memory before they can begin returning results. Other language constructs might require this, too. Queries without those language features may be able to start returning results faster, depending on the execution plan.
But 50 seconds is a looooong time in the world of SQL. There's almost always a way to re-write the query or tune indexes to run much faster. Of course, we can't help with that unless we can see the query, the table structure, and the indexing all listed as part of the question.
1 If the ORDER BY clause matches the primary key or other important indexes relative to JOINs, such that the requested order matches the order used for the query's working set, and this can be mathematically shown, you might still be okay.
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
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
});
}
});
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%2f53398279%2fhow-to-get-some-results-mid-query-using-sqlcommand%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
Reading results "as they come in one by one" is what an SqlDataReader already does.
Whether this helps depends on the query. Queries with GROUP BY+aggregate functions or ORDER BY1 clauses generally have to materialize the entire result set in server memory before they can begin returning results. Other language constructs might require this, too. Queries without those language features may be able to start returning results faster, depending on the execution plan.
But 50 seconds is a looooong time in the world of SQL. There's almost always a way to re-write the query or tune indexes to run much faster. Of course, we can't help with that unless we can see the query, the table structure, and the indexing all listed as part of the question.
1 If the ORDER BY clause matches the primary key or other important indexes relative to JOINs, such that the requested order matches the order used for the query's working set, and this can be mathematically shown, you might still be okay.
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
add a comment |
Reading results "as they come in one by one" is what an SqlDataReader already does.
Whether this helps depends on the query. Queries with GROUP BY+aggregate functions or ORDER BY1 clauses generally have to materialize the entire result set in server memory before they can begin returning results. Other language constructs might require this, too. Queries without those language features may be able to start returning results faster, depending on the execution plan.
But 50 seconds is a looooong time in the world of SQL. There's almost always a way to re-write the query or tune indexes to run much faster. Of course, we can't help with that unless we can see the query, the table structure, and the indexing all listed as part of the question.
1 If the ORDER BY clause matches the primary key or other important indexes relative to JOINs, such that the requested order matches the order used for the query's working set, and this can be mathematically shown, you might still be okay.
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
add a comment |
Reading results "as they come in one by one" is what an SqlDataReader already does.
Whether this helps depends on the query. Queries with GROUP BY+aggregate functions or ORDER BY1 clauses generally have to materialize the entire result set in server memory before they can begin returning results. Other language constructs might require this, too. Queries without those language features may be able to start returning results faster, depending on the execution plan.
But 50 seconds is a looooong time in the world of SQL. There's almost always a way to re-write the query or tune indexes to run much faster. Of course, we can't help with that unless we can see the query, the table structure, and the indexing all listed as part of the question.
1 If the ORDER BY clause matches the primary key or other important indexes relative to JOINs, such that the requested order matches the order used for the query's working set, and this can be mathematically shown, you might still be okay.
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
Reading results "as they come in one by one" is what an SqlDataReader already does.
Whether this helps depends on the query. Queries with GROUP BY+aggregate functions or ORDER BY1 clauses generally have to materialize the entire result set in server memory before they can begin returning results. Other language constructs might require this, too. Queries without those language features may be able to start returning results faster, depending on the execution plan.
But 50 seconds is a looooong time in the world of SQL. There's almost always a way to re-write the query or tune indexes to run much faster. Of course, we can't help with that unless we can see the query, the table structure, and the indexing all listed as part of the question.
1 If the ORDER BY clause matches the primary key or other important indexes relative to JOINs, such that the requested order matches the order used for the query's working set, and this can be mathematically shown, you might still be okay.
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
edited Nov 20 '18 at 19:09
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
answered Nov 20 '18 at 17:29
Joel CoehoornJoel Coehoorn
309k95494728
309k95494728
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
add a comment |
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
"Reading results "as they come in one by one" is what an SqlDataReader already does.". Yes - but the reader isn't going to return the FIRST record until the ENTIRE result set is available. In other words, it isn't going to help with the "50 seconds"...
– paulsm4
Nov 20 '18 at 19:08
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
@paulsm4 Yes, it can begin returning results before the entire set is available, under the conditions described in this answer.
– Joel Coehoorn
Nov 20 '18 at 19:09
add a comment |
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.
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%2f53398279%2fhow-to-get-some-results-mid-query-using-sqlcommand%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
1
Short answer: no. It sounds like you need to optimize your query, add indexes, and/or cache your result sets. You can also try making your query asynchronously: docs.microsoft.com/en-us/dotnet/api/…
– paulsm4
Nov 20 '18 at 17:23
1
You may be able to squeak out performance by converting it to a stored procedure, memory optimized tables, indexes, etc. Really depends what your query looks like and why the query is slow but you can't look at those records they're all returned as a group.
– Nathan Champion
Nov 20 '18 at 17:29