473,324 Members | 2,581 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,324 software developers and data experts.

Q: Slow update

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
29 2082
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
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
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
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
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
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
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
And if that doesn't work, you could try Google Groups.

Nov 21 '05 #9
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
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
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
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
And if that doesn't work, you could try Google Groups.

Nov 21 '05 #14
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need...
1
by: Thomas Richards | last post by:
Hi, I have a table with about 70k rows. When I use open table --> return all rows and then edit a field Enterprise Manager then says it has to return the entire result set for the update to take...
12
by: Neil | last post by:
I previously posted re. this, but thought I'd try again with a summary of facts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that is linked to the database via ODBC...
1
by: IkyL234 | last post by:
I'm using Access2000. I have just designed a database which seems to be operating very slow on a network. There are currently only a few records in it. Should I be compacting it now before it gets...
2
by: ThurstonHowl | last post by:
Hello, my task is the following: Input are tables with fields containing strings where the strings are actually delimited lists. For example, one field could contain 'AB|CD|EF|GH' I've...
2
by: Chris | last post by:
I'm using a DataTable in my application. I am able to load rows into the DataTable quickly. What's puzzling me, however, is that when I update a set of cells in the DataTable, the update is really...
3
by: Geoff Jones | last post by:
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...
4
by: jonceramic | last post by:
Hi all, I have a situation I can't find any old messages or web-help on. I have a split database with a bizarre quirk. When I open up the front-end file, the main form opens up fairly quickly...
4
by: Claudio Calboni | last post by:
Hello folks, I'm having some performance issues with the client-side part of my application. Basically, it renders a huge HTML table (about 20'000 cells in my testing scenario), without content....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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...

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.