Optimize the Sql query

Posted by Dnandha87 under Sql Server on 5/16/2013 | Points: 10 | Views : 1060 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

The below query taking 3 mints to update...

update #TaskCards set PT_FileName = ISNULL(dbo.GetFileName_fn(@PPM_OU_Id, PT_GUID, @pkg_package_name, @pkg_start_date ), PT_FileName)

Where, #TaskCards is the Temp table, it may contains minimu 500 records
GetFileName_fn is the function accessed from the different server by using synonym.

Is there any possible way to minimise the execution timing?

Regards
Nandha Kumar,

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Niladri.Biswas on: 5/17/2013 [Member] Platinum | Points: 25

0	It is a little difficult to say without looking into the function dbo.GetFileName_fn as what it is doing.However try this, create table #temp(value varchar(250)) Insert into #temp Select dbo.GetFileName_fn(@PPM_OU_Id, PT_GUID, @pkg_package_name, @pkg_start_date ) update #TaskCards set PT_FileName = ISNULL(value,PT_FileName) From #temp Drop table #temp OR try to use Cross Apply inorder to avoid the function all together. Best Regards, Niladri Biswas Dnandha87, if this helps please login to Mark As Answer. \| Alert Moderator

It is a little difficult to say without looking into the function dbo.GetFileName_fn as what it is doing.However try this,

create table #temp(value varchar(250))

Insert into #temp

Select dbo.GetFileName_fn(@PPM_OU_Id, PT_GUID, @pkg_package_name, @pkg_start_date )



update #TaskCards

set PT_FileName = ISNULL(value,PT_FileName)

From #temp



Drop table #temp

OR

try to use Cross Apply inorder to avoid the function all together.

Best Regards,
Niladri Biswas

Dnandha87, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dnandha87 on: 5/17/2013 [Member] Starter | Points: 25

0	Thanks for your reply Niladri.. funtion GetFileName_fn contains simple select statements which returns the file name. How can i use the PT_GUID value in your query.. its the #TaskCards- temptable coulmn name... all the others are local variable. i couldnt avoid function call... I should use that function to get the filename from another db... The function is also out of my control. Regards Nandha Kumar, Dnandha87, if this helps please login to Mark As Answer. \| Alert Moderator

Thanks for your reply Niladri..

funtion GetFileName_fn contains simple select statements which returns the file name.

How can i use the PT_GUID value in your query.. its the #TaskCards- temptable coulmn name... all the others are local variable.

i couldnt avoid function call...
I should use that function to get the filename from another db...
The function is also out of my control.

Regards
Nandha Kumar,

Dnandha87, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dnandha87 on: 5/17/2013 [Member] Starter | Points: 25

0	I tried the way that you suggested, it also takes 3 mints for execution.. Regards Nandha Kumar, Dnandha87, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Neeraaj.Sharma on: 6/24/2013 [Member] Starter | Points: 25

0	hi Nandha Kumar , i guess dbo.GetFileName_fn is killing your time, can you remove it, just try to use it directly to query. and this is not helping you then show code of your function here Neeraj prasad sharma Visit my Blog http://www.tutorialsqlserver.com/ Dnandha87, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pandians on: 6/25/2013 [Member] [MVP] Silver | Points: 25

0	Hey Dnandha87 Please try with SET STATISTICS TIME ON GO You may know which statement taken time! Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Dnandha87, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts