June 17, 2012 at 9:10 am
hi,
my sp takes long time to execute, i need some alternative for the same code.
create Procedure [dbo].[custinfo]
@FromDate datetime,
@ToDate datetime,
@capital bit,
@date datetime
as
begin
Declare @temp1 table
(Code Nvarchar(255),
Name Nvarchar(255),
B1 float,
B2 float
)
Insert @temp1
select
Distinct Code,
Name,
b1 = CASE WHEN @capital = 1 THEN
dbo.fGetcustinfo (dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$1'), ph.FromDate, @ToDate )
ELSE dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$1') END,
b2 = CASE WHEN @capital = 1 THEN
dbo.fGetcustinfo (dbo.fGetcapitalreturn(d.ID , ph.FromDate, @ToDate , '$2'), ph.FromDate, @ToDate )
ELSE dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(d.ID , ph.FromDate, @ToDate , '$2') END,
from
dbo.fgetcustinfo(@date) ph
LEFT Outer Join dbo.fBase(@ID, @Code, @s-2) p on
p.ID = ph.ID
Left Outer join
dbo.Labels C On ph.ID = C.ID
LEFT OUTER JOIN dbo.market m ON C.Value = m.value
LEFT OUTER JOIN dbo.market n ON d.Value = n.value
Where ph.ID = @ID and c.Label = '$1' and
and d.Label = '$2'
union all
select
Distinct Code,
Name,
b1 = CASE WHEN @capital = 1 THEN
dbo.fGetcustinfo (dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$3'), ph.FromDate, @ToDate )
ELSE dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$3') END,
b2 = CASE WHEN @capital = 1 THEN
dbo.fGetcustinfo (dbo.fGetcapitalreturn(d.ID , ph.FromDate, @ToDate , '$4'), ph.FromDate, @ToDate )
ELSE dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(d.ID , ph.FromDate, @ToDate , '$4') END,
from
dbo.fgetcustinfo(@date) ph
LEFT Outer Join dbo.fBase(@ID, @Code, @s-2) p on
p.ID = ph.ID
Left Outer join
dbo.Labels C On ph.ID = C.ID
LEFT OUTER JOIN dbo.market m ON C.Value = m.value
LEFT OUTER JOIN dbo.market n ON d.Value = n.value
Where ph.ID = @ID and c.Label = '$3' and
and d.Label = '$4'
union all
............
Select * from @temp1
--
End
i have to do union all for 6 times ,and i need to use udf.
so its taking long time.
is there any other way i can insert data.
thanks
June 17, 2012 at 9:25 am
Kick out ALL udfs you use and write the proper query to fetch the data you aim for.
Without a sqlplan, it is up to our imagination to figure out what these functions in your select statement do.
if you can, please post the ddl of all objects involved, including indexes and functions.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 17, 2012 at 9:31 am
but i need t use udf ,the data has to come from udf and insert into table
June 17, 2012 at 10:22 am
Scalar udfs are incredibly slow. If you want to optimise the procedure you need to get rid of the UDFs, replace them with inline table-valued UDFs or subqueries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 10:52 am
create Procedure [dbo].[custinfo]
@FromDate datetime,
@ToDate datetime,
@capital bit,
@date datetime
as
begin
Declare @temp1 table
(Code Nvarchar(255),
Name Nvarchar(255),
B1 float,
B2 float,
B3 float,
B4 float
)
IF @capital = 1
BEGIN
Insert @temp1
select Code,
Name,
b1 = dbo.fGetcustinfo (dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$1'), ph.FromDate, @ToDate ),
b2 = dbo.fGetcustinfo (dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$2'), ph.FromDate, @ToDate ),
b3 = dbo.fGetcustinfo (dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$3'), ph.FromDate, @ToDate ),
b4 = dbo.fGetcustinfo (dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$4'), ph.FromDate, @ToDate )
from dbo.fgetcustinfo(@date) ph
LEFT Outer Join dbo.fBase(@ID, @Code, @s-2) p on p.ID = ph.ID
LEFT Outer join dbo.Labels C On ph.ID = C.ID
LEFT OUTER JOIN dbo.market m ON C.Value = m.value
LEFT OUTER JOIN dbo.market n ON C.Value = n.value
where ph.ID = @ID and c.Label in ('$1' ,'$2','$3' ,'$4')
END;
IF @capital = 0
BEGIN
Insert @temp1
select Code,
Name,
b1 = dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$1'),
b2 = dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$2'),
b3 = dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$3'),
b4 = dbo.fGetcapitalReturn(dbo.fGetcapitalreturn(C.ID , ph.FromDate, @ToDate , '$4')
from dbo.fgetcustinfo(@date) ph
LEFT Outer Join dbo.fBase(@ID, @Code, @s-2) p on p.ID = ph.ID
LEFT Outer join dbo.Labels C On ph.ID = C.ID
LEFT OUTER JOIN dbo.market m ON C.Value = m.value
LEFT OUTER JOIN dbo.market n ON C.Value = n.value
where ph.ID = @ID and c.Label in ('$1' ,'$2','$3' ,'$4')
END;
Select * from @temp1
You might get rid of the UNION stuff with a rewrite like code above BUT (I like big BUTs rap song...) As others pointed out, you need to dig into dbo.fgetcustinfo(@date) and dbo.fBase(@ID, @Code, @s-2) to really make a big difference I suspect.
June 18, 2012 at 6:02 pm
harri.reddy (6/17/2012)
but i need t use udf ,the data has to come from udf and insert into table
Why?
If it is non-performant why must you use a scalar udf?
As Gail suggested, an iTVF would be much better.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 19, 2012 at 4:09 am
As others have said, in order for us to help you better, please post the DDL behind your objects. That includes CREATE TABLE statements, INSERT statements for sample data, and the code behind all your UDFs.
Coming from an environment where the schema got thoroughly FUBARed during its evolution, I totally understand where UDFs might be the only course of action you have to consistently come up with specific values. So unlike the others, I won't immediately say "don't use UDFs" because I do get it. However, if you want us to help you with your performance, you need to post everything. Not just the stored procedure causing you problems.
Every table your UDFs touch, we need DDL and sample data for. Every table the Proc touches, we need DDL and sample data for. We'll also need expected results for the UDFs and Proc. Maybe what you need is a UDF redesign or maybe the UDFs can be dumped. We won't know either until you post your code.
June 19, 2012 at 4:12 am
CELKO (6/17/2012)
Floating point math is illegal in many commercial application...
Really? I had no idea there was an actual law against it. Could you tell me which one it is so I can point it out to my local actuarial wannabes? I need to explain this to them so they stop asking me to use it.
CELKO (6/17/2012)
....AND bitch for computation;
You = Preacher. Me = Choir. Sing it again, Sam.
June 19, 2012 at 2:41 pm
CELKO (6/17/2012)
SQL programmers do not use local temp tables. This is how COBOL mimic scratch tapes. We use derived tables AND CTEs so the optimizer can work.Floating point math is illegal in many commercial application AND bitch for computation; we use DECIMAL(s,p). I started as a FORTRAN programer AND I am saying that!
Okay, first, I have used local temporary tables to break down complex processing into logical chunks that has actually improved performance of stored procedure processing over trying to do everything in one query using CTE's and/or derived tables.
Second, please provide your citation regarding the illegality of using floating point math in commercial applications.
I worked for a company whose application software was written in Fortran V and it did in deed use floating point math.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply