故事是我需要优化我的数据库访问。
现在我需要从 6 个不同的表中检索相关数据。我总结了人们在这些方面的建议：
-
使用相同的连接
不同的命令仍然支付
使用每个命令访问数据库
执行
存储过程是一个好方法
有多于 select 语句
（不使用联接）从多个表中进行选择并且仅对数据库进行一次访问
数据库上的真正负载的来源是与它的连接，而不是数据的数量（数量仍然影响 。
现在我想要多个 SELECT 语句，并且按照建议以及以上几点，我认为我应该使用存储过程选项。但我想到使用批处理 SELECT 语句来代替
示例： SELECT e FROM p;从 t 中选择 x,y,z；选择 ....; SELECT ....;
这会导致在一次数据库中作为存储过程吗？
您对我的选择有何看法？
..提前感谢您的宝贵时间:)
The story is that I need to optimize my database access.
Now I need to retrieve related data from like 6 different tables. and I've summerized the people's advise in those points:
-
Using the same connection for
different commands still pays a
visit to the db with every command
execution
-
Stored procedures are a good way to
have more than select statement
(without using joins) to select from more than one table and paying only a single visit to the database
-
The source of the real load on the database is the connection to it, not the quantity of the data (quantity still affects the performance but N connections is a bigger factor).
Now I want to have multiple SELECT statements and as advised and more the points above I think I should go with the Stored Procedure option. but I got the thought to use batch SELECT statements instead
Example: SELECT e FROM p; SELECT x,y,z FROM t; SELECT ....; SELECT ....;
Will this cause in a single trip to the database as the stored procedure or not ?
and what do you think about my options ?
..Thanks for your time in advance :)
发布评论
评论（3）
回答您的问题：唯一的区别是您将整个查询而不是仅将存储的过程名称传输到数据库服务器。除非您的查询长几 MB 或每秒执行几千次，否则它不会对性能产生任何影响。
To answer your question: the only difference is that you're transferring the whole query instead of just the stored proc name to the database server. Unless your query is a few MBs long or is executed a few thousand times per second, it shouldn't make any difference in terms of performance.
您没有提到任何有关索引的内容。从这里开始，因为 6 个表对于包含在 select 语句中来说并不算多。根据需要排除不必要的列并过滤行。查看执行计划并查看成本最高的内容（还有很多其他内容需要查找。从这里开始）如果您返回的结果不相关，您可以在存储过程中作为单独的查询运行。
通过数据库引擎优化顾问运行它，让 SQL Server 赚取许可费。
让存储过程返回多个查询可能不是理想的解决方案：任何 SQL服务器多记录集存储过程有什么问题吗？
尝试管理用户对要提取的数据量的期望。我的一些用户一直在困扰我，因为他们绝对必须拥有一个包含 200 多个字段的数据集。他们发誓性能永远不会成为问题；时代在变。
You haven't mentioned anything about indexes. Start there because 6 tables is not a lot to include in a select statement. Exclude unnecessary columns and filter rows as needed. View the execution plan and see what is costing the most (there are a lot of other things to look for. Start Here) If the results you are returning are not related, you can run as separate queries in a stored procedure.
Run it through the Database Engine Tuning Advisor and let SQL Server earn the licensing fee.
Having a stored procedure return multiple queries may not be an ideal solution: Any SQL Server multiple-recordset stored procedure gotchas?
Try to manage user expectations on the amount of data to pull. I have users I've been beating over the head because they absolutely must have a single dataset that has over 200 fields. They swore performance would never be an issue; times change.
我看了你的另一个问题。
两种方法同时采用也是合理的。
在某些应用程序中，有多个连接是合理的，每个连接并行提取数据（您可以在 ADO.NET 中使用异步方法执行此操作）以显示在表单或网页上。
然而，就您的情况而言，对于您的模型，人们必须问您正在对数据做什么？模型不错啊编写一个连接所有表的查询也很好。但对于一对多关系，您期望什么？您是否希望填充一个父母，然后填充一组孩子？此时，您已经离开了行和列的世界，并且有了一个对象模型。这就是 Entity Framework 或 NHibernate 或自定义代码等东西发挥作用的地方 - 将良好的数据库设计转换为可以在应用程序中很好使用的对象模型。
I looked at your other question.
It is reasonable to have both approaches.
In some applications, it is reasonable to have multiple connections, each pulling data in parallel (you can do this in ADO.NET using asyncronous methods) for display on a form or web page.
However, in your case, with your model, one has to ask what you are doing with the data? The model is fine. Writing a query joining all the tables is also fine. But on a one-to-many relationship, what are you expecting? Are you expecting to populate one parent and then a collection of children? At this point you've left the world of rows and columns and you have an object model. This is where things like Entity Framework or NHibernate or custom code come into play - translating your good database design into an object model which can be used well in the application.