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 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
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
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
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
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
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
And if that doesn't work, you could try Google Groups.
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
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
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
And if that doesn't work, you could try Google Groups.
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
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
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
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
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
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
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
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
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.
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.
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
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
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |