Please Help me in the query

Question

Post reply

Please Help me in the query

rj529

Ten Centuries

Points: 1194
More actions
June 28, 2013 at 4:23 am

#279188

Hi team,
The below query takes 100 % CPU. When I see in execution plan, its going in index seek. Kindly help me to get rid off this issue.
SELECT
CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli
END 'cli', min(call_date) 'date'
FROM DbName..table_name1(nolock)
WHERE network_id = '1' and dialed_number not in(select msisdn from table_name2(nolock))
group by CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli END
Note : CLI & call_date comes in table_name1 table
Thanks in advance

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

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Can you post your execution plan?

You can try replacing the NOT IN with NOT EXISTS

SELECT

CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli

END 'cli', min(call_date) 'date'

FROM DbName..table_name1(nolock)

WHERE network_id = '1' and NOT EXISTS( SELECT * FROM table_name2(nolock) WHERE msisdn = dialed_number )

group by CASE WHEN substring(cli, 1, 2) = '44' THEN cli ELSE '44' + cli END

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Table definitions, index definitions and execution plan please.

p.s. Watch those nolocks, do you know what effect that can have on your data accuracy?

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

rj529 Ten Centuries Points: 1194 More actions · Answer 3

rj529

Ten Centuries

Points: 1194

June 28, 2013 at 7:37 am

#1628288

Execution plan attached.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

rajkiran.panchagiri (6/28/2013)
Execution plan attached.

Can you post the actual execution plan? The estimated plan is pretty useless here. Also as Gail asked previously, please post the table definitions and indexes.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

An index on column msisdn of table vmd_blulm would help, though probably not as much as the plan suggests. I'd do this first because it's likely skewing the relative costs.

The Actual plan is often much more useful than the Estimated plan.

Can you post the definition of vw_cdr please, in addition to Gail's requests above.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden