Let me give an overview of one of multi join Ad hoc query with couple of where condition, which I got received to improve the run time speed.
I got a statement from a developer that one of code is running long time, more than 15+ hours, which usually runs in less than a minute. I have asked them how a less than minute code will run more than 15 hours, they said no changes made, I am sure they are lying and it is a Ad-hoc, I cannot trace the DDL changes.
I started doing basic check and it is an eight table join with group by and order by. Basically, it is searching policy details and getting a report of all policy except passing parameter. Ex: If we pass three policy ID except the three it should return all other policy details. As first step checked number rows in all involved tables to look larger table.
I have asked again any changes made in the code or adjustment in where clause. The default answer – They said no changes.
By seeing that code, It is easy to understand that, why they written ‘NOT IN’ – Where policy_ID NOT IN (select policy_ID from @tembvariable)
I have re written the code which turned into 30 seconds run time: Logic is load all data without not in where clause and delete the passing parameter from the loaded data.
1) Removed the NOT IN clause and kept other where conditions
Removed: Where policy_ID NOT IN (select policy_ID from @tembvariable)
Kept: Date < ‘’ and other conditions as well
2) Loaded the data into temp table
Just a created a table #tbl_policy_details and insert into #tbl_policy_details
3) Deleted the parameter values from the temp table
Delete from #tbl_policy_details where policy_id in (select policy_ID from @tembvariable)
Select * from #tbl_policy_details
Drop table #tbl_policy_details
It took less than 35 seconds. The reason why it took 15+ hours, the code written by developer is join with eight tables that have millions of record and it has to inter join rows with NON SARG method (NOT IN) and almost it returns 95% table record after joining all tables, think about the optimizer job, how difficult to generate a query plan.
The non SQL developers do not understand optimizer and query plan etc.
Query: It simply removing condition WHERE s.reference not in (select id from @omischeme) and load all data into temp table #tbl_MI_Stats_load, after a load delete passing parameter data DELETE from #tbl_MI_Stats_load where [Ref.] in (select id from @omischeme).
Query Run time: It took 15+ hours, I just stopped it. of course both plan are would different, but the query took 15+ hours does not generate a plan, since optimizer cannot generate optimal plan.
