Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

CPU intensive query#2

Dear all,

I found out some SQL queries that make the CPU hight, tempdb usage hight and I dont' know how to solve, please comment.

FROM #VendorDD dd 				
JOIN @CountryCode cc ON dd.CountryCode = cc.CountryCode				
LEFT JOIN #DDRoot r1 ON dd.DialedDigits = r1.DD				
LEFT JOIN #DDRoot r2 ON dd.DestinationID = r2.AttributesID 				-- 2004.10.25 sg #7148-#7149
WHERE r1.DD IS NULL				
AND r2.AttributesID IS NULL				
AND	@EffDate BETWEEN dd.BeginDate AND ISNULL(dd.EndDate, @EffDate)
.....

.
			

Open in new window


now the in the execution plan, what I can see the high cost is 2 x table scan, so how to solve it?

execution plan:

#1
#2
0
marrowyung
Asked:
marrowyung
  • 3
1 Solution
 
PortletPaulCommented:
can you provide:

a. the complete query, and
b. the complete execution plan (.sqlplan file, xml, not an image)

http://msdn.microsoft.com/en-us/library/ms190646.aspx

please note the second table scan is of an estimated 2 rows, but it is iterated 17109 times
so may not be the scan that is the problem, but the iterations
0
 
marrowyungAuthor Commented:
I can't find that plan anymore, I will start another question.
0
 
marrowyungAuthor Commented:
as you proactive help me, then that;s why you get my award! I appreciate your help and I usually just want the helper and reader direct me to the right point of solving problem why is it.

and I will keep learning from you all.
0
 
marrowyungAuthor Commented:
ok, tks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now