How to reduce time while executing stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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.

C#

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.

Posted 26-Dec-13 23:14pm

Arifa S

Updated 26-Dec-13 23:31pm

Tom Marvolo Riddle

v2

Add a Solution

Comments

AlexDpars 27-Dec-13 10:19am

Why do you need this? b.CustNo>a.CustNo... Also why do you need to do a left outer join why not an inner join? Is the field on the on clause indexed? is it the primary key? How long does it takes just to call the function itself? Could we see the function?

Arifa S 28-Dec-13 1:04am

now I have created one new procedure to achieve goal
ALTER procedure [dbo].[SP_Temp](@custNo int,@Name varchar(100))
as
insert into F0001
select @custNo,CustNo,dbo.fnSpSearch(@Name,Longname) as percentage
from F009011 where CustNo>@custNo
and (dbo.fnSpSearch(@Name,Longname)>80)
return
but thing is that i hav to enter customer number i want something that automatically iterate on customer number. like loop can you suggest please for the same.
Thanks

Andrius Leonavicius 27-Dec-13 17:58pm

Hi,

I completely agree with AlexDpars. I just want to add one thing. You should do some query performance analysis. For example, check the execution plan to determine the slowest point...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2014-01-05T11:29:00

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.

SQL

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 ?