多个查询与存储过程 [英] Multiple queries VS Stored Procedure

问题描述

我有一个申请 每小时大约进行20000次数据操作 DATA-OPERATION总共有30个参数(用于所有10个查询).有些是文本，有些是数字.某些Text参数最长为10000个字符.

I have an application which does around 20000 DATA-OPERATIONS per/hour DATA-OPERATION has overall 30 parameters(for all 10 queries). Some are text, some are numeric. Some Text params are as long as 10000 chars.

每个数据操作都执行以下操作:

Every DATA-OPERATION does following:

• 单个DATA-OPERATION，可插入/更新数据库中的多个表(约10个).
• 对于每个数据操作，我都会建立一个连接，
• 然后，我对DATA-OPERATION中的每个查询使用新的prepared语句.
• 每次执行查询时都会关闭准备语句.
• 所有10个准备好的语句都重新使用了连接.
• 当DATA-OPERATION完成时，连接将关闭.

现在执行此数据操作

• 10个查询，10个准备好的语句(创建，执行，关闭)，1次n/w调用.
• 1个连接(打开，关闭).

我个人认为，如果我通过以上10个查询创建存储过程，那将是更好的选择.

I personally think that, if I create a Stored Procedure from above 10 queries, it will be better choice.

对于SP，DATA-OPERATION将具有:

In case of SP, DATA-OPERATION will have:

• 1个连接，1个可调用语句，1个n/w命中.

我建议了这个，但被告知

I suggested this, but I am told that

• 这可能比SQL查询更耗时.
• 这将给数据库服务器带来额外的负担.

我仍然认为SP是更好的选择.请让我知道您的输入.

I still think SP is a better choice. Please let me know your inputs.

基准测试是一个选项.将必须搜索任何可以提供帮助的工具. 也没有任何人可以为这种问题建议已经可用的基准.

Benchmarking is an option. Will have to search any tools which can help in this. Also can any one suggest already available benchmarks for this kind of problem.

推荐答案

任何建议都部分取决于执行查询的脚本所在的位置.如果执行查询的脚本与MySQL实例在同一台服务器上，则不会有太大的区别，但是与1个存储过程相比，执行200k查询的开销仍然很小.

Any recommendation depends partially on where the script executing the queries resides. If the script executing the queries is on the same server as the MySQL instance then you won't see that much of a difference, but there will still be a small overhead in executing 200k queries compared to 1 stored procedure.

我的两种建议都是将其作为存储过程.您可能需要几个步骤.

My advice either way would be though to make it as a stored procedure. You would need maybe a couple of procedures.

1. 结合了每次操作执行的10条语句的过程 通话1次
2. 可以使用CURSOR遍历参数表的过程以馈入过程1

1. A procedure that combines the 10 statements you do per-operation into 1 call
2. A procedure that can iterate over a table of arguments using a CURSOR to feed into procedure 1

您的过程将会

1. 使用参数填充表，该参数将被过程2馈入过程1
2. 执行步骤2

这将带来性能优势，因为无需连接到MySQL服务器20000 * 10次.尽管每个请求的开销可能很小，但毫秒数加起来.即使每个请求节省了0.1毫秒，也仍然节省了20秒.

This would yield performance benefits as there is no need to connect to the MySQL server 20000*10 times. While the overhead per-request may be small, milliseconds add up. Even if the saving is 0.1ms per request, that's still 20 seconds saved.

另一种选择是修改您的请求以一次执行所有20k数据操作(如果可行)，方法是调整您的10个查询以从上述数据库表中提取数据.所有这些的关键是将参数加载到单个批处理插入中，然后在过程中使用MySQL服务器上的语句来处理它们，而无需进一步往返.

Another option could be to modify your requests to perform all 20k data operations at once (if viable) by adjusting your 10 queries to pull data from the database table mentioned above. The key to all of this is to get the arguments loaded in a single batch insert, and then using statements on the MySQL server within a procedure to process them without further round trips.

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