存储过程或散装声明 [英] Stored Procedure or Bulk Statements

问题描述

这故事是说我需要优化我的数据库访问。

现在我需要检索来自像6个不同表中的相关数据。我已经summerized人们的提醒在这些点：

1. 使用了相同的连接 不同的命令仍然支付 登陆到数据库与每一个命令 执行

2. 存储过程是一个好办法 有超过select语句 （不使用联接），以从多个表中选择并支付只有一个访问数据库

3. 数据库上的实际负载的源极是连接到它时，数据不是量（数量仍然影响了性能，但N个连接是一个更大的因子）。

现在我想有多个SELECT语句并作为建议及以上多点，我认为我应该与存储过程选项去。但我一想到要使用批处理的SELECT语句来代替

例如： SELECT E从磷;选择X，Y，Z FROM吨;选择 ....; SELECT ...;

将在一个访问数据库的存储过程或不是这个原因？ 您怎么觉得我的选择吗？

..谢谢你的时间提前：）

解决方案

  

将在一个访问数据库的存储过程或不是这个原因？您怎么觉得我的选择吗？

要回答你的问题：唯一的区别是，你只是转移了存储过程的名称的整个查询，而不是数据库服务器。除非你的查询是几MB的长​​或执行每秒数千次，它不应该有什么区别在性能方面。

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:

1. Using the same connection for different commands still pays a visit to the db with every command execution

2. 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

3. 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 :)

解决方案

Will this cause in a single trip to the database as the stored procedure or not ? and what do you think about my options ?

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.

这篇关于存储过程或散装声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！