如何使快速存储过程? [英] How to make fast Stored Procedure ?

问题描述

我在存储过程中使用以下查询，执行后仅得到27条记录.但是该过程将花费5分钟以上的时间来执行，我要如何快速执行该过程?......... Titlefee表具有大约62,000,00记录...

Hi,
I am using the following query in store procedure and after execution I get only 27 records. But the procedure will take 5+ minute for execution, what I do for its fast execution?......... Titlefee tabel having approx 62,000,00 records...

SELECT  u.sname
        , u.idUsr
        , count(distinct o.idord) as Orders
        , premiums = sum(IsNULL(tf.curAmount, 0))

 FROM  ord o (nolock)
       Inner Join usr u (nolock) on o.idUsrTO = u.idusr
       Inner Join titlefee tf (nolock) on  tf.idord = o.idOrd
       Inner Join feetype ft (nolock) on ft.idFeetype = tf.idFeetype
 WHERE
       ft.bTOCommission = 1
       AND tf.dtIssue between @dateFrom and DATEADD(DAY, 1, @dateTo)
       AND o.idBusUnit in (select idBusUnit from @BusUnit)
       AND o.idOrdType = (Case when @idOrdType = 0 or @idOrdType Is Null then o.idOrdType else @idOrdType End)
       AND o.idUsrTo =  (Case when @idTo <=0 or @idTo is null then o.idUsrTo Else @idTo End)
       AND o.idOrdStatusCurrent = 3

 group by
       u.sName
       , u.idUsr
 order by
       u.sname
       , u.idUsr

推荐答案

您需要向表中添加索引.您可以运行数据库引擎优化顾问，该工具位于SQL Management Studio中的工具"菜单下.它可以建议添加索引或统计信息以帮助提高性能.

您还可以运行查询以显示SMS中的实际执行计划"或估计执行计划"(均在查询"下)菜单，并查看花费了这么长时间的特定步骤.

You need to add indexes to your tables. You can run the Database Engine Tuning Advisor, which is under the Tools menu in SQL Management Studio. It can suggest indexes or statistics to add to help improve performance.

You can also run your query showing the Actual Execution Plan or the Estimated Execution Plan (both under the Query) menu in SMS and see what particular step is taking so long.

第一件事应该做的是通过EXPLAIN运行该语句以查看您的查询正在做什么.

在SSMS中，确保已选择数据库并打开一个新的查询窗口.将您的SQL粘贴到窗口中，然后选择Query->显示估计的执行计划(Ctrl + L)

查询计划显示在底部的窗口中.查看计划，它会告诉您时间在哪里(相对于批次的百分比)如果您不熟悉计划，请在此处查看...

http://www.simple-talk.com/sql/performance/execution-plan-basics / [^ ]

您也可以通过优化顾问运行查询，以查看是否建议使用任何索引.但是，如果表titlefee是记录最多的表，则需要考虑对其执行的操作.

1)您要通过字段dtIssue限制此表的结果.此字段是索引的一部分，还是引起表扫描(在执行计划中查看)

2)您正在按字段curAmount进行汇总-可能会对其进行索引.

使用执行计划+查询调整顾问，您应该能够索引表以获得最佳性能.

但是，通过引入索引，您必须权衡表的更新频率\更新数据的过程(例如，用户应用程序?一夜ETL?).要获得READ和WRITE性能之间的正确平衡，可能有点技巧:)

The first thing you should do is run that statement through EXPLAIN to see what your query is doing.

In SSMS, make sure you have the database selected and open a new query window. Paste your SQL into the window, then choose Query -> display estimated execution plan (Ctrl + L)

The query plan is displayed in the window at the bottom. Look at the plan, it will tell you where things are taking time (percentage relative to batch) If you are unfamiliar with plans, have a look here...

http://www.simple-talk.com/sql/performance/execution-plan-basics/[^]

You could also run your query through the tuning advisor to see if any indexes are recommended. However, if table titlefee is the table with the most records, you need to consider the operations you are performing against it.

1) You are limiting the results from this table by field dtIssue. Is this field part of an index, or is it causing a table scan (look in your execution plan)

2) You are summarising by field curAmount - this could possibly be indexed.

Using the execution plan + query tuning advisor, you should be able to index your tables for optimal performance.

However, by introducing indexes, you have to weigh that up against how often the table is updated \ the process that updates the data (e.g user application? overnight ETL?). It can be a bit of a dark art to get the correct balance of READ against WRITE performance :)

检查这一系列

在SQL Server中优化数据访问的十大步骤:第一部分(使用索引编制) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第二部分(重构TSQL并应用最佳实践) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第三部分(应用高级索引和非规范化) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第四部分(诊断数据库性能问题) [ ^ ]
在SQL Server中优化数据访问的十大步骤:第五部分(优化数据库文件并应用分区) [ ^ ]

Check this bunch

Top 10 steps to optimize data access in SQL Server: Part I (use indexing)[^]
Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)[^]
Top 10 steps to optimize data access in SQL Server: Part III (Apply advanced indexing and denormalization)[^]
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)[^]
Top 10 steps to optimize data access in SQL Server: Part V (Optimize database files and apply partitioning)[^]

这篇关于如何使快速存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！