如何在执行存储过程时减少时间 [英] How to reduce time while executing stored procedure

问题描述

大家好！



我有8个数据缺少客户表，我需要创建一个调用一个函数的程序（fnSpSearch） ，此函数用于比较两个名称并返回匹配百分比。我的问题是函数由Name1和Name2组成，因此name1将是第一个客户编号，它将与客户编号1加1匹配。比如Name1是客户编号1的名称，因此name2将是客户编号2的名称，然后是3，直到最后一个客户编号（表示8lack）然后再次对客户编号2的Name1进行相同的循环重复此循环将重复直到8lack数据。

所以问题在于花费了太多时间。

Hi to All!!!

I have data of 8 lacks customer in a table for which I need to create one procedure that call one function (fnSpSearch),this function is use to compare two names and return matching percentage. My problem is that function consist of Name1 and Name2 so name1 will be first customer number and it will match with customer number1 plus one. like Name1 is Name of customer number one so name2 will be Name of customer number 2 then 3 so on till last customer number(means 8lack) then again same cycle repeat for Name1 of customer number2 this cycle will repeat till 8lack data.
So the problem is that it is taking too much time.

with tmpCTE as
(
select a.CustNo,
b.CustNo as matchNo ,
a.Longname,b.Longname as matchname,
(dbo.fnSpSearch(a.Longname, b.Longname)) as rs 
from F009011 a
left outer join F009011 b
on b.CustNo!=a.CustNo
--where ((dbo.fnSPFind(a.Longname, b.Longname)))>80
where a.Longname<>'' and  
b.CustNo>a.CustNo --and 
--(dbo.fnSpSearch(a.Longname, b.Longname)>80)
order by a.CustNo,b.CustNo
) select * from tmpCTE where rs>80

先谢谢。

Thanks in Advance.

推荐答案

您发布的代码无法运行。您无法在CTE中指定订单。此外，你的CTE没用，它没有增加任何价值，因为你只需在条件上选择*。



确保你正在进行的值检查，被索引。

The code you posted, cannot run. You cannot specify order by in a CTE. Also, your CTE is useless, it adds no value because you just select * on it with a condition.

Make sure the values you are doing checks on, are indexed.

from F009011 a
left outer join F009011 b
on b.CustNo!=a.CustNo

这是昂贵的，它要求大多数表连接每一行。你需要重新思考你的方法。你有几行？这里有什么业务需求？

THIS is expensive, it asks for a join of most of the table against every row. You need to rethink your approach. How many rows do you have ? What is the business need here ?

这篇关于如何在执行存储过程时减少时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！