CPU intensive query#2

Posted on 2014-08-10
Last Modified: 2014-10-03
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
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:

Question by:marrowyung
    LVL 47

    Accepted Solution

    can you provide:

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

    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
    LVL 1

    Author Comment

    I can't find that plan anymore, I will start another question.
    LVL 1

    Author Comment

    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.
    LVL 1

    Author Comment

    ok, tks.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now