处理访问数据库时处理速度慢 [英] Slow processing when dealing with an access database
问题描述
-1
When reading and writing from the database, the device slows down exponentially and when the process repeats, it becomes much slower. Is there a way to speed up processing? I am writing a program by C # Windows Applications The code works properly and has no problems but the problem is only in the slow executable. As noted in the following code I take the student number from the first cell and then look in the database for the existence of a record of the student: If we find the student a record, the wizard will be directed to update student data If no student record is found, a new student record will be added to the student table This is a very slow code when it is re-executed:
我尝试过：
What I have tried:
prog1.Value = 0;
prog2.Value = 0;
prog1.Maximum = DGV1.RowCount;
string muoadNotSave = "";
prog2.Maximum = 7;
for (int i = 0; i < DGV1.RowCount; i += 5)
{
FlushMemory();//دالة تفريغ الذاكرة
if (prog1.Value < DGV1.RowCount - 2)
{
prog1.Value += 5;
}
else
{
prog1.Value += 1;
}
try
{
if (DGV1.Rows[i].Cells[16].Value != null)
{ string name = DGV1.Rows[i].Cells[15].Value.ToString();
int cell_no = DGV1.ColumnCount - 4;//عدد خلايا الصف
for (int o = 0; o <= 6; o++)
{
prog2.Value += 1;
int shoabah_No = 0;
int studenID =
int.Parse(DGV1.Rows[i].Cells[16].Value.ToString());
string ShoabahName =
DGV1.Rows[i].Cells[cell_no].Value.ToString();
OleDbConnection con77 = new
OleDbConnection(System.Configuration
.ConfigurationManager.ConnectionStrings["acce"].ToString());
OleDbCommand com77 = new OleDbCommand();
com77.Connection = con77;
com77.CommandText = "SELECT * FROM Table_Shoab
where shoba_name ='" + ShoabahName + "'";
con77.Open();
OleDbDataReader r77 = com77.ExecuteReader();
while (r77.Read())
{
shoabah_No =
int.Parse(r77["shoba_id"].ToString());
}
con77.Close();
if (shoabah_No != 0)
{
OleDbConnection con6 = new
OleDbConnection(System.Configuration
.ConfigurationManager.ConnectionStrings["acce"].ToString());
OleDbCommand com6 = new OleDbCommand();
com6.Connection = con6;
com6.CommandText = "insert into
link_stud_shobah (shoba_id,JLOS_NO) values
('" + shoabah_No + "','" + studenID + "')";
con6.Open();
com6.ExecuteNonQuery();
if (con6.State == ConnectionState.Open)
con6.Close();
}
else
{
muoadNotSave +=
DGV1.Rows[i].Cells[cell_no].Value.ToString()
+ " " + studenID + "\n\r";
}
cell_no += -2;
}
prog2.Value = 0;
}
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
MessageBox.Show("اما ان يكون هناك مشكلة في الاتصال او ان الطالب "
+ " " + DGV1.Rows[i].Cells[23].Value + " " + " سبق تسجيلة ",
"تنبيه");
// MessageBox.Show("" + ex, "تنبيه");
}
finally
{
}
}
if (muoadNotSave != "")
{
MessageBox.Show("الشعب التي لم تحفظ "+muoadNotSave,
"تنبيه");
}
else
{
MessageBox.Show("تم حفظ كافة الشعب بنجاح ولله الحمد " +
muoadNotSave, "تنبيه");
}
推荐答案
设备以指数方式减速，当流程重复时，速度会慢得多。
the device slows down exponentially and when the process repeats, it becomes much slower.
你能定义指数减速和慢得多吗？
Can you define "slows down exponentially" and "much slower" ?
com2.CommandText = "insert into sch_school1 (schoolnumd ,schoolname,Sys ) values ('" + TBSchoolNum.Text + "','" + TBSchoolName.Text + "','" + TBschoolSys.Text + "')";
...
com2.CommandText = "update sch_school1 set schoolname = '" + TBSchoolName.Text + "' where schoolnumd ='" + TBSchoolNum.Text + "' ";
不是您问题的解决方案，但是你有另一个问题。
永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。
名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。
SQL注入 - 维基百科 [ ^ ]
SQL注入 [ ^ ]
按示例进行SQL注入攻击 [ ^ ]
PHP：SQL注入 - 手册 [ ^ ]
SQL注入预防备忘单 - OWASP [ ^ ]
Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
除了ppolymoprphe所说的 - 这非常重要，你需要通过整个应用程序做一些紧急事情 - 你的SQL很差，那个没有帮助表现。
为什么会这样：
In addition to what ppolymoprphe has said - which is really important, and you need to do something about it through your whole app as a matter of urgency - your SQL is poor, and that doesn't help performance.
Why this:
com1.CommandText = "SELECT schoolname FROM sch_school1 where schoolnumd='" + TBSchoolNum.Text + "'";
string schoolname = com1.CommandText;
con1.Open();
OleDbDataReader r1 = com1.ExecuteReader();
int y = 0;
while (r1.Read())
{ y += 1; }
con1.Close();什么时候可以不返回任何行或创建DataReader？
When you can do that without returning any rows, or creating a DataReader?
com1.CommandText = "SELECT COUNT(schoolname) FROM sch_school1 where schoolnumd=@SN";
com1.Parameters.AddWithValue("@SN", TBSchoolNum.Text);
con1.Open();
int y = com1.ExecuteScalar():
con1.Close();其实，你不喜欢甚至需要这样做：你可以发出UPDATE命令，并检查返回值（受影响的行数）为零。如果没有更新行，则需要执行INSERT。
In fact, you don't even need to do that: you can just issue the UPDATE command, and check the return value (which is the number of rows affected) for zero. If no rows were updated, you need to do the INSERT.
这篇关于处理访问数据库时处理速度慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
