db.execute INSERT INTO very slow

  • Thread starter Thread starter TonyT
  • Start date Start date
T

TonyT

Hi,

I have a client who is having a problem on one client pc when trying to
Insert one row into a table with under 5,000 records. The single Insert takes
8-15 seconds to complete. Other pc's on the network take sub 2 seconds. The
code is;

strSQLJDEnter = "INSERT INTO JobDatesTbl (JobNum, ModelID, MachID,
MachSerNum, MachEngNum, InDate, CustID, DelAddRef, JobStat, " _
& "MachTot, SubTot, VATPr, GrTot, MSales) " _
& "VALUES(" & lngNewJN & ", " & Me.txtModID & ", " &
Me.txtMachID & ", '" & Me.txtSerNum & "', '" & Me.txtEngNum & "', " _
& "" & fncSQLDate(Date) & ", " & Me.txtCustID & ", " &
Nz(Me.txtDelAddNum, "Null") & ", '3', " & Me.txtMSellEx & ", " _
& "" & Me.txtMSellEx & ", " & Round(curEx, 2) & ", " & curTot &
", '-1')"
db.Execute strSQLJDEnter, dbFailOnError

front and backends are on Windows XP running Office 2003 Pro. Network is
100MBit.

All references are set correctly.

I have compacted and repaired the back-end, created a new front-end,
decompiled and re-compiled it, disabled firewall on both server and front end
& checked anti-virus is not active.

File copy/paste tests in explorer perform adequately with large jpeg files.

The only issue I've had is a recent problem with the original server
crashing, and as a temporary measure they are using a pc running XP Home,
this means that they have a problem connecting 5 pc's simultaneously, but
even with just the one problem pc connected it's just as slow. Strangely, the
most complex form in the system bound by inconsistant updates to a complex
query based on many other queries opens in under 3 seconds - this is always
the form that shows up network and pc limitations (and also the next form for
a complete re-working).

Where should I be looking next?
 
Try saving this as a querydef and taking a look at jetshowplan to see details
on how the query is executing. I don't know details about this myself, but
it wouldn't hurt to try.

Odd that this only happens on this one query on one computer. Running XP
may be a cuplrit, given the seemingly lack of "structured faults". Have you
tried various configurations with different computers connecting the the temp
server, ruling out possible lockup of bandwith to this pc?

Another thing that comes to mind is keeping an open connection to the
backend... use a form on startup that is bound to a table in the backend
(doesn't matter what table, as long as its there every time). Hide the form
on startup and close it on app shutdown. This forces a persistent connection
to the backend during the cycle of your app... otherwise access reconnects as
required (a very noticable increase in performance when I implemented this
much advised practice).

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
thanks for the response Jack,

I'll look into the jetshowplan, it's new to me, so I'll investigate further.

I have a perstistant back end connection in place already. Tests were the
same with only the back end and troublesome front-end pc running - exactly
the same.

I too feel that XP is causing the problem, especially being the Home
version. Can't help thinking it's something to do with it's network
connection handling, or lack of it and thread limit, but all this is beyond
my realm of experience.

I'll also look into packet handling & Hub connections.

Unfortunately, in the current financial climate, I can't pursuade the client
to upgrade to another server. We did discuss upgrading to XP Pro, but they
wanted to know why that would resolve the issue before going through the
hassle of it.
 
Back
Top