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

Q: Slow update

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
29 Replies


P: n/a
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

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:ue**************@TK2MSFTNGP14.phx.gbl...
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

Nov 21 '05 #2

P: n/a
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?

"Geoff Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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

Nov 21 '05 #3

P: n/a
Geoff Jones wrote:
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
Nov 21 '05 #4

P: n/a
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

"Terry Olsen" <to******@hotmail.com> wrote in message
news:OK****************@TK2MSFTNGP10.phx.gbl...
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?

"Geoff Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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


Nov 21 '05 #5

P: n/a
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
Nov 21 '05 #6

P: n/a
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

"Chris" <no@spam.com> wrote in message
news:OZ*************@TK2MSFTNGP15.phx.gbl...
Geoff Jones wrote:
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

Nov 21 '05 #7

P: n/a
Geoff,

Maybe I have typed something wrong however clicking on this should do the
job
news://msnews.microsoft.com/microsof...amework.adonet

:-)

Cor
Nov 21 '05 #8

P: n/a
And if that doesn't work, you could try Google Groups.

Nov 21 '05 #9

P: n/a
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

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:ue**************@TK2MSFTNGP14.phx.gbl...
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

Nov 21 '05 #10

P: n/a
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

"Terry Olsen" <to******@hotmail.com> wrote in message
news:OK****************@TK2MSFTNGP10.phx.gbl...
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?

"Geoff Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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


Nov 21 '05 #11

P: n/a
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

"Chris" <no@spam.com> wrote in message
news:OZ*************@TK2MSFTNGP15.phx.gbl...
Geoff Jones wrote:
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

Nov 21 '05 #12

P: n/a
Geoff,

Maybe I have typed something wrong however clicking on this should do the
job
news://msnews.microsoft.com/microsof...amework.adonet

:-)

Cor
Nov 21 '05 #13

P: n/a
And if that doesn't work, you could try Google Groups.

Nov 21 '05 #14

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

Geoff

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Geoff,

Maybe I have typed something wrong however clicking on this should do the
job
news://msnews.microsoft.com/microsof...amework.adonet

:-)

Cor

Nov 21 '05 #15

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

Geoff

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
Geoff,

Maybe I have typed something wrong however clicking on this should do the
job
news://msnews.microsoft.com/microsof...amework.adonet

:-)

Cor

Nov 21 '05 #16

P: n/a
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 Jones" <no********@email.com> wrote in message news:43**********************@news.dial.pipex.com. ..
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

Nov 21 '05 #17

P: n/a
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 Jones" <no********@email.com> wrote in message news:43**********************@news.dial.pipex.com. ..
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

Nov 21 '05 #18

P: n/a
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
"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:Od**************@tk2msftngp13.phx.gbl...
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 Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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


Nov 21 '05 #19

P: n/a
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
"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:Od**************@tk2msftngp13.phx.gbl...
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 Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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


Nov 21 '05 #20

P: n/a
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
"Geoff" <no********@email.com> schreef in bericht
news:43***********************@news.dial.pipex.com ...
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

Nov 21 '05 #21

P: n/a
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
"Geoff" <no********@email.com> schreef in bericht
news:43***********************@news.dial.pipex.com ...
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

Nov 21 '05 #22

P: n/a
Geoff wrote:

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.

Nov 21 '05 #23

P: n/a
Geoff wrote:

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.

Nov 21 '05 #24

P: n/a
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]" <no************@planet.nl> wrote in message
news:O0*************@TK2MSFTNGP10.phx.gbl...
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
"Geoff" <no********@email.com> schreef in bericht
news:43***********************@news.dial.pipex.com ...
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


Nov 21 '05 #25

P: n/a
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]" <no************@planet.nl> wrote in message
news:O0*************@TK2MSFTNGP10.phx.gbl...
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
"Geoff" <no********@email.com> schreef in bericht
news:43***********************@news.dial.pipex.com ...
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


Nov 21 '05 #26

P: n/a
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


Nov 21 '05 #27

P: n/a
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" <no********@email.com> wrote in message
news:43***********************@news.dial.pipex.com ...
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
"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:Od**************@tk2msftngp13.phx.gbl...
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 Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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

Nov 21 '05 #28

P: n/a
Thanks Cor

"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:eB**************@TK2MSFTNGP10.phx.gbl...
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

Nov 21 '05 #29

P: n/a
Hi William

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

When is ADO.NET 2 out?

Geoff

"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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" <no********@email.com> wrote in message
news:43***********************@news.dial.pipex.com ...
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
"William (Bill) Vaughn" <bi**************@nwlink.com> wrote in message
news:Od**************@tk2msftngp13.phx.gbl...
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 Jones" <no********@email.com> wrote in message
news:43**********************@news.dial.pipex.com. ..
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


Nov 21 '05 #30

This discussion thread is closed

Replies have been disabled for this discussion.