I am wondering which is a more efficent method to retrieve data from the database.
我想知道从数据库中检索数据哪个是更有效的方法。
ex. One particular part of my application can have well over 100 objects. Right now I have it setup to query the database twice for each object. This part of the application periodically refreshes itself, say every 2 minutes, and this application will probably end of being installed on 25-30 pc's. I am thinking that this is a large number of select statements to make from the database, and I am thinking about trying to optimize the procedure. I have to pull the information out of several tables, and both queries are using join statements.
恩。我的应用程序的一个特定部分可以有超过100个对象。现在我设置为每个对象查询数据库两次。应用程序的这一部分会定期刷新,比如每2分钟一次,这个应用程序可能会在25-30 pc上安装完毕。我认为这是从数据库中生成的大量select语句,我正在考虑尝试优化该过程。我必须从几个表中提取信息,并且两个查询都使用连接语句。
Would it be better to rewrite the queries so that I am only executing the queries twice per update instead of 200 times? For example using a large where statement to include each object, and then do the processing of the data outside of the object, rather than inside each object?
重写查询是否更好,这样我每次更新只执行两次查询而不是200次?例如,使用大的where语句来包含每个对象,然后在对象外部处理数据,而不是在每个对象内部?
Using SQL Server, .net No indexes on the tables, size of database is less than 10-5th
使用SQL Server,.net表上没有索引,数据库大小小于10-5
2 个解决方案
#1
all things being equal, many statements with few rows is usually worse than few statements with many rows.
在所有条件相同的情况下,许多具有少量行的语句通常比具有许多行的少数语句更糟糕。
show the actual code and get better answers.
显示实际代码并获得更好的答案。
#2
The default answer for optimization must always be: don't optimize until you have a demonstrated need for it. The followup is: once you have a demonstrated need for it, and an alternative approach: try both ways to determine which is faster. You can't predict which will be faster, and we certainly can't. KM's answer is good - fewer queries tends to be better - but the way to know is to test.
优化的默认答案必须始终是:在您明确需要之前不要进行优化。后续内容是:一旦您对它有明确的需求,并采用另一种方法:尝试两种方法来确定哪种方法更快。你无法预测哪个会更快,我们当然不能。 KM的答案很好 - 更少的查询往往更好 - 但知道的方法是测试。
#1
all things being equal, many statements with few rows is usually worse than few statements with many rows.
在所有条件相同的情况下,许多具有少量行的语句通常比具有许多行的少数语句更糟糕。
show the actual code and get better answers.
显示实际代码并获得更好的答案。
#2
The default answer for optimization must always be: don't optimize until you have a demonstrated need for it. The followup is: once you have a demonstrated need for it, and an alternative approach: try both ways to determine which is faster. You can't predict which will be faster, and we certainly can't. KM's answer is good - fewer queries tends to be better - but the way to know is to test.
优化的默认答案必须始终是:在您明确需要之前不要进行优化。后续内容是:一旦您对它有明确的需求,并采用另一种方法:尝试两种方法来确定哪种方法更快。你无法预测哪个会更快,我们当然不能。 KM的答案很好 - 更少的查询往往更好 - 但知道的方法是测试。