Writing T-SQL or C# or any code, we should keep performance efficiency in mind

For example, the following code checking if a record exists in T-SQL using NOT IN. That is not efficient code.

Many people know how to do this with IN, but don't know how to do with NOT IN. The following example illustrates the technique:

I have the following code in prodution stored procedure:

Change this block : INSERT INTO #tmpMinMax SELECT DISTINCT S.BillOid, MIN(S.servicedate),MAX(S.servicedate) FROM orders S WITH (NOLOCK) left outer join Bills B WITH (NOLOCK) on B.Oid=S.BillOid WHERE B.Oid NOT IN(SELECT isnull(BillOid,0) FROM #tmpMinMax) AND ISNULL(B.IsDeleted,0) = 0 AND (B.billtypeoid=5) GROUP BY S.BillOid

To the following block :  INSERT INTO #tmpMinMax SELECT DISTINCT S.BillOid, MIN(S.servicedate),MAX(S.servicedate) FROM orders S WITH (NOLOCK) left outer join Bills B WITH (NOLOCK) on B.Oid=S.BillOid left outer join #tmpMinMax tem WITH (NOLOCK) on S.BillOid = tem.BillOid WHERE ISNULL(B.IsDeleted,0) = 0 AND (B.billtypeoid=5) AND tem.BillOid is null GROUP BY S.BillOid

this is the 3rd query statement inside a stored procedure that is reduced from 10-11 minutes to 6-8 seconds without index change, partition table etc. That is 82.5 times better, 8250% improvement. For those who want to understand, the changed code utilizes the advantage of existing index.

orders table contains 638,000+ records, bills table contains 187,000+ records. They are not particular big. Each user saves 10 minutes when they run this SP behind the UI.

Note: we are using WITH (NOLOCK) because this is in a report. Our scenario in our environment only requires READ UNCOMMITTED isolation level.

I have other large stored procedure I have changed that improves from 5-6 hours run to 10 minutes run, 3600%, 4500%, 8250% (yes, not a typo, 8250%) etc. improvement.