SQL Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-11 : 14:59:29

Hi guys,

I would like to know how this going to work.
My Store Procedure is very slow and in the store i am using 5 UDF. I am thinking create new table and put all data that i need from UDF. I am sure
its gonna be fast. My question is how this new table gonna update or insert when new DAta inserted or updated.. There is valid linked and thats my
ID.

Thanks.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-11 : 15:03:31

tooba, first try to find out which part of stored procedure is slow, get detail execution plan, go through each and every node, and try to find that if UDF are actual problem. Its still yes, did you applied proper indexes ???

--------------------------
http://connectsql.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-11 : 15:06:19

Thanks for your reply...
I tried everything and finally if i comment out all UDF,S.P runs very fast with no Problem, but if i run my S.P with UDF taking forever.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-11 : 15:09:00

Can you bring UDF code to stored procedure and putting data in temporary tables for later on usage in stored procedure.
Actually one can't say anything correct without going through all the code and data its playing with.

--------------------------
http://connectsql.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-11 : 15:19:04

Actually Original table has Few Millions Records and everytime UDF runs with where clause and they check all Millions records everytime UDF runs,
so what i am thinking grab only records that i needed from original table to new table and then run my store procedure...
What you think its sounds reasonable. I want to know how if someone update or Insert new data in Original one how the new table can upload or insert
by its self....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 15:27:57

whats that UDF doing? is it possible to include logic inline in SP as lionofdezert suggested?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-11 : 19:13:55

The UDFs are collecting a data from table, Below are the my UDF, Here is the part of the UDF that i need

UDF Name = dbo.Udf_E_GetIDType
-- Roomed & Roomed Time
if @ID = 3
Begin
select @Output = convert(varchar,DATEPART(hour, et.CreatedDate)) + ':' +
convert(varchar,DATEPART(minute, et.CreatedDate)) + ':' +
convert(varchar,DATEPART(second, et.CreatedDate))
+ ' - ' + isnull(e.Room,' No Room')
from tCounter ET
inner join tEnc E on ET.EId = E.EId
where TrailId = @MaxId

End

Here i am using in my S.P

select
e.EId,
dbo.Udf_E_GetIDType(e.EId, 3) Room,
e.CreatedDate

into #tempEns
from #tempEOS e

Note:- Like lionofdezert said, I test everything and the bottom line is these UDF giving me Problem mean slow Performance. This is the part of the UDF
AND i AM USING ONLY ONE UDF BUT SAME UDF 5 TIMES I NEED IT.

Any advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 00:04:50

i think the logic can be implemented inside SP itself.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-12 : 11:22:53

Visakh you are right, there is valid link. My question is how i can implement
dbo.Udf_E_GetIDType(e.EId, 3) Room,
in SP?

Because i just want to insert in Room column where Eid = 3.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:41:30

[code]
UPDATE t
SET t.Room= CONVERT(varchar(8),et.CreatedDate,108) + ' - ' + isnull(e.Room,' No Room')
FROM Yourtable T
INNER JOIN tCounter ET
ON ET.TrailId = T.EID
inner join tEnc E on ET.EId = E.EId
[/code]

i hope i have used correct column names so make sure you double check it with your table before running

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-12 : 11:56:22

Vikas thank you for your help, but what i want to replace this store procedure

select
e.EId,
dbo.Udf_E_GetIDType(e.EId, 3) Room,
e.CreatedDate

into #tempEns
from #tempEOS e

How i can replace this function in Select statement so i can insert valid data into #tempEns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 12:03:45

same way!


select 
e1.EId,
CONVERT(varchar(8),et.CreatedDate,108) + ' - ' + isnull(e.Room,' No Room') AS Room,
e1.CreatedDate
into #tempEns
from #tempEOS e1 
INNER JOIN tCounter ET
ON ET.TrailId = e1.EID
inner join tEnc E on ET.EId = E.EId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-12 : 12:08:44

Is this sounds right to you

select
e1.EId,
CONVERT(varchar(8),et.CreatedDate,108) + ' - ' + isnull(e.Room,' No Room') AS Room,
e1.CreatedDate
into #tempEns
from #tempEOS e1
INNER JOIN tCounter ET
ON ET.TrailId = e1.EID
inner join tEnc E on ET.EId = E.EId
Where E.ID = 3

Becuase i want to insert only where E.ID = 3 in Room Field.

What do you tink?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 12:20:47

quote:
Originally posted by tooba

Is this sounds right to you

select
e1.EId,
CONVERT(varchar(8),et.CreatedDate,108) + ' - ' + isnull(e.Room,' No Room') AS Room,
e1.CreatedDate
into #tempEns
from #tempEOS e1
INNER JOIN tCounter ET
ON ET.TrailId = e1.EID
inner join tEnc E on ET.EId = E.EId
Where E.ID = 3

Becuase i want to insert only where E.ID = 3 in Room Field.

What do you tink?

nope this will only populate the Eid=3 records in #tempEns
table.

if you want to mask room values in all other cases then it should be


select 
e1.EId,
CASE WHEN E.EID=3
THEN CONVERT(varchar(8),et.CreatedDate,108) + ' - ' + isnull(e.Room,' No Room') 
ELSE '' 
END AS Room,
e1.CreatedDate
into #tempEns
from #tempEOS e1 
INNER JOIN tCounter ET
ON ET.TrailId = e1.EID
inner join tEnc E on ET.EId = E.EId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-12 : 12:23:06

Awesome, that will work..

Thank You.

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-12 : 12:26:31

I am not very good in TSQL I am more in SSAS/SSRS & SSIS, Could you please tell me
how i can read this UDF, mean how i can understand what this UDF doing, I can check online as well.

dbo.Udf_E_GetIDType(e.EId, 3) Room,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 13:00:07

quote:
Originally posted by tooba

I am not very good in TSQL I am more in SSAS/SSRS & SSIS, Could you please tell me
how i can read this UDF, mean how i can understand what this UDF doing, I can check online as well.

dbo.Udf_E_GetIDType(e.EId, 3) Room,

have a look at its definition

SELECT [definition] FROM sys.sql_modules WHERE OBJECT_NAME(object_id)='your udf name here'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tooba
Posting Yak Master

224 Posts

Posted - 2012-09-12 : 17:18:49

Hi Visak and all quick advise.

I have udf

if @Type = 0
begin
SELECT @Output =
Do some validation
end

else

if @Type = 1
begin

Do some validation

else

if @Type = 2
begin
Do some validation
end

I am using this UDF in Store Procedure. What you guys think its a good idea or it would be fast(Performace) if
i create three different udfs

UDF 1
if @Type = 0
begin
SELECT @Output =
Do some validation
end

UDF 2
if @Type = 1
begin

Do some validation
END

UDF 3
if @Type = 2
begin
Do some validation
end

Please advise...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 17:23:03

whats the validation you're trying to do? no need of separate UDFs for each condition anyways

If possible i'll try to do this validation inline without any UDF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources