Way to get full @@rowcount even when using "top"?
-
Is there a way that I can use, say, "select top 10 * from Table1", or "set rowcount 10 select * from Table1", and yet STILL get the actual number of records that would be returned if I didn't set rowcount to 10 or use "top 10", without running the query twice? @@rowcount is always equal to 10 when I do this, but there are hundreds of records in my real table. I'm using this in a reporting system where the user can limit (preview) the number of records to whatever they want, yet we still want to show the number of records that would be returned if there was no limit without running the report query twice. Thx -Vic
-
I'm curious to see what other people are gonna say but I think you'll have to run a second query to get the actual count. Or run the query once and send it to a temp table, giving you the actual row count... then select top 10 from that table. But you still do 2 selects and an insert on top of that (which may not be that bad if the table is huge or the query very complex).
-
As Remi said, this is still running 2 queries, just one inside another....
use pubs
go
select top 10 au_lname, au_fname,
(select count(*) from authors) as num
from authors
Steve
-
Won't this query be run multiple times
(select count(*) from authors) since it's a sub query
?
if I run this
use northwind
go
SET STATISTICS IO ON
select top 10 *, (select count(*) from Orders) as num from Orders
select top 10 * from Orders
SET STATISTICS IO OFF
I get an 87% / 13% load... and 85% of the load of the first query goes for the count(*). The strange thing is that when I do top 500 it's more balanced (69%/31% and only 52% for count(*))... can't explain that one though except that it may be considered as a deterministic query and not reran after a few executions.
It think I would test both my method of the temp table to get the count without rerunning the query and 2 separate queries that don't use a temp table to see which is the fastest. I assume that the more complexe the search is, the more efficient the temp table becomes (read @table variable here instead of temp table).
-
agreed with the combo approach of @table and multiple queries to getting the data you want. Something similiar to this.
Declare @table table ( MyColumn varchar(50), iCount int, iTotal int )
Insert into @table
( MyColumn, iCount )
select C.ContactTitle, Count(*)
from dbo.Customers C with (nolock)
Group By C.ContactTitle
Update @table set iTotal = ( select Count(*) From dbo.Customers with (nolock) )
select * From @table
The better part of the query load here appears to be the: Update @table set iTotal = ( select Count(*) From dbo.Customers with (nolock) )
-
Actually what he wants to accomplish is :
insert into @Table
Select * from ?? where .....
set @MyRowCount = @@RowCount
Select top 10 * from @Table
-
understood what he wants to accomplish. Seems to me that there is less load adding only the records he needs to the temp table, then updating the table with the count of all records, rather than add all records to the temp table, then derive from that.
Lets take a look and see
-
lol .. well, I guess that is why you are an addict and I am a mere grasshopper.
When run as a batch.
Declare @table table ( MyColumn varchar(50), iCount int, iTotal int )
Insert into @table
( MyColumn, iCount )
select C.ContactTitle, Count(*)
from dbo.Customers C with (nolock)
Group By C.ContactTitle
Update @table set iTotal = ( select Count(*) From dbo.Customers with (nolock) )
select * From @table
Declare @table2 table ( MyColumn varchar(50), iCustID varchar(10) )
Declare @MyRowCount int
Insert into @table2
( MyColumn, iCustID )
select C.ContactTitle, C.CustomerID
from Customers C with (nolock)
Set @MyRowCount = @@RowCount
Select top 10 @MyRowCount, *
From @table2
Your solution uses less resources. Better way to measure this than ExecutionPlan?
-
Looks like we're saying the same thing.
which of these is the closer to what you are thinking?
execute search into temp table without top 10
gives @@rowcount
select top 10 from temp table
or
Execute search with top 10 and send directly to user
rerun query without top 10 and only count(*)
-
Thanks for the responses guys. Actually what I was looking for was a magic bullet, something like, just check this system var and it gives you the total! it's unfortunate that there is no way to get that value easily. These queries I am running for the reports are quite complex with up to 5 or 6 inner & outer joins in them & returning potentially 10's of thousands of records, so running the query twice is not really an option we want to go with at this point. However, I'm thinking that Remi's option makes the most sense here. At least inserting into a table var once with the full query & where clause, then getting that @@rowcount & saving it to a var, then selecting the top X from the table var out without a where clause, appears to be the cheapest idea yet. Thanks! -Vic
-
Execution plan + profiler with DBCC DROPCLEANBUFFERS between batches is usually what I use.
However a good rule of thumb is to move as little data as possibble and also to avoid rerunning very long queries... The only way to see which weighs more in this case is to test with his data to see what yields best performance. Anything more said from here is merely a guess and unhelpfull to him. So I'll stop typing
. -
I've run in a situation like this before. Is the data from the report static or can it be changed in the execution of the report?
if the data is static, you can create a permanent report table and requery this table.
pseudo code
create permanent table that holds the result of the search
add column Search_id
create permanent table Searches
SearchDate
SearchCriteria1
SearchCriteria2
SearchCriteria3
TotalLines
sp that makes the search :
check if the search has already been run and that the results are still valid : if yes then select from the report table
if not
create the search in the search table
make the full 6 tables inner join and send the data in the report table and link back to the search
in all cases
select the first 10 lines in the reports table joined to the search table for the total row count (or use @@rowcount if you just inserted the data)
you could even add an identity column to the report table and make it the clustered index (combined with the search_id) so that you could select the next 10 ids that are greater than the last presented id for the current search... those selects would be based on a single clustered index so it would be much faster than a complex 6 table joins ran 2 times for each page.
-
Great idea Remi, but alas the report data is very dynamic. In fact, a single proc is used to execute different queries that are already stored in a table for each report. It's all data dictionary driven, and our customers essentially "change the where clause" by picking criteria in dropdowns for each column in the output of the report. It's actually quite slick and performs rather well. My mgr wanted to see if this was possible without rerunning the query and I couldn't make anything with "top" or "set rowcount" work. We will be moving this off our OLTP system and starting a data mart that the customers will use for the reports, so I might implement an idea of your type above down the road! -Vic
-
HTH... too bad my last idea couldn't work in this case. I guess checking if a search like the current one has been cached, and adding where conditions to the cache from there is out of the question... but it would be a nice project to try to make happen someday
. -
I would greatly caution you against using the temp table. Check out the two scripts below; the 1st set of statements costs 30% of the total cost. This will scale to larger recordsets as well (and those with where clauses), even more so when you are putting a large number of records into the temp table.
If you need to count on one row, plus the 10 rows as a sample, you definitely need to run your query twice. I've run into this specific problem (same as you, using dynamic SQL to create the where clause and needing a sample and a total count), tested it heavily, and came to the conclusion that two queries against the underlying table are better than using an intermediate table. Of course this assumes that the underlying table is heavily indexed (which it sure better be!).
If you decide you MUST use a temp table, only store the primary key value in this table, then join back to the original table to get the full top 10 records (see below for example).
---------------------------------------------------------------------------------------
use pubs
go
---1st Version
select count(*) from authors
select top 10 *
from authors
----2nd Version
Declare @table table ( Au_id varchar(50))
Insert into @table
(Au_id)
select Au_id
from authors with (nolock)
select Count(*) From @table
select a.*
From @table t
JOIN authors a on t.au_id = a.au_id
Signature is NULL
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply