Hi!

Q : How can I estimate the number of rows that will be returned from query (without execution)?

It takes too much time finding it by using your first answer.

This can be done by examining query plan. But, my problem is, I do not know how to access execution plan from inside of a program. (There is a tool provided by http://www.donia.com/ (DS SQL Tool). It does what exactly I want to do. I think I will have to buy it, itīs no problem (not expensive) but this became an obsession for me :( )

Kind regards, FA

>I have to stick with my answer on Q1. Presuming that you have a >clustered or non-clustered index on the c2 and c1 columns, to >determine counts like you desire, you will have to create a query >that does an index scan of some sort (either clustered or non- >clustered). If that takes too long, the answers to the other >questions are moot.

-- Anonymous, July 03, 2001

Answers

Fuat,

Please share with this forum how well DS SQL Tools from http://www.donia.com works for you.

I'm dubious of its value. I could not get it to work, it hung my personal web server, I found no customer reviews of the product, it was not mentioned in usenet postings, and it has not been updated to work with SQL Server 2000.

Thanks,

Eric

-- Anonymous, July 06, 2001

Actually, I did not try the software from Donia. I found another way to do same thing (Thanks to Tom Cooper). But, I did not try that way either :-) Because, I no longer need that estimation :-)

Cheers, Fuat.

Possible solution (not compiled): ---------------------------------

Dim oDatabase As SQLDMO.Database Dim oTheResults As SQLDMO.QueryResults Dim oPlanResults As SQLDMO.QueryResults Dim dEstimate As Double Set oDatabase = oSQLServer.Databases("pubs", "dbo")

'Set Showplan on and get the estimate oSQLServer.ExecuteImmediate("Set ShowPlan_All On") Set oPlanResults = oDatabase.ExecuteWithResults("SELECT * from authors") Set dEstimate = oPlanResults.GetColumnDouble(1,9) 'I used (1,9) since EstimateRows is the ninth column in the first row oSQLServer.ExecuteImmediate("Set ShowPlan_All Off")

' Now do the real query Set oTheResults = oDatabase.ExecuteWithResults("SELECT * from authors")

-- Anonymous, July 08, 2001

Fuat,

Thank you for adding Tom Cooper's suggestion to this forum.

FYI, I did a little checking into how the EstimateRows is calculated for ShowPlan.

The way it works is like this. Using the pubs authors table for example, if you want to know the estimated number of rows that will be returned for the query:

select * from authors where au_lname between 'A' and 'M' and au_fname between 'D' and 'O'

ShowPlan uses the distribution statistics to determine that, based on the last name alone, there are 12 rows where au_lname between 'A' and 'M'. Now comes the undocumented part (as far as I could find). Statistics are also kept on the distibution of first name alone! Thus, we know that there are 11 rows where au_fname between 'D' and 'O'. To calculate its estimate then, ShowPlan multiplies the total number of rows (23) by the chances of finding a row based on last name alone (12/23) and then by the chances of finding a row based on first name alone (11/23) to get 23 * 12/23 * 11/23 = 5.74.

The actual number in this case is 5.

I hope this shows both the value and the limitations of this method of estimation.

(I'm sure that the Donia.com product merely passes the EstimateRows value back.)

Eric

-- Anonymous, July 10, 2001