Click here to Skip to main content
13,096,858 members (81,406 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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 & "'"

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
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!
Rachna0309 14-Dec-12 3:04am
Applying indexing also doesnt speed up updating table.
Aarti Meswania 14-Dec-12 3:09am
is it taking 30 sec if you are directly execute query in sql server (not fom .net)?
Rachna0309 14-Dec-12 3:10am
No its taking 30 sec from .net..
Aarti Meswania 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 14-Dec-12 3:14am
It is taking same time to update..
Aarti Meswania 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 14-Dec-12 3:25am
Solved it myself...Thanks anyways...
Aarti Meswania 14-Dec-12 3:27am
if you have solved it then post answer.
it will useful to them who are facing same problem
Rachna0309 14-Dec-12 3:27am
Can you please provide solution for this?
Rachna0309 14-Dec-12 3:31am
I just changed my where clause in query to
"where ID = " &ID& ""
Rate this: bad
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.
Rachna0309 14-Dec-12 3:02am
It nearly takes about 30 secs to update one record...and I have nearly 2lakh records.
OriginalGriff 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
Please Sign up or sign in to vote.

Solution 3

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web04 | 2.8.170813.1 | Last Updated 16 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100