Q: Slow update

[Geoff Jones]

Hi All

I hope you'll forgive me for posting this here (I've also posted to ado site
but with no response so far) as I'm urgently after a solution.

Can anybody help me? I'm updating a table on a database i.e. I've modified
the table in a DataSet and I want to update it to the SQL database which the
table originally came from. I'm using a data adaptor and the update command
and it works BUT it is soooooooooo slow!!! Can anybody tell me of any
techniques to speed it up?

Thanks in advance

Geoff

 

[Terry Olsen]

How many records are you updating? How much data per row? Is it updating
the entire table or just the rows you changed? What is the connection speed
between you and the SQL database?

 

[Chris]

Hi All

I hope you'll forgive me for posting this here (I've also posted to ado site
but with no response so far) as I'm urgently after a solution.

Can anybody help me? I'm updating a table on a database i.e. I've modified
the table in a DataSet and I want to update it to the SQL database which the
table originally came from. I'm using a data adaptor and the update command
and it works BUT it is soooooooooo slow!!! Can anybody tell me of any
techniques to speed it up?

Thanks in advance

Geoff

It would help to have an understanding to what you are doing and how you
are doing it....

- Are you working over a lan/dialup/local machine?
- What is the update statement you are using to do the insert.
- How many records are in the table
- What indexes do you have in the table.

Chris

 

[Cor Ligthert [MVP]]

Geoff,

I can imaging that you did not get an answer on this AdoNet question in the
ADO newsgroup.



However try it in the ADONET newsgroup.

microsoft.public.dotnet.framework.adonet

and than crossposted if you want to this one because there is real needed
some code to see what you do to be able to help you.

I hope this helps something

Cor

 

[Geoff Jones]

Hi Cor

Ah yes! It would have helped wouldn't it - lol

Unfortunately, I don't seem to be able to access your suggested newsgroup
i.e. it does not turn up on a search.

Geoff

 

[Geoff Jones]

Hi Terry

I'm a bit of a newbie to this database stuff so I hope you'll forgive me for
not giving all the information.

There is about 3000 records, which have about 10 fields each (of various
types).

Now, as far as the connection speed to the SQL database. Again, forgive my
ignorance, I'm on 1Mb broadband connection. Is that what you mean. If not,
can you tell me how to obtain the speed.

Thanks in advance

Geoff

 

[Geoff Jones]

Hi Chris

Sorry to you as well for the vague information in my earlier post. There are
about 3000 records and I'm using a 1Mb broadband connection. The update
command is via a command builder.

Indexes? Err, sorry, new to all this. Are you refering to the primary key?

Thanks for trying to help.

Geoff

 

[Cor Ligthert [MVP]]

Geoff,

Maybe I have typed something wrong however clicking on this should do the
job


news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet



Cor

 

[Chris Dunaway]

And if that doesn't work, you could try Google Groups.

 

[Geoff Jones]

Thanks Cor, clicking on the link opened up a new newsgroup in my email.

Geoff

 

[William \(Bill\) Vaughn]

Update speed really depends more on what you're asking SQL Server to do--not how fast you ask it. I've performed reasonably fast updates from a 1200 baud modem (Dallas to Redmond) where the speed was 80% of the speed of a LAN. Other factors to consider include:
1) What is the server doing besides SQL? Is it a print server too? Is it running reporting services? Is the server hosted on a workstation running Office applications? Is the server running a screen-saver or a game? (seriously)
2)What other SQL is the server being asked to do? Are there a thousand other clients trying to query or update rows? Are these clients locking/blocking rows that your update needs?
3) What kind of volume are you asking the server to change? If it's 3000 rows, are all of these being changed at once? If it's 3000 rows, where did they come from? Did they come from another data source? If so, why aren't you using a bulk copy utility?
4) What is "slow"? Is it seconds/record? minutes/record or what?
5) How much RAM does the server have? How much is dedicated to SQL Server?
6) Does the target table have an index? How many? Too many indexes can slow down updates while too few can slow down queries.

You say you're a novice at this. What have you read on the subject? There are a lot of ADO.NET and SQL Server books (I know, I wrote several).

Consider that SQL Server can support thousands of users and some of the fastest processing in the industry. Even a lightweight rig can support hundreds of users with very little resources--assuming the application is written correctly and the database is designed properly. Do the Visual Studio/VB wizards generate efficient code? Nope, but it's a good staring point.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Geoff]

Hi William

Many thanks for your comprehensive reply. It is taking about 5 minutes to
upload the 3000 rows. Each row has approximately 20 fields. Would you say
this is typical? I'm afraid I don't know much about the SQL server itself
but I'll try and get the information.

What does puzzle me is that I can read about 3000 records into a dataset in
about 10 seconds. But working the other way i.e. writing, takes the time I
mention above.

I'm using a dataadaptor update command to do this. Is there a shortcut?

As far as books, I'm afraid I don't have any. I'm relying on web pages for
the time.

Geoff


Update speed really depends more on what you're asking SQL Server to do--not
how fast you ask it. I've performed reasonably fast updates from a 1200 baud
modem (Dallas to Redmond) where the speed was 80% of the speed of a LAN.
Other factors to consider include:
1) What is the server doing besides SQL? Is it a print server too? Is it
running reporting services? Is the server hosted on a workstation running
Office applications? Is the server running a screen-saver or a game?
(seriously)
2)What other SQL is the server being asked to do? Are there a thousand
other clients trying to query or update rows? Are these clients
locking/blocking rows that your update needs?
3) What kind of volume are you asking the server to change? If it's 3000
rows, are all of these being changed at once? If it's 3000 rows, where did
they come from? Did they come from another data source? If so, why aren't
you using a bulk copy utility?
4) What is "slow"? Is it seconds/record? minutes/record or what?
5) How much RAM does the server have? How much is dedicated to SQL
Server?
6) Does the target table have an index? How many? Too many indexes can
slow down updates while too few can slow down queries.

You say you're a novice at this. What have you read on the subject? There
are a lot of ADO.NET and SQL Server books (I know, I wrote several).

Consider that SQL Server can support thousands of users and some of the
fastest processing in the industry. Even a lightweight rig can support
hundreds of users with very little resources--assuming the application is
written correctly and the database is designed properly. Do the Visual
Studio/VB wizards generate efficient code? Nope, but it's a good staring
point.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cor Ligthert [MVP]]

Geoff,

Now you have given us some more information. If reading goes quick and
updating goes slow, than we know that it is either your DataBase server or
your program.

What you did not tell (or I did not read it) is if the update is a real
update, a delete or an insert. As well not if all rows from the table should
be update or not. And not at least. How did you create your update strings
(desigener, by hand or by commandbuilder). As last show us the codepart that
is around the dataadapter.update

Maybe we can help you than.

Cor

 

[Chris Dunaway]

What does puzzle me is that I can read about 3000 records into a dataset in
about 10 seconds. But working the other way i.e. writing, takes the time I
mention above.

You said you were on a 1Mb broadband connection. Many broadband
connections have a significantly slower upload speed.

 

[Geoff]

Hi Cor

I'm using a commandbuilder. To simplify things, I've wiped all the rows from
the database and simply adding new ones i.e. there is no appending it is now
just an insert. It is still as slow. It looks as if it is the server itself


This is the code:

dataAdaptor.Update(ds.Table("MyData"))

Geoff

 

[Cor Ligthert [MVP]]

Geoff,

Than why not try it once with an executenonquery does.

Create a new table in your database with an uniqueidentifier and a string
field.

Than

Just create a simple insert and than process that 3000 times while you
update the key.

\\\\
Dim strSQL As String = "INSERT INTO mytable (TheId, TheText) VALUES (@TheId,
@TheText)"
dim conn as new connectionstring(connectionstring)
Dim cmd As New SqlCommand(strSQL, Conn)
cmd.Parameters.Add("@TheId", gettype(system.guid))
cmd.Parameters.Add("@TheText", gettype(system.string))
open conn
For i as integer = 1 to 3000
cmd.parameter(0)= new guid
cmd.parameter(1)= i.tostring
cmd.executenonQuiery
next
close conn
///

This should go quick,

I hope this helps,

Cor

 

[William \(Bill\) Vaughn]

Trying to learn how to use ADO.NET (or anything as complex) from the web is
like learning to perform brain surgery on yourself by reading an article in
Reader's Digest. The books out there (and there a wealth of them) are for
the most part comprehensive treatments of the many inter-related issues
involved in designing, coding, testing and deploying ADO.NET applications.

Perhaps your upload speed is an issue... broadband is very slow on the
reverse channel.What does the DSL Speed test say (these can be found on the
web)? However, consider that the Update method before version 2.0 makes a
single round trip for each row that needs to be changed. If you're using the
CommandBuilder incorrectly it might be two trips/row and one of those trips
is VERY expensive. You can implement batch updates but it means rolling your
own UPDATE statements and other DML commands--or wait for ADO.NET 2.0.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Geoff]

Thanks Cor

 

[Geoff]

Hi William

Thanks for your comments - time to invest in some books me thinks!

When is ADO.NET 2 out?

Geoff