Re: Slow update

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" <nodamnspam@xxxxxxxxx> wrote in message
news:432473e0$0$22952$cc9e4d1f@xxxxxxxxxxxxxxxxxxxxxx
> 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" <billvaRemoveThis@xxxxxxxxxx> wrote in message
> news:OdDNxvitFHA.1284@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <nodamnspam@xxxxxxxxx> wrote in message
> news:4321b4b4$0$3623$cc9e4d1f@xxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>>
>


.

Relevant Pages
Loading