Reputation: 5
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
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
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