473,326 Members | 2,095 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Update Query VERY slow! Please help :(

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
6 10692
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: Felix | last post by:
Dear Sql Server experts: First off, I am no sql server expert :) A few months ago I put a database into a production environment. Recently, It was brought to my attention that a particular...
3
by: Dan Berlin | last post by:
I have two tables: T1 : Key as bigint, Data as char(20) - size: 61M records T2 : Key as bigint, Data as char(20) - size: 5M records T2 is the smaller, with 5 million records. They both have...
2
by: Joshua Moore-Oliva | last post by:
I have a query that is asking me to GROUP a column, yet when I GROUP it it causes an error near GROUP. What is very strange about the following query is that the line list_size_active =...
4
by: MaRcElO PeReIrA | last post by:
Hi there, I was in troubles with a UPDATE+IN statement: The following command use to take about 5 minutes to be done: UPDATE requisicao SET conclusao='3' WHERE reg IN (SELECT reg FROM...
6
by: pg | last post by:
Is there any simple way to query the most recent time of "changes" made to a table? I'm accessing my database with ODBC to a remote site thru internet. I want to eliminate some DUPLICATE long...
10
by: Steve Jorgensen | last post by:
Hi all, Over the years, I have had to keep dealing with the same Access restriction - that you can't update a table in a statement that joins it to another non-updateable query or employs a...
8
by: Philipp Buehler | last post by:
Hello, postgresql 7.3.4 on Debian or the redhat packaged 7.3.4-8 on RHEL AS3 - same issue, so I somewhat cut out RH is playing things on me. Tested on two different PCs, too (say, one debian,...
5
by: parwal.sandeep | last post by:
Hello grp! i'm using INNODB tables which are using frequently . if i fire a SELECT query which fetch major part of table it usually take 10-20 seconds to complete. in mean time if any UPDATE...
3
by: Slower Than You | last post by:
I am trying to write an SQL UPDATE statement for an MSAccess table and am having some problems getting my head around it. Can anyone help? TableName: CustTransactions TransactionKey AutoNumber ...
0
by: ceevee | last post by:
Hi Everyone, Was hoping you could help me understand what I may be doing wrong. I am trying to update a column of one table from the data in a column of another table. Here are my create...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.