Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
I want to update table after splitting a fullname into surname,name and middlename.Below is my code:
 

 
sql = "Select fullname from datafile"
        rs.Open(sql, maincon, 1, 3)
        Do While Not rs.EOF
            fullname = rs.Fields(0).Value
            uname = fullname.Split(" ")
            sql = "Update datafile set esurname = '" & uname(0) & "',ename='" & uname(1) & "',emiddle = '" & uname(2) & "' where fullname = '" & fullname & "'"
            MainCon.Execute(sql)
        Loop
The line MainCon.execute(sql) takes too long to execute.Can any1 tell me what is the problem with this.
Thanks in advance.
Posted 13-Dec-12 20:30pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

can be many reason, you should apply indexing first on your table
apply indexing on table of database...
 
Overview of SQL Server 2005/2008 Table Indexing (Part-1)[^]
 
Happy Coding!
Smile | :)
  Permalink  
Comments
Rachna0309 at 14-Dec-12 3:04am
   
Applying indexing also doesnt speed up updating table.
Aarti Meswania at 14-Dec-12 3:09am
   
is it taking 30 sec if you are directly execute query in sql server (not fom .net)?
Rachna0309 at 14-Dec-12 3:10am
   
No its taking 30 sec from .net..
Aarti Meswania at 14-Dec-12 3:13am
   
okay direct execute it from sql, if it's quickly being execute then...
network traffic, memmory can effect speed
Rachna0309 at 14-Dec-12 3:14am
   
It is taking same time to update..
Aarti Meswania at 14-Dec-12 3:24am
   
okay try to execute this by store procedure
first create sp and test it's execution time from sql
because it also matters
Rachna0309 at 14-Dec-12 3:25am
   
Solved it myself...Thanks anyways...
Aarti Meswania at 14-Dec-12 3:27am
   
if you have solved it then post answer.
it will useful to them who are facing same problem
:)
Rachna0309 at 14-Dec-12 3:27am
   
http://www.codeproject.com/Questions/508738/PublishingplusVb-NetplusApplication
Can you please provide solution for this?
Rachna0309 at 14-Dec-12 3:31am
   
I just changed my where clause in query to
"where ID = " &ID& ""
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

No, we can't.
You need to qualify things: how long is "too long"? Have you measured it?
Use the Stopwatch class to accurately time what is going on - and then at least you have a measure of what you are doing and how long it takes so you can check how any changes you make affect that time. There is a tip here that is concerned largely with timing things: Counting lines in a string[^] have a look at how it is done, then start working out why your code is so slow. (I suspect I know, but that is just a guess so I won't share it at this time - a quick test will tell you which bit of your code is taking most of the time.)
 
BTW: Don't do it like that - do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
  Permalink  
Comments
Rachna0309 at 14-Dec-12 3:02am
   
It nearly takes about 30 secs to update one record...and I have nearly 2lakh records.
OriginalGriff at 14-Dec-12 3:24am
   
That explains it.
You are first retrieving 200,000 record in one go (not a fast operation, depending on where your SQL server instance is located.)
Then you issue 200,000 individual SQL commands - each of which needs a round trip to the server, an exceute, and a trip back.
You need to look at either doing this as a bulk update, or doing it locally to the server via a stored procedure.
Add some stopwatches and see what times you get - it will show you where the slow-down is.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

I just changed my where clause in query to
"where ID = " &ID& ""
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 16 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid