By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Update Query VERY slow! Please help :(

P: n/a
I have a database that isn't very big. It has about 2400 rows in it.
I try and do an update like this one below (it looks big, but it's
really not if you look at it) :

UPDATE jobs

SET prodduedate = '1/5/2005', clientduedate = '1/5/2005', digitizingcnd
= 'x', digitizingcndnonsub = '', digitizingintl = '', retrieval = '',
wellsitetape = '', boardsubmissions = '', hardcopylogs = '', burningcd
= '', las20 = '', metric = '', tops = '', custsupplogs = '', email =
'', bhcscax = 'x', bhcscay = 'x', bhcsgr = 'x', bhcsdt = 'x',
dipoledtsm = 'x', dipoledtco = 'x', dilsp = 'x', dilim = 'x', dilid =
'x', dilfr = 'x', mlin1 = 'x', mln02 = 'x', mlcal = 'x', arraysp = 'x',
arrayat10 = 'x', arrayat20 = 'x', arrayat30 = 'x', arrayat60 = 'x',
arrayat90 = 'x', arrayat120 = 'x', arrayrxoz = 'x', elsp = 'x', elsn =
'x', elln = 'x', ellt = 'x', ielsp = 'x', ielsn = 'x', ielid = 'x',
ielic = 'x', ielres = 'x', cnfdcax = 'x', cnfdcay = 'x',cnfdgr = 'x',
cnfdden = 'x', cnfdrhob = 'x', cnfdrhoz = 'x', cnfddc = 'x', cnfddrho =
'x', cnfdhdra = 'x', cnfdnphilime = 'x', cnfdnphisand = 'x',
cnfddphilime = 'x', cnfddphisand = 'x', cnfdpef = 'x', cnfdpefz = 'x',
cnfdrop = 'x', cnfdtotalgas = 'x', notes = '', nologs = 0, nocurves =
47, footageout = 33, overdue = 197, totaldays = 244, headerprep = '',
digitizing = '', edit = '', output = '', rejected = '', fixed = '',
rejectreason = '', query = '', answer = '', dccurves = '', dccharges =
'', dcfeet = '', dicurves = '', dicharges = '', difeet = '', dtcurves =
'', dtcharges = '', dtfeet = '', rcurves = '', rcharges = '', rfeet =
'', wcurves = '', wcharges = '', wfeet = '', hclogs = '', hccharges =
'', hcfeet = ''

WHERE jobnumber = 4010


As you can see it looks huge, but really it's just editing one row in
the database and updateing many fields.

I've tested this update and it takes :

1 minute 26 seconds to do the update when there are 2400 rows in the
table.
25 seconds to do the update when there are only 1200 rows in the table
and 7 seconds to do the update when there are 600 rows int he table.

Either way, all of those updates are taking way to long. I'd assume
the update should be well under a second for each.

I know what you're thinking. I didn't use indexes. I did in fact put
an index on "jobnumber" (where there are no duplicates within that
field).

I only have the one index on that field.

I know for a fact that it's not a network issue. The network is fast
and opening the database from anywhere is very fast.

Anyone see anything I could be doing work or just overlooking?

Any help at all is greatly appreciated. I can't seem to find much at
all on this on google :(

TIA!

Ryan

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Br
Sparticus <sp*************@thesparticusarena.com> wrote:
I have a database that isn't very big. It has about 2400 rows in it.
I try and do an update like this one below (it looks big, but it's
really not if you look at it) :

UPDATE jobs

SET prodduedate = '1/5/2005', clientduedate = '1/5/2005',
digitizingcnd = 'x', digitizingcndnonsub = '', digitizingintl = '',
retrieval = '', wellsitetape = '', boardsubmissions = '',
hardcopylogs = '', burningcd = '', las20 = '', metric = '', tops =
'', custsupplogs = '', email = '', bhcscax = 'x', bhcscay = 'x',
bhcsgr = 'x', bhcsdt = 'x', dipoledtsm = 'x', dipoledtco = 'x', dilsp
= 'x', dilim = 'x', dilid = 'x', dilfr = 'x', mlin1 = 'x', mln02 =
'x', mlcal = 'x', arraysp = 'x', arrayat10 = 'x', arrayat20 = 'x',
arrayat30 = 'x', arrayat60 = 'x', arrayat90 = 'x', arrayat120 = 'x',
arrayrxoz = 'x', elsp = 'x', elsn = 'x', elln = 'x', ellt = 'x',
ielsp = 'x', ielsn = 'x', ielid = 'x', ielic = 'x', ielres = 'x',
cnfdcax = 'x', cnfdcay = 'x',cnfdgr = 'x', cnfdden = 'x', cnfdrhob =
'x', cnfdrhoz = 'x', cnfddc = 'x', cnfddrho = 'x', cnfdhdra = 'x',
cnfdnphilime = 'x', cnfdnphisand = 'x', cnfddphilime = 'x',
cnfddphisand = 'x', cnfdpef = 'x', cnfdpefz = 'x', cnfdrop = 'x',
cnfdtotalgas = 'x', notes = '', nologs = 0, nocurves = 47, footageout
= 33, overdue = 197, totaldays = 244, headerprep = '', digitizing =
'', edit = '', output = '', rejected = '', fixed = '', rejectreason =
'', query = '', answer = '', dccurves = '', dccharges = '', dcfeet =
'', dicurves = '', dicharges = '', difeet = '', dtcurves = '',
dtcharges = '', dtfeet = '', rcurves = '', rcharges = '', rfeet = '',
wcurves = '', wcharges = '', wfeet = '', hclogs = '', hccharges = '',
hcfeet = ''

WHERE jobnumber = 4010
Umm, why?
As you can see it looks huge, but really it's just editing one row in
the database and updateing many fields.

I've tested this update and it takes :

1 minute 26 seconds to do the update when there are 2400 rows in the
table.
25 seconds to do the update when there are only 1200 rows in the table
and 7 seconds to do the update when there are 600 rows int he table.

Either way, all of those updates are taking way to long. I'd assume
the update should be well under a second for each.

I know what you're thinking. I didn't use indexes. I did in fact put
an index on "jobnumber" (where there are no duplicates within that
field).

I only have the one index on that field.

I know for a fact that it's not a network issue. The network is fast
and opening the database from anywhere is very fast.
Unfortunately this is not proof that isn't not a network issue....
Anyone see anything I could be doing work or just overlooking?

Any help at all is greatly appreciated. I can't seem to find much at
all on this on google :(

TIA!

Ryan


Is the DB split? What version are you using? Have you made all the
necessary optimisations when using Access on a network? (eg.
AutoNameCorrect off, Subdatasheet set to [None], etc)
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 13 '05 #2

P: n/a
Ryan,

Some ideas:

* To confirm that the network is indeed having no effect, copy all
the files locally and see what happens. (And make sure not to use a
drive mapping to your local drive.)

* Change the UPDATE statement to just update 1 field and see if that
helps with speed. It would certainly give you more information.

* The jobnumber field is a numeric integer type, yes?

* Question: Why empty strings instead of nulls?

-Tom

Nov 13 '05 #3

P: n/a
Ok, I'm only saying it's not an network issues because I have tons of
other programs on the network that run from the same server and they
are all lightning fast.

I changed my query to :

UPDATE jobs SET prodduedate = '1/5/2005' WHERE jobnumber = 4010

and it still takes forever. Obviously there is nothign about that
query that should slow things down. One thing I forgot to point out,
that when you submit the query, the "dllhost.exe" on the server gets
pins at 100% cpu utilization the whole time the query is updating. So
I assume the lag is not network related... but it's the fact that the
computer is computing something crazy when an update is done.

Any other ideas?

TIA

Ryan Ritten

Nov 13 '05 #4

P: n/a
to*******@gmail.com wrote:
* Question: Why empty strings instead of nulls?


Hi Tom,

There was an interesting and lively debate on a number of subjects here
a couple of years ago in August/September as a result of a cross-post to
comp.databases.theory (Similar to how the recent "How do I get this
query to work?" started). For me, one of the benefits of that debate,
which got quite acrimonious at times, was the concept of not using
nulls. If you want to search for empty values, a search for a null
value negates the use of an index while an empty string would.

Since then, in jet and Oracle, I set default values for text and
varChar2 (Oracle) to an empty string rather than not specify it and have
it null. I've no idea if this is what's behind the OP's logic, though. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5

P: n/a
Ryan,

And if you run this simplified query on an entirely local copy of
your database, what happens? (Note: Be sure none of the object
libraries you are referencing are on the network.)

-Tom

Nov 13 '05 #6

P: n/a
Ryan,

And if you run this simplified query on an entirely local copy of
your database, what happens? (Note: Be sure none of the object
libraries you are referencing are on the network.)

-Tom

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.