June 20, 2012 at 5:37 am
June 20, 2012 at 5:45 am
the count of table rows are materialized in sys.indexes, and are accurate for the heap tables or clustered tables;
that's the way to count rows quickly:
SELECT so.[name] as
, CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END
AS [Index Name]
, si.indid, rows
FROM sys.sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id
WHERE si.indid < 2
AND so.type = 'U' -- Only User Tables
AND so.[name] != 'dtproperties'
ORDER BY so.[name]
another example:
Select OBJECT_NAME(object_id) as TableName,SUM(rows) as NumRows,index_id
From sys.partitions p
Inner Join sys.sysobjects o
on p.object_id = o.id
Where index_id in (0,1)
And o.type = 'U'
Group By object_id,index_id
Order By NumRows Desc
Lowell
June 20, 2012 at 5:59 am
Thanks for the reply Lowell.
I was looking for something a little more simple.
I started playing a little and came up with the following two queries of doing it without Count():
--Creating Table
Create Table Ex
(COLUMN1 int,
Customer_Zip_Code bigint,
Total int )
--Inserting Sample Data
Insert into Ex
Select 40228,40228, 37
union ALL
Select 47130,471308050, 35
union ALL
Select 47119,47119, 30
union ALL
Select 47104,47104, 26
union ALL
Select 47203,47203, 26
union ALL
Select 47111,471111047, 21
union ALL
Select 47172,471728933, 19
union ALL
Select 47112,47112, 19
union ALL
Select 47145,47145, 17
union ALL
Select 47130,47130, 15
--First Query
Select Top 1 rn From
(Select *, ROW_NUMBER() Over (Order by (Select NULL) ) As rn From Ex ) As a
Order by rn Desc
--Second Query
Select Max(rn) From
(Select *, ROW_NUMBER() Over (Order by (Select NULL) ) As rn From Ex ) As a
It was surprisingly easy. I didn't think it would be this easy. Mind block...I guess.
Anyways, thanks a lot Lowell for the reply.
I'll do a little more research to understand your approach.:-)
June 20, 2012 at 6:27 am
Why are you trying to count without count?
Take this table:
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
REPLACE(CAST(NEWID() AS VARCHAR(36)),'-',' ') AS string
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @HOLDER INT;
Let's compare your two queries to a simple count.
PRINT '========== COUNT ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== FIRST QUERY ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP 1 @HOLDER = rn
FROM (SELECT *, ROW_NUMBER() Over (Order by (Select NULL) ) As rn
FROM #testEnvironment) As a
ORDER BY rn DESC;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SECOND QUERY ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = MAX(rn)
FROM (SELECT *, ROW_NUMBER() Over (Order by (Select NULL) ) As rn
FROM #testEnvironment) AS a;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
What do you think the results are going to show? 😉
========== COUNT ==========
Table '#testEnvironment'. Scan count 5, logical reads 6580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 23 ms.
================================================================================
========== FIRST QUERY ==========
Table '#testEnvironment'. Scan count 1, logical reads 6580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 610 ms, elapsed time = 339 ms.
================================================================================
========== SECOND QUERY ==========
Table '#testEnvironment'. Scan count 1, logical reads 6580, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 265 ms, elapsed time = 274 ms.
================================================================================
So count is 10x faster.
June 20, 2012 at 6:39 am
<pedantic mode on>
sysindexes and sysobjects are deprecated, should not be used in new development, will be removed from a future version of SQl Server, are included only for backward compatibility with SQL 2000.
Use sys.indexes and sys.objects instead, and in 2005+ the row count for tables (per partition) is in sys.partitions and sys.dm_db_partition_stats.
If you're trying to count the rows and don't want to rely on the cached metadata (sys.partitions), then use Count(*). It is optimised to be the fastest way to actually count the rows in a table and it is highly unlikely that any alternative is going to be faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2012 at 7:52 pm
vinu512 (6/20/2012)It was surprisingly easy. I didn't think it would be this easy. Mind block...I guess.
Yeah, but can you do it in less than 90 keystrokes?
DECLARE @CR INT = 0
UPDATE Ex SET @CR = @CR + 1
SELECT @CR
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 20, 2012 at 9:51 pm
Yes. The best option is always Count(). I agree on that with everyone.
This was not a Work related requirement. This was just a conceptual thing that I wanted to clear.
Someone asked me this and I suddenly went blank. My senior gave me 10 minutes to find out. I couldn't think of anything that time(the mind block I was talking about). After I posted this thread I suddenly realized that it was really easy and came up with the two Row_Number() queries.
It was seriously embarrassing that it took me so much time for something that was so simple.
Dwayne, that is a smart way of doing it too....Thanks for all your help guys.
June 20, 2012 at 9:56 pm
The smartest way is also usually the fastest way.
In this case, that would be picking it up out of the systables.
I was just having a bit of fun. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply