Replacing Looped Function with SQL

  • Thread starter Thread starter Hoppy Brewing
  • Start date Start date
H

Hoppy Brewing

I have a looped VBA function that I'd like to replace with a query.
Basically it is a yeast generation counter for a brewery. The yeast from one
fermentation is used for the next one, starting from a fresh pitch
occasionally (generation 0).

The function uses a loop to create a 1-record recordset from a parameter
query. The query looks like:

PARAMETERS parOne Long;
SELECT SubFermentationLogBrew.FermentationLogID,
SubFermentationLogBrew_1.FermentationLogID
FROM (SubFermentationLogBrew INNER JOIN SubBrewingLogTransfer ON
SubFermentationLogBrew.BrewingLogID = SubBrewingLogTransfer.BrewingLogID)
INNER JOIN SubFermentationLogBrew AS SubFermentationLogBrew_1 ON
SubBrewingLogTransfer.YeastFrom = SubFermentationLogBrew_1.FermentationLogID
WHERE (((SubFermentationLogBrew.FermentationLogID)=[parOne]));

SubFermentationLogBrew links Fermentations to Brews. SubBrewingLogTransfer
records the previous Fermentation from which the yeast came.
FermentationLogID is the primary key from the main FermentationLog that is
recorded in the SubBrewingLogTransfer.YeastFrom. That last field is Null at
the 0 generation of the yeast.

The second field's value is fed into the query as a new parameter, looped,
and away we go. It works, but ever since we split the db (server remote on a
fairly new computer, there are watches more powerful than the client's home)
the speed has slowed dramatically.

So I was wondering if there is a way to write a SQL in such a fashion that
all the records of the yeast's lineage will be returned in one recordset. I
assume this will be faster than accessing the server for one record many
times.

Thanks for any help you can give me.
 
Hoppy said:
I have a looped VBA function that I'd like to replace with a query.
Basically it is a yeast generation counter for a brewery. The yeast from one
fermentation is used for the next one, starting from a fresh pitch
occasionally (generation 0).

The function uses a loop to create a 1-record recordset from a parameter
query. The query looks like:

PARAMETERS parOne Long;
SELECT SubFermentationLogBrew.FermentationLogID,
SubFermentationLogBrew_1.FermentationLogID
FROM (SubFermentationLogBrew INNER JOIN SubBrewingLogTransfer ON
SubFermentationLogBrew.BrewingLogID = SubBrewingLogTransfer.BrewingLogID)
INNER JOIN SubFermentationLogBrew AS SubFermentationLogBrew_1 ON
SubBrewingLogTransfer.YeastFrom = SubFermentationLogBrew_1.FermentationLogID
WHERE (((SubFermentationLogBrew.FermentationLogID)=[parOne]));

SubFermentationLogBrew links Fermentations to Brews. SubBrewingLogTransfer
records the previous Fermentation from which the yeast came.
FermentationLogID is the primary key from the main FermentationLog that is
recorded in the SubBrewingLogTransfer.YeastFrom. That last field is Null at
the 0 generation of the yeast.

The second field's value is fed into the query as a new parameter, looped,
and away we go. It works, but ever since we split the db (server remote on a
fairly new computer, there are watches more powerful than the client's home)
the speed has slowed dramatically.

So I was wondering if there is a way to write a SQL in such a fashion that
all the records of the yeast's lineage will be returned in one recordset. I
assume this will be faster than accessing the server for one record many
times.

Thanks for any help you can give me.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure, 'cuz you don't show the schema of your tables, but I believe
what you want is a method usually called the Bill of Materials solution.


There are pure SQL solutions, but they require that the table(s) be set
up w/ 2 extra columns (usually named rgt and lft [right and left,
respectively]). This allows a B-tree like structure to be created that
the SQL can "follow" from the root to a branch or vice-versa. In "human
speak" this would be like following the family tree from grandparents (x
times removed) to children and/or from children to grandparents. This B
tree method is called the Nested-Set model.

Loading and adjusting the table requires some VBA programming, or stored
procedures if you're using an db server.

Google for "nested set." Joe Celko has a book that you may be
interested in that explains nested sets (among other things): _Joe
Celko's SQL For Smarties: Advanced SQL Programming_. He also has some
articles on the Web about nested sets. There are other authors that
discuss this method on the Web.

Good luck.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtAah4echKqOuFEgEQISkACeIFm3sAKtkz8izDAUUN9+X3MOnO8AoNP/
FHGPCx90nEU4oLwPykim/Fzr
=n3N+
-----END PGP SIGNATURE-----
 
Back
Top