I want to optimise the query

Question

I want to optimise the query

vikram_prince2003

Newbie

Points: 7
More actions
February 6, 2012 at 8:12 am

#145026

Here is the query, it takes 13 seconds to execute the query. By somehow I want to reduce the execution time.
I want to execute this query less than 5 seconds. Please help me out
SELECT
DISTINCT(C.DPCodes) [ICD9Code],--ICD9 Code
CASE S.ICD_10_DCode
WHEN 'NoDx' THEN 'NoDx'
WHEN 'NO' THEN 'NO'
WHEN NULL THEN 'NA'
ELSE S.ICD_10_DCode
END [ICD10Code],--ICD10 Code
CAST(S.Approximate_Flag AS char(1))+ CAST(S.No_Map_Flag AS char(1))+ CAST(S.Combination_Flag AS char(1))+ CAST(S.Scenario AS char(1))+ CAST(S.Choice_List AS char(1)) [Flags],--Combining all flags al one value
CASE--
WHEN (SELECT
COUNT(*)
FROM
TBL_ICD_FORWARD_D_GEMMAPPINGS
WHERE
ICD_9_DCode=S.ICD_9_DCode
)=1 THEN 'YES'
ELSE 'NO'
END [Unique],--Unique Mappings
(SELECT
Count(*)
FROM
TBL_ICD_837_DPCodes
WHERE
DPCodes=C.DPCodes
) [Count]--Count of code occurence
FROM
TBL_ICD_837_DPCodes C
Left JOIN TBL_ICD_FORWARD_D_GEMMAPPINGS S
ON C.DPCodes = S.ICD_9_DCode AND S.Default_Flag=1
WHERE
C.Qualifier IN ('BK','BJ','PR','BN','BF')
AND C.File_Id --Searchable file ID
IN
(
SELECT
File_Id
FROM
TBL_ICD_FileSubmission
WHERE
Transaction_Name ='837'
AND Search_Flag='True'
AND CONVERT(VARCHAR(11),creation_date) --Searchable date
IN
(
SELECT
creation_date
FROM
TBL_ICD_FileDates
WHERE
files_status = 'True'
AND transaction_name = '837'
)
)
ORDER BY
[Count] DESC,
[ICD9Code] ASC

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

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

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

Please post table definitions, index definitions and execution plan, 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

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

Hi and welcome to SSC. In order for us to help you, you first have to help us to help you. What do i mean by that? Post ddl (create table scripts), sample data (insert statements) and desired results based on your sample data. Once you post that we are all on the same page and can help optimize your query.

_______________________________________________________________

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/

Umer Hussain Minhas SSChasing Mays Points: 620 More actions · Answer 3

Wow

Umer Hussain,Software Engineer,Net SolY-126 Commercial AreaDHA, Lahore Pakistan