更新数据库表需要很长时间才能执行 [英] Updating database table takes too long to execute

问题描述

我想在将姓名拆分为姓氏，姓名和中间名后更新表。我的代码是：

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

MainCon.execute（sql）行执行时间太长。可以告诉我这有什么问题。

提前谢谢。

The line MainCon.execute(sql) takes too long to execute.Can any1 tell me what is the problem with this.
Thanks in advance.

推荐答案

可能有很多原因，你应该先在你的桌子上应用索引

在数据库表上应用索引 ...



[ ^ ]



快乐编码！

：）

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

不，我们不能。

你需要对事物进行限定：太长多长时间？你有没有测量过它？

使用秒表课程来准确计算正在发生的事情 - 然后至少你可以衡量你在做什么以及需要多长时间，这样你就可以检查一下有什么变化你那个时候有影响力。这里有一个提示主要涉及计时事项：计算字符串中的行 [ ^ ]看看它是如何完成的，然后开始研究为什么你的代码太慢了。 （我怀疑我知道，但这只是一个猜测，所以我不会在这个时候分享它 - 快速测试会告诉你大部分时间你的代码占用了多少。）



BTW：不要这样做 - 不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

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.

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

这篇关于更新数据库表需要很长时间才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！