Forget about this one. It worked on the table I was testing with but not on
others and I think I have

a good solution now as posted earlier.



RBS



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of RB Smissaert
Sent: 01 October 2008 01:19
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] What programming languages and toolkits do
you use to access Firebird?



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@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com
[mailto:firebird-support@ <mailto:firebird-support%40yahoogroups.com>
yahoogroups.com] On Behalf Of Lucas Franzen
Sent: 30 September 2008 20:03
To: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
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]





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