July 26, 2011 at 11:29 am
Hello Folks,
I am facing a strange problem.
As part of a big solution. I have to validate each record of an object (can be a view or Table) in batches. (the batch size is fixed number like 10 K ). while making the records into batches I have to know the number of records in that object . I am facing the issues.
I have an identity column on all the tables so no issue if the object is a Table. I am getting count by using this query SELECT IDENT_CURRENT ('dbo.Customer' ).
But for the if the object is a view, then I am facing the issue. Couple of views are returning 3 million records, for views I wrote the query like this " SELECT COUNT(1) FROM dbo.VwCustomer". and this query is taking almost 8-10 minutes and facing timeout issues. one more thing the view " dbo.VwCustomer " has very complex select statements.
Please help me; how to get the record count for that view in an efficient way.
Thanks in advance.
July 26, 2011 at 11:37 am
anand_vanam (7/26/2011)
I have an identity column on all the tables so no issue if the object is a Table. I am getting count by using this query SELECT IDENT_CURRENT ('dbo.Customer' ).
That assumes you've never had a failed insert and never had a delete. Dangerous assumption. Rather query sys.partitions for the row count.
Please help me; how to get the record count for that view in an efficient way.
SELECT count(*) from ViewName
It's the only way. If it's taking a long time it means your view is not very efficient and queries that use it are performing badly too.
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
July 26, 2011 at 11:43 am
i suspected what gail said, where you have to use a SELECT * FROM VIEWNAME was the only way to do rowcounts for views.
for tables, you can use the indexes to get the coutns really quickly for tables:
--edited for accuracy: thanks Gail!
SELECT
S.name AS schemaname,
T.name AS tablename,
SUM(P.rows) AS rows
FROM
sys.partitions P
INNER JOIN sys.tables T
ON P.object_Id = T.object_id
INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE P.index_id IN (0,1)
GROUP BY P.object_Id,P.index_id ,S.name ,T.name
Lowell
July 26, 2011 at 11:45 am
Lowell, to be completely accurate you should sum that grouped by object_id, index_id. If the table is partitioned, there will be multiple rows in there for a single table.
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
July 26, 2011 at 11:50 am
GilaMonster (7/26/2011)
Lowell, to be completely accurate you should sum that grouped by object_id, index_id. If the table is partitioned, there will be multiple rows in there for a single table.
As always, Thanks Gail! you help make me a better poster;
I edited my example, and it seems to return accurate results, but i didn't create any partitioned tables to test it agaisnt to see for myself.
Thanks!
Lowell
July 26, 2011 at 11:53 am
GilaMonster (7/26/2011)
anand_vanam (7/26/2011)
I have an identity column on all the tables so no issue if the object is a Table. I am getting count by using this query SELECT IDENT_CURRENT ('dbo.Customer' ).That assumes you've never had a failed insert and never had a delete. Dangerous assumption. Rather query sys.partitions for the row count.
Please help me; how to get the record count for that view in an efficient way.
SELECT count(*) from ViewNameIt's the only way. If it's taking a long time it means your view is not very efficient and queries that use it are performing badly too.
Many thanks,
Will change the statement for Tables. and see I can write an efficient statement to create the view.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply