This is an algo I put together that more quickly gets the estimated max
value of a field. I know this is not the estimated

row count, but if I combine it with the average gaps then it will come
close.

This is VB, so ignore the details, but you will get the idea.





Public Function EstimatedFieldMax(strTable As String, _

strField As String, _

lMax As Long, _

lMin As Long, _

lCount As Long, _

lPrecision As Long, _

Optional lNeededChecks As Long) As Long



Dim i As Long

Dim strSQL As String

Dim lMid As Long

Dim lResult As Long

Dim bSetCmd As Boolean



bSetCmd = True



strSQL = "SELECT 1 FROM " & strTable & " WHERE " & strField & " = ?"



Do While lMax > lMin + lPrecision



lMid = (lMin + lMax) \ 2



lResult = -1



For i = lMid To lMid + lCount

lNeededChecks = lNeededChecks + 1

If GetSingleValueWithParams(strSQL, i, bSetCmd) = 1 Then

lMin = i

lResult = i

Exit For

End If

bSetCmd = False

Next i



bSetCmd = False



If lResult = -1 Then

lMax = lMid - 1

End If

Loop



EstimatedFieldMax = lMax



End Function





Sub test()



Dim x As Long



MsgBox EstimatedFieldMax("ENTRY", _

"ENTRY_ID", _

9000000, _

1, _

100, _

100, _

x), , "EstimatedFieldMax"



MsgBox x, , "needed checks"



End Sub







RBS

_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Lucas Franzen
Sent: 30 September 2008 20:03
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] What programming languages and toolkits do
you use to access Firebird?





bartsmissaert@ <mailto:bartsmissaert%40blueyonder.co.uk> blueyonder.co.uk
schrieb:

> Yes, I understand that, but it would still be
> useful to know the tota number of records in a table without having to do
> a count.

That will be kind of tricky, won't it?
You want Firebird to estimate precisely how many valid records the
current trasaction is seeing with having to count them?
Sounds like an very superb clairvoyant algorithm.

Or sth. like this - almost 40 years old:

I read the news today, oh boy,
4000 holes in Blackburn, Lancashire
And though the holes were rather small
They had to count them all -
Now they know how many holes it takes to fill the Albert Hall

;-)

Luc.





[Non-text portions of this message have been removed]