Solved

VB Functions instead of SQL

Posted on 2009-05-15
34
307 Views
Last Modified: 2013-12-25
Hi Guys

I have a couple of queries that take a while to load up, because of the complexity,  now i design some functions in vb that will get specific values that i need in that query, for example
getAge(ID)
GetAgeAtDiagnosis(ID)
GetIstEval(ID)
GetIstTreat(ID)
etc, etc

So in the query i just open  the table with the IDs and in every column i put 1 function, it seems to be working faster

What are the pro and cons using this functions instead of plain SQL

Please advice
0
Comment
Question by:titorober23
  • 10
  • 6
  • 5
  • +4
34 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 50 total points
ID: 24395282
titorober23 said:
>>What are the pro and cons using this functions instead of plain SQL

Very quickly...

Pros: You can structure UDFs to perform calculations that are either impossible or are extremely complex
using regular SQL

Cons: UDFs will always run much, much more slowly than regular SQL
0
 

Author Comment

by:titorober23
ID: 24395392
for some reason this ones looks to run faster than the complex sql i had
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24395436
titorober23 said:
>>for some reason this ones looks to run faster than the complex sql i had

That may be an indication that the original SQL was not as good as it could have been :)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24395443
when i create a UDF for query is when it takes long time for the regular to execute, because of complexity.

in short i used UDF for queries to get results faster. so, no Cons
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24395611
No cons? :-s
Can you provide an example scenario where the UDF is faster than performing a query for complex requests?
What kind of example queries are we talking about (titorober23 and Cap).
What kind of operation in a UDF?  (Not still running a query - but from code?)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24395826
Obviously I'm assuming we don't mean the small advantage from say a DLookup on a local table compared to a recordset (which would be using a query - even though it's in code!)
0
 

Author Comment

by:titorober23
ID: 24397596
For some reason Dlookup is taking longer than the vb code
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24397691
And so it generally would on a non-local table lookup.
We still can't see what the comparison is between.
What query and what VBA?
This is currently analogous to talking about shooting in the dark. :-)
Happy to look at any examples of comparitive retrieval methods.
(Bear in mind that a recordset also performs a query!  It may be that it's only doing part of the job - open a query in a recordset and then search through it or go to an ordinal position (as opposed to subqueries galore to determine such things.  But the recordset has still performed a query - to get at least part of the way).
So... examples? :-)
0
 

Author Comment

by:titorober23
ID: 24432710
This is the vb code
Function GetRFS(ByVal PID As Long) As Long
    Dim rsTreat As DAO.Recordset
    Set rsTreat = CurrentDb.OpenRecordset("SELECT PatientMaster.*, DiagnosisDetails.MetsAtDiagnosis FROM PatientMaster INNER JOIN DiagnosisDetails ON PatientMaster.PatientID = DiagnosisDetails.PatientID  WHERE (PatientMaster.PatientID = " & PID & ") ;")
    If rsTreat.EOF = False Then
        If HadSurgery(rsTreat!PatientID) Then
            If GetIstSurType(rsTreat!PatientID) = "Primary" Then
                If HadProgressedATD(rsTreat!PatientID, GetIstSurDate(rsTreat!PatientID)) Then
                    GetRFS = DateDiff("m", GetIstSurDate(rsTreat!PatientID), GetIstProgATD(rsTreat!PatientID, Nz(GetIstSurDate(rsTreat!PatientID), #1/1/1900#)))
                Else
                    GetRFS = DateDiff("m", GetIstSurDate(rsTreat!PatientID), GetLastContactDate(rsTreat!PatientID))
                End If
            Else
                    GetRFS = 0
            End If
        Else
            If rsTreat!MetsAtDiagnosis = True Then
                GetRFS = 0
            ElseIf HadProgressed(rsTreat!PatientID) Then
                GetRFS = DateDiff("m", GetDiagDate(rsTreat!PatientID), GetIstProgATD(rsTreat!PatientID, GetDiagDate(rsTreat!PatientID)))
            Else
                GetRFS = DateDiff("m", GetDiagDate(rsTreat!PatientID), GetLastContactDate(rsTreat!PatientID))
            End If
        End If
    End If
     rsTreat.Close
End Function


And this is the SQL of the query, so to get the value i will use Dlookup
SELECT [PatientsPT@DiagWSurgery].PatientID, Min(DateDiff("m",[MinOfSurgeryDate],[Start Date])) AS RFS
FROM [PatientsPT@DiagWSurgery] LEFT JOIN [Progression-History] ON [PatientsPT@DiagWSurgery].PatientID = [Progression-History].Patient
GROUP BY [PatientsPT@DiagWSurgery].PatientID, [PatientsPT@DiagWSurgery].MinOfSurgeryDate
HAVING (((Min([Progression-History].[Start Date]))>[MinOfSurgeryDate]))
ORDER BY [PatientsPT@DiagWSurgery].PatientID;
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24432814
PatientMaster and DiagnosisDetails are tables?

[PatientsPT@DiagWSurgery] and [Progression-History] are queries?
And aggregate queries at that.
Or are they tables which are the result of queries?
0
 

Author Comment

by:titorober23
ID: 24436884
PatientMaster and DiagnosisDetails are tables? YES
[PatientsPT@DiagWSurgery] and [Progression-History] are queries? YES
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24457640
Long holiday weekend here too.  And (speaking for myself at least) a lot of work lately has taken priority and time and this break is a welcome one.  No doubt we'll get back to this - but as much information and detail about the objects involved seems reaonsable to offer in the mean time - no?
0
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 100 total points
ID: 24458127
MS Access 95 added optimization code in VBA for lookup functions.  Some time ago I compared the speed of a query with a where clause with a DLookup function in a do loop on one million records. The two came within micro seconds of each other.  So post A95, there is next to no speed difference between a query and a lookup function run in VBA.  

A lookup function outside of VBA (inside a query or source property for example) will often be slower than a query but not always.

Another important consideration is development time. If a lookup function is the first thing to pop up in my mind, I use it. If a query pops into my mind first I use it. If either creates an unacceptable processing delay then I consider options. Why spend an hour optimizing a query when spending 2 minutes writing a lookup function that processes seeming instantaneously (or vice versa).

titorober23,

You were right to consider other options since your queries "a while to load up".  If the functions you substituted have better processing times, then use them.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24458165
I am not sure if this has been mentioned, but one big drawback to using an MS Access function is that it is MS Access specific.  So you say: "What is the big deal? I will be using MS Access databases until the day I die."  While that may be true (not that you are going to die soon, of course), you have now invalidated any chance that function can be used from some app using your MS Access database written in VB6, ASP, .NET, etc.  You have basically locked it down to only be used within the MS Access application.  And now you have a licensing problem: Every workstation using your MS Access database must have MS Access installed.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24459352
>you have now invalidated any chance that function can be used from some app using your MS Access database written in VB6, ASP, .NET, etc.<

Not true. If the function is made public in a global module, it can be accessed by any modern object oriented language.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24460132
I wonder if you would be so kind as to give me a single example of how you would do that from say an ASP.NET app using VB.NET or C# for that matter?
Thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24460153
P.S. I suggested VB.NET because VB6 is neither modern nor object oriented and those were your requirements.
0
 

Author Comment

by:titorober23
ID: 24460275
bottom line there is no major difference between both ways?
0
 
LVL 39

Assisted Solution

by:thenelson
thenelson earned 100 total points
ID: 24461868
>VB6 is neither modern nor object oriented<
Modern: Ok, since I have been programming since '68, I may have a different take on the term. VB is a third generation language so to my thinking "Modern".
From my viewpoint:
Not Modern:
Cobol, Fortran , Basic, MAD (Michigan Advance Data), APT, Machine language, Machine dependant assembler, pretty much stuff not used anymore (except Cobol because of all the old almost working stuff in the Federal Government).
Modern:
Any OOP after C and that supports GUI (this would include VB).

Object Oriented: VB is generally accepted as an OO Language - see:
http://en.wikipedia.org/wiki/Object_oriented_programming_language - the list of "Languages with object-oriented features"
Characteristics of an OOP that are in VB:
Modules, Functions, Polymorphic Variables, functions & Modules, multiple encapsulation capabilities such as class modules, type defs, function declarations, properties, ActiveX objects, Automation, etc.

>I wonder if you would be so kind as to give me a single example of how you would do that from say an ASP.NET app using VB.NET or C# for that matter?<

You can set a reference to VBA/MS Access in your Project. Then you are able to do call any public function in any standard module in the MS Access database.

I believe you could also explicitly call Run to call the function.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24461946
I used VB since before version 1 and it has always been considered "Object based" and not a true OOP language.  The standard measure of OOP is: Does it contain all three attributes: Encapsulation, Polymorphism, Inheritance?  Since VB does not have Inheritance and never has and the other two implementations are crippled at best, that in my view disqualifies it.

>>You can set a reference to VBA/MS Access in your Project. <<
But that is my point.  That requires licensing of MS Access components.  As I mentioned in my example, supposing that this is a website (if we can put on one side the fact that many shops will not allow any MS Office components on a server) that makes the licensing factor a non-starter.

Let's assume that all those factors are surmounted, are you telling me that you can execute a query usuing ADO or ADO.NET from VB or VB.NET):

Select Item, Quantity, Price, GetExtendedPrice(Quantity, Price) From OderDetails Where ...

Where GetExtendedPrice() is a function contained in a module in an MS Access database?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24461973
By the way, my definition of a "modern language" has nothing to do with chronology and length of existence and everything to do with is it currently supported?  By that measure, much as I hate to admit it, the language is as dead as Latin or Nahuatl.  Yes, there are people still using it, but it is not growing and will eventually die.  Think back to when we used Fortran and you will know what I mean.  Sure I can still create a program using Fortran, is it relevant, no.
0
 
LVL 75
ID: 24462031
">VB6 is neither modern nor object oriented<"
That's just wrong.

mx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24462086
Fair enough.  I thought I stated my definitions for both terms quite clearly.  We will have to agree to disagree.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24462122
But I beleive we are getting off-track from the original question.  I did not mean to get it side-tracked on what has turned out to be such an emotional tangent.  The question had to do with the pros and cons of using custom MS Access functions in queries.  Do you have any input here?
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24462152
I Surrender - we are way OT here.

titorober23,
>bottom line there is no major difference between both ways?<
MY answer: correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24462178
thenelson,

But I am interested.  Are you saying that I can build an app using VB that contains a query as I mentioned previously using an MS Access function and ADO will not barf?  In all my years developing in VB6, I have never seen that.  Which is not to say it cannot be done.

This IMHO is on topic, as it is central to the question of pros and cons of using MS Access functions in queries or at least that is my understanding.

Anthony
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24462331
Anthony,
That would be an interesting discussion. May I suggest a new question for it? I assume you have unlimited points- if not, I can post it
Nelson
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24462359
Actually I was hoping someone in this thread (Leigh ?) could say if they had ever done that with or without using the MS Access automation objects.  For me it is purely academic, but it is significant in this thread, far more in my opinion than any performance criteria/
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24462581
Fair enough.  So let me answer the author's question "bottom line there is no major difference between both ways?" this way:

You are correct:
1. If you plan never to go beyond MS Access as a development platform.
2. If you find a way to address using MS Access functions in queries from other platforms.

Otherwise I would think twice about it, as I mentioned in my first comment you have basically locked out that functionality from any other platform (other than MS Access).
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 50 total points
ID: 24471777
Well - at the risk of being partially off-topic here, I'll mention a few things.
Firstly to come back to the questions asked.
The objects you refer to in your "query" method are themselves queries.  How convoluted they are we can't tell without knowing their definition.  But this will add to the overhead of your "query" method.
A say "query" as I still contend that your code method uses queries too.
For example your main recordset
"SELECT PatientMaster.*, DiagnosisDetails.MetsAtDiagnosis FROM PatientMaster INNER JOIN DiagnosisDetails ON PatientMaster.PatientID = DiagnosisDetails.PatientID  WHERE (PatientMaster.PatientID = " & PID
and we can't know what act your individual functions are performing
HadSurgery
GetIstSurDate
HadProgressedATD
GetLastContactDate
HadProgressed
GetIstProgATD
etc
And even your "query" method - presumably you use code to retrieve a value from that final single query?
So it too is a hybrid method.  Indeed, almost any solution is.
I know this may seem like I'm splitting hairs, but it's more a question of where lines lie.
It's entirely possible that the most optimum solution for you lies somewhere inbetween - querying a little more before handing over to VBA for some comparative decision making.
I think one thing to bear in mind here is that we are (presumably) talking about ACE being the database in question here. (You list 2007 but don't mention an external datasource).
So let me push the boundries of remaining on-topic and comment on that.
It's a fundamental limitation that Jet and ACE support only single statement procedures (query defintions).
The decision making is limited (involving convoluted use of boolean expressions, immediate if and custom VBA functions with maybe a smattering of EXISTS operator assistance).
Consequently it's not always possible to make operations with such a backend platform inherent to the database, VBA does a great job of supplementing what Jet and ACE lack.
I agree, where you can (and when not detrimental to performance) it's often worth maintaining operations at the database level.
And with normalised data, it's usually faster to get required results by querying.
As for access to VBA functions externally to Access...
That depends upon what the comparison is.  The example here is either a single query (performed upon subqueries of unknown design) or a set of VBA functions, each possibly performing queries.
I don't see how the latter would translate to an external source (other than by recoding to match the syntax of the host environment).  Equally - a query which is making use of custom VBA procedures inside the SQL definition
e.g. WHERE fFunction(FieldName) = 123
(not a good example I know - you don't want to be performing function calls on fields against criteria if you can possibly avoid it)
That function call there requires a host Access environment.
AFAIK you can only use MDB's as AddIns/References within MDBs. Is there some other way Nelson?
Even other VBA (e.g. Office) environments don't suport MDB references.
Called from within Access, these VBA functions contain a great deal of extending functionality (not even necessarily the same original host MDB running them).
Otherwise AFAIK you need to instantiate an instance of Access to execute them. (It's Access which bonds VBA with Jet - and hence Access which is required).
For example - a query which refer to a Form control.  Bog standard - used millions of times.
WHERE FieldName = Forms!FormName!ControlName
Now for this to be executed in an external code library (and even DAO and ADO count as external libraries here) we have to explicitly evaluate this for them.
qdf.Parameters(0) = Eval(qdf.Parameters(0).Name)
This can equally be achieved by causing the function evaluation in the query
WHERE FieldName = Eval("Forms!FormName!ControlName")
Hence no explicit evaluation is required as Jet's expression service is served by VBA, even when Access UI expressions fail.
Access provides this function expression resolving.
However I, personally, shy away from this.  Because it assumes this resolvement by Access.
In this case Eval is a built-in Access function, but the same is true for VBA custom functions too.
Called externally (say even from Word) then you again have to explicitly evaluate that value which is, again, interpreted as a implicit Parameter by the Jet/ACE - unless you instantiate an instance of Access and execute it therein (using a built in Access database object - I'm thinking CurrentDb here even more so than CurrentProject.Connection which, IIRC, doesn't even offer the same level of externall instantiated expression support).
Anyway... to offer my take on it
>> bottom line there is no major difference between both ways?
That depends a lot.
To begin with you have to be comparing like for like.
Subquerying a parent query to death isn't necessarily a fair match if there was an alternative.
But in a Jet/ACE backend with decision making to perform then you will likely have to involve VBA somewhere and hence, to some extent, "Access-ize" your solution.
I don't think that's a bad thing - truly platform independent software is likely pretty rare.  But we can take steps to help transitions easier if and when the time comes.
(First step is probably just a well designed, normalised database).
If either BE or FE redevelopment is required then I think it's not unreasonably to expect some effort in the transitions in a vast majority of real world implemented applications.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now