Limiting SQL return
-
Good day all,
Strange question:
We have a VB & .Net applications connecting to the database, executing sprocs in order to get the information that it needs, do what it needs, etc.
Basically we have noticed that the Application server has been starting to take a bit of hit, performance wise. We have identified that there are some queries that are having a large amount of data returned to the APP server, 10MB and going up, and the APP server is taking sometime to render this.
Something that we we would like to put in, is that only the TOP 500 records are returned, instead of everything. We would also like to add in something that says 500 rows of possible 250 000 returned.
So basically our query will look something like this:
SELECT TOP 500 {column1}, {column2}, {column3}
FROM dbo.{table_name}
WHERE....
--
The problem is that @@ROWCOUNT only returns 500 (for those with 500 or more records). I need the full COUNT based on the FROM and WHERE clause.
I am not too keen to put in a SELECT count(*) and duplicating the FROM and WHERE.
Any ideas? Anyone do this in the past?
Thanks in advance,
Graham
-
It's pretty much a case of eating the cake and still keeping it - can't have both..
If you want to limit the rows returned, then TOP or SET ROWCOUNT n is what you can do.
If you also want to know how many rows could have been returned, that's pretty hard to get the server to tell you without actaully asking it to count for you. Thus, you must either return all rows (the thing you don't want to do) or count according the the WHERE clause and return that number...
..between a rock and a hard place
/Kenneth
-
Yeah, well thats the problem, rock - hard place...
It is unfortunately the answer that I expected.
As a matter of interest, is there any real difference between TOP or SET ROWCOUNT? Pros or cons for either, or is it the same thing?
Will have test if the count is worthwhile, and how much of a problem it is going to be versus passing all the data back to the APP, and letting it do all the work.
Personally, I think the COUNT is probably going to be easiest!
Thanks for the response, much appreciated!
Graham
-
I don't think there's any difference between TOP and SET ROWCOUNT in functionality.
If both are used, then ROWCOUNT overrides TOP if it's smaller.
Also, MS recommends TOP over ROWCOUNT to be used.
There seems to be some differences between the two in 7.0 vs 2k versions also.
BOL has more info.
The area you can gain something from doing a separate count(*) vs sending the entire set to the client and count the total there, is that the former doesn't travel all data over the wire. If it's of any significant difference will depend much on the volumes.
/Kenneth
-
I would use the RETURN statement in my stored procedure to return the full count to the calling app. If you're using the SqlDataAdapter, I would declare an integer variable called RetValue in my app and add a parameter to capture the return value sent back by the RETURN statement, like:
SqlCommand.Parameters.Add(
New SqlParameter("RetValue", SqlDbType.Int)).Direction = ParameterDirection.ReturnValue
The last line in my stored procedure would be:
RETURN (SELECT COUNT(column_name) FROM dbo.{table_name} WHERE....)
-
For the count, use:
Select count (1) from tbl where...
It's generally slightly more efficient.
-
"select count(indexed_column) from table" is probably your best bet where indezxed column is the narrowest available to get a row count.
However select count(*) just prior to your actual query will possibly give you an execution speed advantage since a very large number, if not all, of the data pages needed for the real query that returns data will already be cached.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
One thing which has helped in doing some things like this for us is using a temp table or table var depending on the size of the data.
This way you get a much smaller table and can count the rows in the temp table without locking up your main table.
SELECT {column1}, {column2}, {column3} into #temp
FROM dbo.{table_name}
WHERE....
select {column1}, {column2}, {column3}
from #temp
and
select count(*)
from #temp
-
FORGET ABOUT "TOP"!!!
Select Identity (int, 1, 1), <key columns>
INTO #TempTable
FROM SomeTable
ORDER BY <desired order>
SELECT T.*
FROM SomeTable T
INNER JOIN TempTable TT ON T.<key columns> = TT.<key columns>
WHERE TT.ID between @StartRow and @EndRow
_____________
Code for TallyGenerator -
You should never use RETURN for other stuff than return (ie status) codes. If you decide to use return for returning stuff like rowcount, you no longer have the ability to trap errorcodes.
Instead you should use OUTPUT parameters to return values from procedures, and let return just return the 'normal' returncodes.
/Kenneth
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply