Optimize the Sql query

Posted by Dnandha87 under Sql Server on 5/16/2013 | Points: 10 | Views : 929 | Status : [Member] | Replies : 5
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,



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response