Download 100% FREE Office Document APIs for .NET
DotNetFunda.com
Online: 884
Home > Forums > Sql Server >

Optimize the Sql query

Posted by Dnandha87 under Sql Server on 5/16/2013 | Points: 10 | Views : 1014 | Status : [Member] | Replies : 5
Write New Post | Search Forums | AnsweredResolved 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

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

Latest Posts