Update query problem

[Guest]

I have a Microsoft SQL Server 2005
I have a table named HISTORY_OLD

I linked to HISTORY_OLD in an access 2003 program.

The Problem:
I am trying to update the HISTORY_OLD table with an Access history table.
that is about 9 million records. I am using an UPDATE query.

everything works fine for the first 1/3 of the update. Then I get timeout
errors.

If I try to use the update wizard all I get is a database with one empty
table. That is after 2 hours of waiting.

any one got an ideal?

Scott Burke

 

[Guest]

Consider updating only part of the records at a time by using a Where clause
that will limit the number of records. Something like:

WHERE LastName like "A*" ;
WHERE LastName like "B*" ;
WHERE LastName like "C*" ;
WHERE LastName like "D*" ;

 

[Phil]

Does this table have unique record numbers? If so, you could try to
update records 1 through 1 million in a single query
Then again, 1 million 1 through 2 million
lather rinse repeat...

adjusting the step size based on whatever it will handle without choking.

Sounds like maybe housekeeping, rebuilding the indexes, etc. is taking
too long for your ODBC driver. You might go into data sources for that
connection, hit the advanced tab, and increase anything that says
"timeout" I would up it considerably. For instance, my "page timeout"
defaults to 5. I would up it to say, 50 or even 500 and see if that helps.

 

[Guest]

More Info.

Access is the only thing running on my computer and It is only using 10% of
the network to comunicate with the Sql Server. It only use 42% of CPU
resources too.

Is there a way to bost that to 90%? network?

Scott Burke

 

[Phil]

It uses what it uses. Consider you data to be patients in a doctor's
office. they can go through the door at a rate of 1 every five seconds.
but the simple fact of the matter is, until the nurse comes to the door
and says "Next", no-one is going through the door. You are waiting for
your system to compose the requests, hand it to the server, and then
waiting for the server to process the request, find the corect data,
then send it back down the line. Put bluntly, you are waiting on the
SQL server to do it's thing, and your computer, and it's network
conection are just sitting there thumb twiddling. a RAM CRAM in the
server would undoubtedly help, but your basic problem would appear to be
too much data. 9 million records in a single table is a lot to sift
through.

 

[Guest]

Hi Phil, I realize that 9 million is a lot. However, I am the only one on
the SQL right now. It has four cpu's , four gigs of ram, raid drive. and
there are no indexes. All in all, I am just coping records from one system
to another. I did not expect "Star Trek" responce time. but, ten or
fifteen minates - yes.

The reality of this work, I could easly move 50 thousand records at a time.
Am I going to have to tell CLEARANCE to twittile there thumbs for 30 +
minates?
while the server works on the request.

Scott Burke