speed of my split database is killing me!!! 2

[dotolee]

hi there. I have a split database that's running on a network. it's running soooo slowly. I've tried improving my code wherever I could.. but it's still really slow.
i think the part that's killing me is in my before update method, i'm checking every control and see if it's been assigned a value. if not, depending on the control, i assign a default value of 101 or something else.
There are about 80 different text boxes that i'm checking.
The code looks like:

Private Sub Section1CheckForMissingValues()

If IsNull(Me.ABC.Value) Then
Me.ABC.Value = 101
End If

If IsNull(Me.ZZZ.Value) Then
Me.ZZZ.Value = 101
End If

If IsNull(Me.BBB.Value) Then
Me.BBB.Value = 101
End If

etc...


And the way that I call it is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call Section1CheckForMissingValues
Call Section2CheckForMissingValues
Call Section3CheckForMissingValues
Call Section4CheckForMissingValues
Call basLogTrans(Me, "RecruitmentID", RecruitmentID)
End Sub

I guess I could set the default value at the table level to 101 for most of my controls... instead of looping through each one at the end. But I just want to make sure that its really this piece of code that is slowing everything down. I know that 80 seems like a big number ... but the code is so simple, it really should zip through it, shouldn't it? I'm not an ms access expert so I dunno..
Any suggestions would be appreciated.

 

[ZOR]

Have you tried going into Tools/Options/General and remove ticks in Track Name Autocorrect.

 

[dotolee]

would i do this in the front end or back end database? or both?
thanks.

 

[ZOR]

Sorry for delay, had to go out. I checked my fron and back ends and found both were done. Have you tried it yet? There are other suggestions in the forums search section, but I found that was very good for me. Regards

 

[genomon]

We used to have issues like this; our network guys said it was due to latency on the network. I believe we acquired a newer switch, and somehow got a faster/bigger network connection.

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[dotolee]

that change didn't resolve my speed issues, ZOR. But thx for trying.
RE: the network. Also tried talking to our network guys but they insist it's microsoft access being flakey.
??
thx.

 

[MajP]

Take a look at these articles, but you need to post the version of Access that you are using. Can not help you without that information. Each version has its own unique issues. Most likely it is not the network, but how you designed and coded the database. Here is a simple test. Run the database on your computer as a single database. Then split the database on your computer. If you see a performance drop then it is not the network.

http://www.granite.ab.ca/access/performancefaq.htm

http://www.fmsinc.com/tpapers/faster/index.html

If you are using A2k the article list three issues that will make a serious improvement. The difference between locking up and running smoothly.

 

[JoeAtWork]

It's not likely your code, as it is only checking values of the controls, which is on the client end.

Unless that function "basLogTrans" is doing something wierd.

Simple test: if you run a basic SELECT query against one of the linked table, do you get results quickly?

 

[dotolee]

Hi everyone. Just FYI. I think the code that's taking the "longest" time is the basLogTran (which i got off this site) It loops through all controls on the form and records a before and after update value - as an audit.
I took your suggestion MajP and put the backend locally as well and the performance was very acceptable. Of course, when i remove the logging function, it's really fast but the 10 -15 second wait for the logging locally is nothing compared to 30-40 second wait across the network.
I will try to read those articles you posted and see what i can get out of them.
Joeatwork - I'll also try your suggestion - I think i'll need this as ammo for the network guys. Is there any way to start a timer before the select, run the select, and then stop the timer just so I have concrete numbers to present?
Many thanks to everyone.

 

[MajP]

Still need to know the Acess version. For example, just by splitting a A2k database you can get so performance unless you do some specific modifications. 10-15 seconds seems like a long time unless basLogTrans is doing something more than you say. Would help to post the code. Again test this in three phases, not two.
1)Import all tables, forms, queries, code into a brand new database. Run this locally as a single db.
2)Split the database and run locally
Is there a significant difference? If there is you need to make fixes prior to putting the backend onto the network.
3)If all problems isolated then put on network

 

[dotolee]

Joeatwork - i just created a query and ran in the local version - fast.
ran it in the network version - also fast.
Argh. but again, there is a noticeable difference when running the basLogTrans locally (12 seconds) vs. across the network. (45 seconds).
But I need a way to have the system document the times...
can you suggest anything to do this?

 

[JoeAtWork]

Well, perhaps create a local table that logs times for basLogTrans, where you log the time at the start, and log the time when it ends. You could also store this in a text file.

But at this point I think the problem is not the network but something with the basLogTrans function. Can you post the code?

 

[JoeAtWork]

Well I just looked at the original FAQ for that function, and I think I found one place that is probably causing the main problem.

In the basAddHist function, it is opening the history table with no filters. If your history tables are already huge, this could cause a lot of time being wasted.

Since you only need the recordset for adding, you don't need it to load all existing records. Instead of just opening "tblHist" directly, you should open it with an SQL statement that returns no existing records. You could do this with a WHERE clause that you know will not return any records, example:

SELECT * FROM tblHist WHERE FldName = ""

Better yet would be not to use a recordset at all but just construct an INSERT statement and execute it.

 

[gwinn7]

I think it highly unlikely that your code is causing the huge delay. What happens when you just call it via a public sub/function from the debug window? Does it go slow? If not, then its likely your recordset.

How many records do you have in your recordset/table? Did you setup any indexes?

What kind of server are you using to host the MDBs? Are you using IDE drives, RAID, SCSI, etc?

How many people are accessing the system at the same time?

I would look at upgrading the server disk performance before looking into boosting network speed.

Also, you can download Ethereal packet sniffer and check your network traffic for unnecessary packets.

These are the pieces of the puzzle I would focus in on first.

Gary
gwinn7

 

[gwinn7]

Oh, and I definitely recommend using the DEFAULT value for eliminating NULLs and set the Required to TRUE. That would eliminate the need for your code altogether.

Gary
gwinn7

 

[dotolee]

Thanks guys! Joeatwork - i'll give that a try and post back. I may have to ask some other questions but i'll give it a go on my own first.
gwinn - i can't make the fields required. they are allowed to skip fields - but the users need to know if they skipped because they just didn't want to answer, or if it's because they answered a question somewhere else that asked them to skip the next 5 questions, for example.
I don't have any of the hardware information. that's a big black box to me - i'm not even allowed to have admin access on my own box here. everything is locked down.

 

[dotolee]

changed the basaddHist routine to look like this:
Public Function basAddHist(Hist As String, frm As Form, MyKeyName As String, MyCtrl As Control, uID as string)

DoCmd.RunSQL "INSERT INTO tblHist (MyKey,mykeyname,frmName,FldName,dtchg,UserID,oldval,newval) VALUES (" & _
frm.Controls(MyKeyName) & "," & MyKeyName & "," & frm.Name & "," & MyCtrl.ControlSource & "," & Now() & _
"," & uID & "," & MyCtrl.OldValue & "," & MyCtrl & ")"
End Function

It's failing with an error:
Syntax Error (missing operator in query expression '18orOlder'

I did a debug print of the statment and it looks like this:
INSERT INTO tblHist (MyKey,mykeyname,frmName,FldName,dtchg,UserID,Oldval,newval)
VALUES (1,RecruitmentID,CombinedInclusionExclusionExam,18orOlder,
2/19/2008 2:55:14 PM,dot,,101)

the table definition for the fldName field is a memo field.
Probably overkill but that's what it is.

 

[JoeAtWork]

You need single quotes around any non-numeric field values, and you need to surround the date with #'s

        DoCmd.RunSQL "INSERT INTO tblHist (MyKey,mykeyname,frmName,FldName,dtchg,UserID,oldval,newval) VALUES (" & _
        frm.Controls(MyKeyName) & ",'" & MyKeyName & "','" & frm.Name & "','" & MyCtrl.ControlSource & "',#" & Now() & _
        "#,'" & uID & "'," & Nz(MyCtrl.OldValue,"Null") & "," & MyCtrl & ")"

I just realized there is a problem with the INSERT method, in that for a couple of values:

frm.Controls(MyKeyName)
MyCtrl.OldValue

You won't know if the data is text, a number, or a date, and therefore will not know if it should be surrounded by quotes, pound signs (#), or just left as is. One method might be to put the data type in the Tag property of the control, and then you would know how to construct it in the SQL statement (e.g. If frm.Controls(MyKeyName).Tag = "Date" you surround it with #'s).

Or you could go back to the recordset, just make sure you open the recordset so it has no records like I mentioned before.

 

[dotolee]

JoeAtWork,
I've reverted back to the record set but changed the code to look like:

Set tblHistTable = dbs.OpenRecordset("Select * from tblhist where dtchg=1/15/1975;", dbOpenDynaset)

There will never be any records dated 1975.
everything runs with no errors - problem is that I'm connected today via remote desktop and everything is slow -so i can't tell what kind of a performance gain this has created. Non the less, i'm happy you found this because it really needed to go!
Thanks.