CODEWITHSUNDEEP
sqlsql-server-2008
Benedict
Benedict

Reputation: 5

SQL query execute takes more time

here i combine two table and get the result.

SELECT * 
FROM  dbo.LabSampleCollection
WHERE CONVERT(nvarchar(20), BillNo) + CONVERT(Nvarchar(20), ServiceCode)
      NOT IN (SELECT CONVERT(nvarchar(20), BillNo) + CONVERT(Nvarchar(20), ServiceCode)                            
              FROM dbo.LabResult)

the problem is Its take more time to execute. is there is any alternative way to handle this.

Upvotes: 0

Views: 75

Answers (2)

hamish
hamish

Reputation: 455

Are you familiar with query execution plans, if not then i strongly recommend you read up on them? If you are going to be writing queries and troubleshooting/trying to improve performance they are one of the most useful tools (along with some basic understanding of what they are how SQL server optimization engine works).

You can access them from SSMS via the activity monitor or by running the query itself (us the Income actual execution plan button or ctrl-M) and they will tell you exactly which part of the query is the most inefficient and why. There are many very good articles on the web on how to improve performance using this valuable tool e.g. https://www.simple-talk.com/sql/performance/execution-plan-basics/

Upvotes: 0

Tom
Tom

Reputation: 747

SELECT  *
FROM    dbo.LabSampleCollection sc 
WHERE   NOT EXISTS (    SELECT  BillNo
            FROM    dbo.LabResult r
            WHERE   r.BillNo = sc.BillNo
            AND r.ServiceCode = sc.Servicecode)

No need to combine the two fields, just check if both are available in the same record. It would also be better to replace the * with the actual columns that you wish to retrieve. (The selected column BillNo in the second select state is just there to limit the results of the second query.

Upvotes: 4

Related Questions