473,842 Members | 1,672 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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', digitizingcndno nsub = '', digitizingintl = '', retrieval = '',
wellsitetape = '', boardsubmission s = '', 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 10776
Br
Sparticus <sp************ *@thesparticusa rena.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', digitizingcndno nsub = '', digitizingintl = '',
retrieval = '', wellsitetape = '', boardsubmission s = '',
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.ex e" 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
14083
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 query that executed quite quickly in our dev environment was painfully slow in production. I analyzed the the plan on the production server (it looked good), and then tried quite a few tips that I'd gleaned from reading newsgroups. Nothing worked....
3
10085
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 clustered indexes on Key. I want to do:
2
5845
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 = COUNT(NEW.active) PROPERLY sets the value to the number of new items.
4
2365
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 requisicao WHERE now()-data>'15 days');
6
8658
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 queries by evaluating whether the data has been changed since last query. What should I do? -Jason
10
9828
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 non-updateable subquery. I run across this problem again and again, and yet I've never come up with a single, universal work-around. I'm wondering what other people here are doing.
8
1837
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, one RHEL). While running UPDATE banner SET counterhalf=counterhalf+1 WHERE BannerID=50 several thousand times, the return times degrade (somewhat linear). The relation banner has currently *seven* rows and thus it doesnt matter
5
8348
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 qry comes for a perticular row which is part of SELECT qry i want to know that whether UPDATE will wait for completing SELECT qry or not, or it simply executing without bothering SELECT qry .
3
2454
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 (Primary Key) CustomerID Long Integer (Non-unique index) AmountSpent Double CustSelected Boolean What I would like to do is, for all of the records in descending order
0
3549
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 statements: CREATE TABLE dictethnicity(sbirthplace char(15) not null, bplacedesc varchar(100) not null, usstateflag int, ethgrpcode char(5)); CREATE TABLE voterethnicity(id bigint, sbirthplace char(15), bplacedesc varchar(100), usstateflag int,...
0
9871
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10610
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10672
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7035
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5884
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4499
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4089
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3144
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.