T-SQL与存储过程:存储过程与T-SQL的速度问题 [英] T-SQL Vs Stored Procedure : Speed issue of Stored procedure Vs T-SQL

问题描述

大家好，

我们有一个存储过程.
现在的问题是，当我们通过传递参数
执行该存储过程时 花费1分8秒

而当我们从该sp中运行SP的所有sql语句时，则需要2秒钟.

我们拥有SP中使用的表上的所有索引.

请建议

Hi All ,

We have one stored procedure .
Now the issue is when we execute that stored procedure by passing parameter
It takes 1 min 8 sec

And when we run all the sql statements of SP out of that sp it takes 2 sec.

We have all the indexes on the tables used in SP.

Please suggest

推荐答案

在存储过程内进行相当复杂的查询之前，我曾遇到过这种情况.它根据几个可选参数(例如，人名，电话号码，邮政编码等)搜索了多个字段. SQL Server提出了该存储过程的执行计划，然后从此以后继续以相同的执行计划运行SP.因此，如果下次您需要使用与执行计划中指定的索引不同的索引，则SQL Server可能会忽略该索引并使用最佳索引.我解决这个问题的方法是将功能拆分为多个存储过程.然后，第一个SP会根据所传递的数据来调用其他SP之一.您还可以添加索引提示，以告诉SQL Server要使用哪些索引.

I''ve run into this before when I made a fairly complex query inside of a stored procedure. It searched multiple fields based on several optional parameters (e.g., person name, phone number, zip code, and so on). SQL Server comes up with an execution plan for that stored procedure, then continues to run the SP with the same execution plan from then on. So if the next time you need to use a different index than the one specified in the execution plan, SQL Server may ignore that and use the less optimal index. The way I got around this was to split the functionality into multiple stored procedures. The first SP then calls one of those other SP''s depending on the data it is passed. You can also add index hints to tell SQL Server which indexes to use.

这篇关于T-SQL与存储过程:存储过程与T-SQL的速度问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！