By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

Is this Query inefficient?

P: n/a
Suppose I have a customer table ("customer"):-
Primary Key cust_id long integer
cust_name text(40)
cust_type text(1)

containing 80000 rows.
There are about 10000 rows where customer type = "A"
To retrieve these rows I use query

Select * from customer where cust_type = get_type()

But function get_type() gets its data using a lookup in a parameter
file and for this example will evaluate to "A"

function get_type() as string
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Set db = CurrentDb
sqlstr = "select spa_param.pp_data from spa_param where
(spa_param.pp_key = 'CUSTTYPE');"
Set rs = db.OpenRecordset(sqlstr)
If rs.RecordCount = 1 Then
rs.MoveFirst
get_type = (rs!pp_data)
Else
' do the error routine
get_type = ""
End If
rs.Close
Set rs = Nothing
Set db = Nothing
end function

My question is:-
Will get_type() be evaluated once and used in the query on the
customer table or will it be evaluated for each row that is read in
the customer table.

Thanks in advance

Mike
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Mike Ridley wrote:
Suppose I have a customer table ("customer"):-
Primary Key cust_id long integer
cust_name text(40)
cust_type text(1)

containing 80000 rows.
There are about 10000 rows where customer type = "A"
To retrieve these rows I use query

Select * from customer where cust_type = get_type()

But function get_type() gets its data using a lookup in a parameter
file and for this example will evaluate to "A"

function get_type() as string
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Set db = CurrentDb
sqlstr = "select spa_param.pp_data from spa_param where
(spa_param.pp_key = 'CUSTTYPE');"
Set rs = db.OpenRecordset(sqlstr)
If rs.RecordCount = 1 Then
rs.MoveFirst
get_type = (rs!pp_data)
Else
' do the error routine
get_type = ""
End If
rs.Close
Set rs = Nothing
Set db = Nothing
end function

My question is:-
Will get_type() be evaluated once and used in the query on the
customer table or will it be evaluated for each row that is read in
the customer table.

Thanks in advance

Mike


A function without parameters will be executed once for the whole query.
If you were to pass in a column name to the function call then it would
execute for each row. So in your case, there's no problem.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.