Retrieving the estimation row count in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Retrieving the estimation row count in a table

Author

Topic

Sundaresan
Starting Member

28 Posts

Posted - 2008-08-12 : 09:24:28

Hi,

I have an requirement like Im having a table A which joins with Table B.
Both the tables are very large in size.
Now i need to retrieve the estiamted rowcount without actually executing the query by joining two tables.

Im able to see the estimated row count using

SET SHOWPLAN_ALL ON
GO
select a.BU_USER_ID,a.CORP_ID,c.UP_USER_NME,c.UP_FIRST_NME

from table1 a, table2 c

where a.corp_id=c.corp_id
GO
SET SHOWPLAN_ALL OFF
GO

I need to retrieve the estimated row count from the ouptut into a table.

Because my logic is like first i will check the estimated row count of the joined tables, if that is with the accepatable limit i will process the query else i wont.

Is there any sys table or view in which i can view the estimated row count of the estimated plan ??
Please help me .. Its a bit urgent

Thnks,
Sundar.R

Sundaresan.R

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 09:27:30

There is no sys tables or view for it:

You can do like:

select count(*) from
(your join query) a

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 09:30:12

you can do :
select count(id) from table1 where <yourcondition>
and if that count is larger that something not execute whole join.
if you have a non fragmented index on your id this operation should be very fast.

and do use JOIN syntax and not the old and deprecated "from table1, table2 where ...."

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Sundaresan
Starting Member

28 Posts

Posted - 2008-08-12 : 09:36:43

Thanks for the reply.

Consider for example by joining two tables the total actual row count is 10,00,000 rows.

select count(a.*) from table A, Table B where a.cond_id=b.cond_id

For attaining this count, will take time to return the rowcounts.

But my criteria is like without actually taking the count of records in a table i need to execute a condition based on the estimated row count.

Thnx,
Sundar.R

Sundaresan.R

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-12 : 10:04:25

then no. you can't.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Sundaresan
Starting Member

28 Posts

Posted - 2008-08-13 : 03:14:24

Hi,
Thanks for ur Reply.

May i know then how the estimated execution plan is showing Estimated Row count.

There must be some system tables which captures the data.
Please advice me

Sundaresan.R

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 03:26:25

For an estimate you can try this

SELECT		object_name(id),
		MAX(rowcnt)AS EstimatedRecords
FROM		sysindexes
GROUP BY	object_name(id)
ORDER BY	object_name(id)

E 12°55'05.25"
N 56°04'39.16"

Sundaresan
Starting Member

28 Posts

Posted - 2008-08-13 : 03:57:49

Hi,

Your suggesstion will work if i need the total rows in a table
What if there are conditions and joins with other tables?

I need the estimated count of rows during such conditions.

Please advice

Sundaresan.R

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 04:00:48

There is no such way, as told many times before.
What SQL Server does, when displaying the Estimated execution plan is that it look at indexes, statistics and many other things to deliver an estimate.

Talk to Microsoft and see if they are willing to give that piece of code to you.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources