我想要一种更快，更有效的方法来实现以下代码行。 [英] I would like a faster and more effective way to implement the following lines of codes.

问题描述

您好，我希望知道一种更快的方式来实现这些代码行，概念是我想从查询中选择某些记录，这些记录选择在Microsoft访问中按照某些条件相互链接的几个表..工作得很好，但是现在访问数据库变得越来越大，花费5分钟甚至更长时间来完成执行变得缓慢且不可靠，如何以更快，更有效的方式优化以下代码？



这是代码：

Hello, I would like the know a faster way to implement these lines of codes, The concept is that I want to select certain records from a query which selects a couple of tables linked with one another within Microsoft access that follow certain criteria.. which worked fine, but now that the access database has become much larger, it's become slow and unreliable taking 5 minutes and more to finish executing, How can I optimize the following code in a faster and more effective manner?

This is the code:

Datatable datatable;
Datatable datatable1;
Datatable datatable2;
Int loopint = 0;
 
OledbDataAdapter dataadapter = new OleDbDataAdapter("Select Distinct EmpNo from Table ",connection);
dataadapter.Fill(datatable);
 
While ( loopint < datatable.rows.count) //for each employee...
{
    OledbDataAdapter dataadapter1 = new OleDbDataAdapter("Select * from whatever1 where col1 = '"+something+"' AND col2 = '"+datatablet.rows[loopint][0]+"' AND etc", connection);
    OledbdataAdapter dataadapter2 = new OleDbDataAdapter ("Select * from whatever2 where col1 = ETC", connection);
    dataadapter1.fill(datatable1);
    dataadapter2.fill(datatable2);
    loopint++;
}

....方法的其余部分



上面显示的while循环有随着数据库的不断增长，变得超级慢，变慢。

....rest of the method

the while loop shown above has become super slow and gets slower as the database continues to grow.

推荐答案

1.我强烈建议您将数据库迁移到MS SQL（可以使用Express免费版）;



2.您应该使用分页和存储过程来优化代码。

您可以在我的下一篇文章中找到有关使用基于存储过程的分页的详细信息：

高级ASPX GridView分页和数据实体 [ ^ ]

1.I am strongly suggesting you to migrate your database to MS SQL (you could use an Express free version);

2.You should optimize your code by using pagination and stored procedures.
You could find details about using pagination based on stored procedures in my next article:
Advanced ASPX GridView Pagination and Data Entities[^]

- 作为大小和SP限制的访问权限不曾用过;因此处理速度很慢

- 为了提高性能，将Access数据库迁移到VS.NET中可用于构建的SQL Server

- 使用存储过程提高查询性能

- As access as limitation in size and SP are not used; as a result the processing is slow
- In order to enhance the performance migrate the Access database to SQL Server which is avialable inbuild in VS.NET
- Use the Stored Procedures to improve the performance of the queries

同意其他答案，你应该做迁移。并且存储过程会更好。

0）永远不要使用 SELECT * （提及所需的列）

1）将该循环逻辑移动到存储过程

2）将数据库迁移到其他数据库时，请使用适当的数据提供程序。 （例如， SqlClient [ ^ ]对于SQL Server， OracleClient [ ^ ] for Oracle）



免费附件

a）使用参数化查询 [ ^ ]

Agree with other answers, you should do migration. And stored procedure'd be better.
0) Never use SELECT * (Mention required columns)
1) Move that loop logic to stored procedure
2) When you migrate your database to some other database, use appropriate data provider. (Ex. SqlClient[^] for SQL Server, OracleClient[^] for Oracle)

Free attachment
a) Use Parameterized Query[^]

这篇关于我想要一种更快，更有效的方法来实现以下代码行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！