SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Retrieving the estimation row count in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sundaresan
Starting Member

India
28 Posts

Posted - 08/12/2008 :  09:24:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 08/12/2008 :  09:27:30  Show Profile  Reply with Quote
There is no sys tables or view for it:

You can do like:

select count(*) from
(your join query) a
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/12/2008 :  09:30:12  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

Sundaresan
Starting Member

India
28 Posts

Posted - 08/12/2008 :  09:36:43  Show Profile  Reply with Quote
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 08/12/2008 :  10:04:25  Show Profile  Visit spirit1's Homepage  Reply with Quote
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!
Go to Top of Page

Sundaresan
Starting Member

India
28 Posts

Posted - 08/13/2008 :  03:14:24  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 08/13/2008 :  03:26:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Sundaresan
Starting Member

India
28 Posts

Posted - 08/13/2008 :  03:57:49  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 08/13/2008 :  04:00:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000