performance question

[tomvdduin]

Hi!

I recently put a lot of procesing out of the query for a form into functions in a module. Then I noticed a delay in showing the data onto the form. For example I have a combobox on a bounded form, witch contains names of people. The combobox first had a query on it getting the full names of persons in one string, eg: select firstname & " " & middlename & " " & lastname from tblPersons where ... (simple example! real situation is much more dificult) Now I have a function containing:

Public Function full_name(in_p_id As Integer) As String
On Error Resume Next
Dim cnn As Connection
Dim rst As New ADODB.Recordset

Set cnn = CurrentProject.Connection

cnn.BeginTrans

rst.open "SELECT * FROM tblPerson where p_id = " & in_p_id, cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
full_name = rst!firstname & " " & rst!middlename & " " & rst!lastname

end function
(again a simplified example)

When I go to the next record, the combobox shows it's values couples of 100ths of seconds later when I use the function. I thought ADO was relative fast? How can I speed this code up a litle?

Greets and thanks!

Tom

 

[RoyVidar]

Stored queries will in most cases be faster than using recordsets, so to gain performance, I'd recommend going back to queries. Taking a second glance at the indexes, seldom hurts, either;-)

Roy-Vidar

 

[tomvdduin]

Roy,

Thanks for your answer, but I don't want to create the name of a person in a query. It's kinda unclear inside a query. And then, I'm using the full name of a person in lots of places on various forms, so it's much clearer to put it in a function!

Any other suggestions?

Tom

 

[RoyVidar]

No - not really, stored queries are just a faster approach than recordsets, and (in my opinion) should be chosen over recordsets whenever possible if speed and efficiency is an issue. What is convenient for the developer, might not be the most efficient way the request is handled by Access/Jet.

To do some minor performance tweaks, consider using DAO, which is often faster on native tables (hate to say that, since I'm an ADO fan myself).

Experiment with concatinating in the query, and using the .execute method of the connection:

[tt]strsql="SELECT firstname & ' ' & middlename & ' ' & " & _
"lastname FROM tblPerson where p_id = " & in_p_id"
set rs=currentproject.connection.execute(strsql)[/tt]

Roy-Vidar

 

[tomvdduin]

Roy,

Yeah ok, I was hoping you didn't say that... I also thought a direct query was better. but consider the following code:

'---------------------------------
Public Function volledige_naam(in_p_id As Integer) As String
On Error Resume Next
Dim cnn As Connection
Dim rstPE As New ADODB.Recordset
Dim rstPERSOON As New ADODB.Recordset
Dim rstPERSOONvolw As New ADODB.Recordset
Dim naam As String

Set cnn = CurrentProject.Connection

cnn.BeginTrans

rstPERSOON.open "SELECT * FROM tbl_PERSOON where p_id = " & in_p_id, cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
If rstPERSOON.EOF Then
naam = ""
Exit Function
End If

rstPERSOONvolw.open "SELECT * FROM tbl_PERSOON where p_id = " & in_p_id, cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
rstPE.open "SELECT * FROM tbl_PE where pe_id = " & rstPERSOON!pastorale_eenheid, cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect


naam = rstPERSOON!roepnaam
If IsNull(rstPERSOON!alt_achternm) Then
If IsNull(rstPE!tussenv) Then
naam = naam & " " & rstPE!achternm
Else
naam = naam & " " & rstPE!tussenv & " " & rstPE!achternm
End If
Else
If IsNull(rstPERSOON!alt_tussenv) Then
naam = naam & " " & rstPERSOON!alt_achternm
Else
naam = naam & " " & rstPERSOON!alt_tussenv & " " & rstPERSOON!alt_achternm
End If
End If

If Not IsNull(rstPERSOONvolw) And rstPERSOON!geslacht = "v" Then
If Not IsNull(rstPERSOONvolw!meisjes_achternm) Then
naam = naam & " - "
If IsNull(rstPERSOONvolw!meisjes_tussenv) Then
naam = naam & rstPERSOONvolw!meisjes_achternm
Else
naam = naam & rstPERSOONvolw!meisjes_tussenv & " " & rstPERSOONvolw!meisjes_achternm
End If
End If
End If


volledige_naam = naam
exit_form:
Exit Function

error_trap:
log_error CurrentUser, Err.Number, Err.Description, "module brieven - schrijf_brief"
Resume exit_form
End Function
'---------------------------------
(sorry, i'm Dutch so the variables mostly are dutch names, too)

I know it is possible to use Iif(bool;exec_if_true;exec_if_false), but it surely isn't clear to understand if I have to nest 4 of them.

If there isn't an other option, I will do a direct query, but if there is a good alternative, I surely will go for that!

Thanks for your quick response...

 

[RoyVidar]

Spotted your usage of the adcmdtabledirect option, which I didn't think would work on an sql string, and did some testing. Interesting, and news to me. adcmdtabledirect is designed to work on native Access tables, and SQL server OLE DB providers, and my reference recommends not to use it, to make the code as general a possible in case of switching provider. One would perhaps have to rethink that after seing this.

It seems opening an ADO recordset with the adcmdtabledirect option not only works, but is faster than both the .execute method and opening a dao recordset. My testenvironment is probably not the cleanest, but an average diff of 10 tests (using QueryPerformanceCount - just using difference between the end and start count) on each method of retrieving/opening the recordsets were:
[tt]
DAO: 0.86273
ADO execute: 1.11220 <- which I believed was fastest ADO method
ADO adCmdText: 1.23029
ADO tabledirect: 0.77884[/tt]

So you are probably alredy using the fastest method except stored queries.

Some questions

Do you need the .BeginTrans?

The rstPERSOON and rstPERSOONvolw is based on the same criterion. Wouldn't you be able to perform what you need with only one recordset (it seems to me both recordsets here would be identical - "show" the same record)?

I'm a little uncertain about the test of recordsets a bit down the routine. I'd figure using the isnull function on an object variable (only the recordset, not including a field) would return false under any conditions (not instantiated, instantiated, opened with eof false/true), was it intended to be tests on a field from the recordset?

Else I'd probably build tests something like this too, except I always test if recordsets return records with:

[tt]if (not rs.bof and not rs.eof) then[/tt]

and close and release them when finished.

Haven't tested the performance on using IIF, though.

Roy-Vidar