Sub queries: How it work ?

[Mihail]

Hello !
SQ(x) is a query that return a single row (record).
The main query MQ(x,y, Calc:f(x,y)) use the value from "x" field in all rows (records) to compute values for "Calc" field.

Very simple data:

SQ:  x         MQ:   x   y   Calc: [x]*[y]
     3               3   1      3
                     3   2      6
                     3   3      9
                     3   4     12

The question is:
SQ is executed for each row(record) in MQ or is calculated once then the value is used in each MQ's records ?

With other words, if I run SQ once and I store the value for x in a VBA variable (in order to use it in MQ) is a better approach than running nested queries ?
Or this does not matter (for speed) ?

[TheSmileyCoder]

If you use a function call in a query that does not rely on any fields in the query the VBA call will only be called once.
Example:

GetX()

would only be called once, even if you have multiple rows, but

getX([Field1])

would be called once for each row.


In your case, make a function GetX and have that return your x value.

[Mihail]

Thank you for answer, Smiley.

My approach is:

Public const_X As Double, const_Y As Double, ....

Public Sub CalculateConstants()
     Here is code in order to calculate X (by running SQ and extracting X)
     const_X = ..... Assign value to const_X

     Here is code in order to calculate Y
     const_Y = ..... Assign value to const_Y

     .......................................
End Sub

I run once (from anywhere) this sub routine.

Then, from the queries I call functions like this:

Public Function GetX() As Double
     GetX = const_X
End Function

Public Function GetY() As Double
     GetY = const_Y
End Function
......................................

This ensure me that I run only one time the queries from where I extract values for X, Y, ....

I wonder if I gain something using this approach versus using subqueries.

[twinnyfo]

Mihail,

Even though your declaration of your variables will work, you should keep in mind that const_X and const_Y are not constants, but variables, because you want to be able for these values to be changed throughout your code.

For programming consistency and clarity, I would recommend declaring these variables with prefixes to identify their purpose (just in case someone else ever has to look at your DB:

Public gdbl_X As Double, gdbl_Y As Double,...

the "g" deesignates that this variable is global, the "dbl" lets us know that the value in this variable is Double. This way, whenever you refer to this variable elsewhere in your DB, you know what type of value to assign to it.

Just a tip....

[Mihail]

Note please that I am looking for a "didactic" answer, in order to improve my knowledge.
My databases are very small, so I am sure that is no difference by using a solution or the other one :)

Thank you !

[twinnyfo]

Concerning whether there is an advantage over using queries, by decalaring global variables, the big advantage is not having to run a query every time you need a particular value. If that value changes often (like all the time), then there is no real advantage, because you would have to run your code in your CalculateConsta nts sub just as frequently.

You must determine how often you expect these values to change, and then weigh the various advantages.....

[Mihail]

Thank you, twinnyfo.
Of course that in my code I use suggestive names for this variables.

[Rabbit]

It all depends on how it's set up. If the subquery doesn't call the outer query, then it's run once. If it does, then it's run for every row. The same goes for functions.

Run Once

SELECT Rnd(), *
FROM table;

SELECT (select max(c) from table1), *
FROM table2

Run for each row

SELECT Rnd(table.id), *
FROM table;

SELECT (select max(c) from table1 where table1.id = table2.id), *
FROM table2

[Mihail]

Thank you all.

Now is clear for me but anyone is welcome if he/she has something to add.

Thank you again !