Subject Re: V2.1.3 Faster than a speeding bullet
Author Peter Bailey

A bit more information, but not answering all of your questions.

The program is a very simple, single function program. All it does is
open the database, launch a query and generate an XML file from the
returned data. It is running as a desk-top, stand-alone program, in a
test environment with no other activity affecting the DB.

The program is running using the embedded DLL's and I am unaware of how
to determine the Plans being used. Perhaps you can assist on that score
... I have Flame Robbin and the light versions of IBExpert & DataBase
Workbench installed.

I have re-run the program with a slight modification to ensure that the
times measure the everything... from opening the XML file and the query
to closing the XML file. The times are a little longer than previously
V1.5.5: 460 seconds
V2.1.3: 0.569 seconds
These are the average of 4 runs.
The V1.5.5 runs vary by < ±1% and the V2.1.3 by < ± 7%.

The database statistics are:

Database header page information:
Flags 0
Checksum 12345
Generation 22784
Page size 4096
ODS version 10.1
Oldest transaction 22775
Oldest active 22776
Oldest snapshot 22776
Next transaction 22777
Bumped transaction 1
Sequence number 0
Next attachment ID 287
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Oct 27, 2010 10:04:55
Attributes force write

Variable header data:
Sweep interval: 20000

The select statement is:
vXU.UmpID as "ID",
vXU.FamilyName as "Family",
vXU.GivenName as "Given",
vXU.BirthDate as "DoB",
vXU.RegdClubCode as "Club",
vXU.LastAllocdDate as "Alloc'd",
vXU.EditStmp as "Amend'd",
vXU.PrefGradeCode as "PrefGrade",
vXU.CurrBadgeCode as "Badge",
vXU.CurrBadgeRating as "Rating",
vXU.CurrBadgeDate as "BadgeDate",
vXU.CurrBadgeComment as "BadgeComment",
vXU.AddressLine1 as "AddressLine1",
vXU.AddressLine2 as "AddressLine2",
vXU.Suburb as "Suburb",
vXU.State as "State",
vXU.Postcode as "Postcode",
vXU.PhoneHome as "PhoneHome",
vXU.PhoneWork as "PhoneWork",
vXU.PhoneMobile as "PhoneMobile",
vXU.eMail as "eMail"
vw_XtnddUmpires vXU

vXU.UmpID <> 0
Order by

Need more? ... please let me know.
> 3a.
> Re: V2.1.3 Faster than a speeding bullet
> <;_ylc=X3oDMTJzb2NyMjBpBF9TAzk3MzU5NzE1BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzExMTA1MgRzZWMDZG1zZwRzbGsDdm1zZwRzdGltZQMxMjkxNjM0OTY2>
> Posted by: "Svein Erling Tysvær"
> svein.erling.tysvaer@...
> <mailto:svein.erling.tysvaer@...?Subject=%20Re%3A%20V2%2E1%2E3%20Faster%20than%20a%20speeding%20bullet>
> svein_erling <>
> Sun Dec 5, 2010 11:18 pm (PST)
> Hi Peter!
> When I hear about that kind of improvement, I'm thinking that the
> optimizer in your case didn't produce the best plan. Show us the
> select statement and the plan generated by both Firebird 1.5 and 2.1.
> I assume you have executed the statement several times in both
> versions, that you've retrieved the entire result set (as opposed
> to only the first few rows), and that the execution time you
> report are typical figures for the statement (e.g. if you executed
> the statement in Fb 1.5 when a sweep was going on, then that could
> influence the speed, but then it would be difficult to reproduce
> the slow execution).
> Set
> -----Original Message-----
> From:
> <>
> [
> <>] On Behalf Of Peter Bailey
> Sent: 5. desember 2010 05:09
> To: Firebird - support
> Subject: [firebird-support] V2.1.3 Faster than a speeding bullet
> Hello Firebird supporters
> I recall, (I think), that V2.1 is expected to be about 40% faster
> than
> V1.5, so I am very curious about some test results that I have
> generated.
> The scenario is that I have a Delphi program that generates an XML
> file
> from a Select Query.
> Linking to fbembed.dll (gds32.dll) gives the following execution
> times
> for the Select Query:
> V1.5.5: 437 seconds
> V2.1.3: 0.078 seconds
> That makes V2.1.3 aprox. 5,600 times faster than V1.5.5!
> Fantastic numbers for V2.1, but why is it so?
> It may be relevant that the FB database is "small" - 6.5MB - and
> therefore could easily be loaded into RAM, (4GB). The database
> structure
> is ODS 10.1.
> Thanks for any information.
> Peter

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