Updating database table takes too long to execute

Question

1.00/5 (1 vote)

See more:

I want to update table after splitting a fullname into surname,name and middlename.Below is my code:

VB

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

Rachna0309

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2012-12-13T20:39:00

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!
:)

Posted 13-Dec-12 20:39pm

Aarti Meswania

Comments

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

http://www.codeproject.com/Questions/508738/PublishingplusVb-NetplusApplication
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& ""

OriginalGriff · Answer 2 · 2012-12-13T20:45:00

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 · Answer 3 · 2012-12-16T17:43:00

Solution 3

SQL

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

Posted 16-Dec-12 17:43pm