Memory???

[Gush]

hi all,

I have a performance question.

Sometimes I write a function that returns an object, but I don't always call it and use the returned object, what really happens here?

Example:

Public Function ExecuteSQL (ByVal sSQL As String) As Recordset
    Set ExecuteSQL = myConn.Execute (sSQL)
End Sub

Sometime I call it as follows.

Dim rsTemp as Recordset
Set rsTemp = ExecuteSQL ("Select * From Table1")

In the above call, the function ExecuteSQL creates an instance of a Recordset in the calling function scope, then puts a pointer to that object in rsTemp, am I right?

Now, what happens if I just call it like this:

ExecuteSQL("Insert Into Table1 (Field1, Field2, Field3) Values (""TestField1"", ""TestField2"", ""TestField3"")")

will the ExecuteSQL function still create an instance of a Recordset in the calling function scope? If so, is it better to create to versions of that function, one a Function and the other a Sub?

[BodwadUK]

you get no return from the function in the second one but it will still work.

Yes but the object is terminated when it exits that function. If you are worried about memory use then dont be. It should have no leaks

[Wokawidget]

That is correct, however, it seems from your code you have a public connection that is contantly open. This will affect your memory. I personally, and I do, would use:

Public Function ExecuteSQL (ByVal sSQL As String) As Recordset
Dim adoConn   As ADODB.Connection
    Set adoConn = New ADODB.Connection
    adoConn.Open DB_CONN_STRING
    Set ExecuteSQL = myConn.Execute (sSQL)
    adoConn.Close
    Set adoConn = Nothing
End Sub

You will not see any loss of performance here as the connection is pooled.
This is the best way to do it...well apart from adding more functionality to that function anyways.

Woka

[Gush]

Are you saying that it is always better to open / close the connection each time I want to execute a query, regardless of how many requests I have?



and what functionalities you suggest adding to that function?

[Wokawidget]

Yes...there is no need to keep a connection open when it's not being used.

You can add a parameter to that sub to say which DB you want to connect to...my app hits 2 DB's, so I pass in the DB name I want to access.
Also you can have a parameter to say whther the recordset is read-only or not...
You could also add functionality to deal with multiple updates using transactions on the ado connection object.

Woka

[Gush]

Thanks woka, you seem to have quite an experience with DB programing, so it would be great if you help me out with this:

• Is it good to use the "Autonumber" field, or it is better to create my own ID generator
• if yes, how can I get the ID of a record that I've just added using the "Insert Into" SQL statement, is there a direct method, or I have to run another query to get the LAST(ID)?

thanks again.

[Wokawidget]

OK, there are a few ways.
One is to do a SELECT MAX(ID) FROM MyTable query straight after the INSERT...although I don't like this.

Another way would be to sue a stored procedure and pass the ID back as a return varible, again, I don't like this as I have a problem with SP's on small to medium sized application, and in some cases large applications. Just a personal thing.

The other way is not to use the insert SQL statement, but instead do this:

Set adoRec = New Recordset
        With adoRec
            .CursorLocation = adUseClient
            .Open "SELECT * FROM MyTable where ID = 0", DB_CONN_STRING, adOpenForwardOnly, adLockReadOnly
                .AddNew
                .Fields("Name").Value = Trim$(udtProps.Name)
                .Fields("Scrap").Value = udtProps.Scrap
                .Update
                MsgBox .Fields("ID")
                .Close
         End With

This is what I would use...gives you more flexability.

However, in my latest project I have moved away from numeric ID's and have opted for UID's instead, ie {6356EDF-765A45C-7823632...Blah blah blha}
This UID's are created in my VB application and are saved to the DB. I have done this as it makes coding in the user interface easier as I can have many mnay objects in a treeview, and I will NEVER have a duplicate KEY It becomes more obvious of the benefits when you do transactional programming, because when you add an object to a grid the UID is the same B4 AND after you save it to the DB, so no need to redo keys and stuff in the grid/treeview...although this may be a little advanced for some people.

Does that help?

Woka