Optimising the insert into temp table query

Question

Optimising the insert into temp table query

raj.prabhu001

Right there with Babe

Points: 758
More actions
February 12, 2014 at 7:18 am

#289354

i have the below query which takes 1 hour to run please suggest any optimising method for the same :
Insert Into #Finaldata
Select Distinct A.id,F.me,F.status ,left(A.ffunction,3) 'function',
left(A.ction,6) '6characters',
A.sublist,A.status ,
'','','','' 'Account_status',''
From DBO.FUNCTION A Join #Finaldata B
On A.ccap_id=B.ccap_id
And A.id Not In (Select distinct id From #Finaldata)
left Join dbo.CUSTOMER F
On A.id=F.idl
left Join dbo.USER C
On A.ccap_id=C.ccap_id
And A.id=C.id
Where left(A.ffunction,3)<>'@@@'
Order by A.id

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table definitions, index definitions and execution plan please, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

raj.prabhu001 Right there with Babe Points: 758 More actions · Answer 2

raj.prabhu001

Right there with Babe

Points: 758

February 12, 2014 at 2:25 pm

#1688564

please find the attached plan

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

You're still missing DDL for tables and indexes.

Things might speed up if you remove the ORDER BY and change your WHERE clause to

WHERE A.entitlement_function NOT LIKE '@@@%'

However, more improvements can be made with some changes to the DB (and possibly the code to remove the self join but I'm not sure). I'm sure that others will give better advices.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Don't use function in the where clause. That will slow down the performance by disabling the indexes. Do as Luiz mentioned.

--

SQLBuddy