Extracting records from DB. Extract from full table or query.

I am looking at and trying to improve an Access 2003 application that was developed a while ago.  Eventually moving it to Access 2013.

There are a lot of subroutine and functions in this application whose sole purpose its to pull a subset of information from one record in the table based on some passed selection criterion, usually a record ID.

In some of the routines the developer is selecting records from a query, in others they are selecting from the actual access table in the backed mdb.

In the example below the user want information from exactly 1 record in a table of about 500,000 records.  There is no updating, just pulling the information on the record.  The fields needed from the record are a subset of all of the fields on the record.  In this case the user is selecting the fields from the entire table 'tblTaxRecs '.  In similar situations I always create a query with the subset of fields needed and select from the query rather than the full table.

My question:  I always thought it was more efficient to use a query on the main table, with only the fields needed, instead of selecting from the entre table.  Am I right?  It's easy enough to create one query and change this code to select from the new query but this is only one of dozens like this in the application.  It would take some time to find them all and change them.  Just wondering if from an efficient processing standpoint it is worth the effort
.  
If anyone has other ideas to make this routine more efficient please let me know.


Here it is:
Public Sub getTaxRecInfoFromIDForOverlay(passedTaxRecID As Long, _
                                         returnTaxHeaderID As Long, _
                                         returnPropertyID As Long, _
                                         returnBRT As Long, _
                                         returnTaxYear As Long, _
                                         returnPrincipalAmt As Double, _
                                         returnInterestAmt As Double, _
                                         returnPenaltyAmt As Double, _
                                         returnLienCost As Double, _
                                         returnAttyFeesAmt As Double, _
                                         returnYearTotal As Double, _
                                         returnPayStatusID As Long)
'

selectString = "Select Top 1  [TaxHeaderID], [PropertyID], [BRT], [TaxYear], [PrincipalAmt], [PenaltyAmt], [InterestAmt], [LienCost], [AttyFeesAmt], [PayStausID] " & _
               " From tblTaxRecs Where [ID] = " & passedTaxRecID
'
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

If rs.EOF Then
Else

        '
        returnTaxHeaderID = Nz(rs!TaxHeaderID, 0)
        returnPropertyID = Nz(rs!PropertyID, 0)
        returnBRT = Nz(rs!BRT, 0)
        returnTaxYear = Nz(rs!TaxYear, 0)
        returnPrincipalAmt = Nz(rs!PrincipalAmt, 0)
        returnInterestAmt = Nz(rs!InterestAmt, 0)
        returnPenaltyAmt = Nz(rs!PenaltyAmt, 0)
        returnLienCost = Nz(rs!LienCost, 0)
        returnAttyFeesAmt = Nz(rs!AttyFeesAmt, 0)
        returnYearTotal = Round(returnPrincipalAmt + returnInterestAmt + returnPenaltyAmt + returnLienCost + returnAttyFeesAmt, 2)
        returnPayStatusID = Nz(rs!PayStausID, 0)
        
End If
'
rs.Close
Set rs = Nothing


End Sub

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
Instead of returning the field values in the parameter, just return the fields collection.  You can change the SQL as necessary and not worry about the parameters.  Below is an example using one of my tables.
Function returnFields(parmID) As Fields
    Dim rs As Recordset
    Set rs = DBEngine(0)(0).OpenRecordset("Select Nz(JobDate, 0) as Ret_jobdate, NZ(JobSite,0) as Ret_Jobsite  From Jobsitetable where ID=" & parmID)
    Set returnFields = rs.Fields
End Function

Open in new window


Invoking the function in the Immediate window
set x = returnFields(1)
?x.count
 2 

for each fld in x : ?fld.name, fld.value : next
Ret_jobdate   6/3/2009
Ret_Jobsite   1

Open in new window


I'm not sure why you'd need a Top 1 clause in your Select statement if your ID matches a single record.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Selecting only the fields you need is the way to go, but including a few extra fields in your recordset generally is not going to cause you much trouble. I'm not sure that refactoring your code to use "SELECT Col1, Col2, Col6 FROM SomeTable" instead of "SELECT * FROM SomeTable" will give you much in the way of efficiency or performance. That would be dictated more by proper indexing and environment.

That's not to say you should leave those alone. From a purists point of view, you SHOULD refactor those to include only the fields needed. It's the right way to do this, and that's sufficient for me.

From a pragmatists point of view, however, your time may be better spent working on other areas of the program.
0
aikimarkCommented:
I agree with Scott that your time on the 2003->2013 conversion should be spent elsewhere.  I found that most of the work involved getting forms to function in 2013 the way the user expected them to behave in 2003.  The most frustrating for me were default values, which I had to code.  The default value in the properties window no longer worked after migration.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mlcktmguyAuthor Commented:
Thanks.
alkimark: May I ask a follow up on a different topic.  You mention
<< The most frustrating for me were default values, which I had to code.  The default value in the properties window no longer worked after migration. >>

I ran into the same thing in another application I am converting.  Did you have any other issues migrating to 2013?  When I began my first conversion I posted a question on experts exchange asking what issues to expect when moving from 2003 to 2013.  Other than the new menu system, no one mentioned the default values.  When I encountered that issue I thought I had done something wrong or had missing references but couldn't find any.  Just curious if you encountered anything else, especially  since you ran into the default value issue.
0
aikimarkCommented:
Your migration problem(s) warrants a new question on just that subject.
0
mlcktmguyAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.