counting records in select query

  • Thread starter Thread starter Konrad
  • Start date Start date
K

Konrad

Hi

I have complicated select query
how to get prior number of records
returned from this query.
One number, to decide
if records should be loaded.

Thanks
Konrad
 
Hi,

This not a good practice. Counting records has to iterate through all
records on server and you'll probably have to run one select for counting
and the other for returning the actual records.
Why don't you simply limit the select with TOP (or similar) clause?
 
Here's what I do.

I set up a stored procedure, with an output parameter for a record count, in
addition to the select.

I add in a count to the select, and pass that result back and an output
parameter to the c# code.
 
Yes, but the OUTPUT parameter can only be fetched AFTER you fetch all of the
rows.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William said:
Yes, but the OUTPUT parameter can only be fetched AFTER you fetch all of
the rows.
Click to expand...

Yeah, I guess he should use a DataSet instead of a DataReader
 
Iterate through all records isn't big problem in my case
because number of records returned is about to 1000.
And as I see it takes lower than 1 sec.
But I'am using SQL Server over internet and sending
this records is bigger problem. So I want to know
how many records is to send and does this operation should
be completed or not.
Do you know how count number of records to send
having select query?

Best regards
Konrad


Miha Markic said:
Hi,

This not a good practice. Counting records has to iterate through all
records on server and you'll probably have to run one select for counting
and the other for returning the actual records.
Why don't you simply limit the select with TOP (or similar) clause?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Konrad said:
Hi

I have complicated select query
how to get prior number of records
returned from this query.
One number, to decide
if records should be loaded.

Thanks
Konrad
Click to expand...
Click to expand...
 
Yes, but the count might not be particularly accurate and it will hurt
performance.
You can execute a SELECT COUNT(*) with the appropriate WHERE clause to
return a count of the rows that qualify for the rowset. However, this takes
almost the same amount of time (especially for complex queries) to run the
query. This means it will cost nearly twice as much as executing the query
without counting the rows first. In addition, since the number of member
rows could change from the time you count to the time you query, the count
could be off--way off. For simple queries counting is fine--but it just
hurts scalability.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Konrad said:
Iterate through all records isn't big problem in my case
because number of records returned is about to 1000.
And as I see it takes lower than 1 sec.
But I'am using SQL Server over internet and sending
this records is bigger problem. So I want to know
how many records is to send and does this operation should
be completed or not.
Do you know how count number of records to send
having select query?

Best regards
Konrad


Miha Markic said:
Hi,

This not a good practice. Counting records has to iterate through all
records on server and you'll probably have to run one select for counting
and the other for returning the actual records.
Why don't you simply limit the select with TOP (or similar) clause?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Konrad said:
Hi

I have complicated select query
how to get prior number of records
returned from this query.
One number, to decide
if records should be loaded.

Thanks
Konrad
Click to expand...
Click to expand...
Click to expand...
 
My query to counting records looks like:
"SELECT COUNT( DISTINCT Means.Name) FROM Doc_items INNER JOIN Doc_headers ON
Doc_items.Document = Doc_headers.ID INNER JOIN Means ON Doc_items.Means =
Means.ID WHERE (Doc_headers.Doc_type LIKE 'S') "

oryginal query looks like:

SELECT SUM(Doc_items.Quantity),SUM(Doc_items.Value),Means.Unit,Means.Name
FROM Doc_items INNER JOIN Means ON Doc_items.Means = Means.ID INNER JOIN "+

"Doc_headers ON Doc_items.Document = Doc_headers.ID WHERE
(Doc_headers.Doc_type LIKE 'S') GROUP BY Means.Unit,Means.Name"

The query counting records is not accurate because it doesn't take itno
account Means.Unit but only Means.Name.

Really Means.Name is much bigger than Means.Unit so results are no t so bad.

But how to know what number of records will be returned from secon query?

Using Count(*) gives numer o records form all resulting rows separately.

I want sum of this.

Best regards

Konrad

William (Bill) Vaughn said:
Yes, but the count might not be particularly accurate and it will hurt
performance.
You can execute a SELECT COUNT(*) with the appropriate WHERE clause to
return a count of the rows that qualify for the rowset. However, this takes
almost the same amount of time (especially for complex queries) to run the
query. This means it will cost nearly twice as much as executing the query
without counting the rows first. In addition, since the number of member
rows could change from the time you count to the time you query, the count
could be off--way off. For simple queries counting is fine--but it just
hurts scalability.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Konrad said:
Iterate through all records isn't big problem in my case
because number of records returned is about to 1000.
And as I see it takes lower than 1 sec.
But I'am using SQL Server over internet and sending
this records is bigger problem. So I want to know
how many records is to send and does this operation should
be completed or not.
Do you know how count number of records to send
having select query?

Best regards
Konrad


Miha Markic said:
Hi,

This not a good practice. Counting records has to iterate through all
records on server and you'll probably have to run one select for counting
and the other for returning the actual records.
Why don't you simply limit the select with TOP (or similar) clause?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Hi

I have complicated select query
how to get prior number of records
returned from this query.
One number, to decide
if records should be loaded.

Thanks
Konrad
Click to expand...
Click to expand...
Click to expand...
 
How about this: SELECT INTO a #Temp table using the aggregates--but not
return the rows. This will save your save (but not return) your rowset.
Next, execute a COUNT(*) against the #Temp table this returns the rows
generated from the aggregate query. Remember #temp tables created by a SP
belong to the SP and are dropped after the SP.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Konrad said:
My query to counting records looks like:
"SELECT COUNT( DISTINCT Means.Name) FROM Doc_items INNER JOIN Doc_headers
ON
Doc_items.Document = Doc_headers.ID INNER JOIN Means ON Doc_items.Means =
Means.ID WHERE (Doc_headers.Doc_type LIKE 'S') "

oryginal query looks like:

SELECT SUM(Doc_items.Quantity),SUM(Doc_items.Value),Means.Unit,Means.Name
FROM Doc_items INNER JOIN Means ON Doc_items.Means = Means.ID INNER JOIN
"+

"Doc_headers ON Doc_items.Document = Doc_headers.ID WHERE
(Doc_headers.Doc_type LIKE 'S') GROUP BY Means.Unit,Means.Name"

The query counting records is not accurate because it doesn't take itno
account Means.Unit but only Means.Name.

Really Means.Name is much bigger than Means.Unit so results are no t so
bad.

But how to know what number of records will be returned from secon query?

Using Count(*) gives numer o records form all resulting rows separately.

I want sum of this.

Best regards

Konrad

William (Bill) Vaughn said:
Yes, but the count might not be particularly accurate and it will hurt
performance.
You can execute a SELECT COUNT(*) with the appropriate WHERE clause to
return a count of the rows that qualify for the rowset. However, this takes
almost the same amount of time (especially for complex queries) to run
the
query. This means it will cost nearly twice as much as executing the
query
without counting the rows first. In addition, since the number of member
rows could change from the time you count to the time you query, the
count
could be off--way off. For simple queries counting is fine--but it just
hurts scalability.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Konrad said:
Iterate through all records isn't big problem in my case
because number of records returned is about to 1000.
And as I see it takes lower than 1 sec.
But I'am using SQL Server over internet and sending
this records is bigger problem. So I want to know
how many records is to send and does this operation should
be completed or not.
Do you know how count number of records to send
having select query?

Best regards
Konrad


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Hi,

This not a good practice. Counting records has to iterate through all
records on server and you'll probably have to run one select for counting
and the other for returning the actual records.
Why don't you simply limit the select with TOP (or similar) clause?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Hi

I have complicated select query
how to get prior number of records
returned from this query.
One number, to decide
if records should be loaded.

Thanks
Konrad
Click to expand...
Click to expand...
Click to expand...
 
Back
Top