would like to do query tuning for this stored procedure
You will need to get rid of the cursor.
Cursors are actually underperforming in SQL engines because SQL is set-based by nature, i.e. not designed to process data on a row-per-row basis (cf. RBAR).
Then you will need to add an index on the dbo.tbl_job_employementconditions, dbo.tbl_secondarypositions, dbo.tbl_job_type tables, on the job_id column; here we can probably afford the luxury of a key lookup to retrieve the smoking column.
You can also replace all COUNT(*) by EXISTS predicates. For example, you can rewrite :
if(((select count(*) from tbl_job_directmatch where title_id = (select prim_pos_id from tbl_jobs_test where job_id = @JobID)) > 0) or ((select count(*) from tbl_job_nearmatch where title_id = (select prim_pos_id from tbl_jobs_test where job_id = @JobID)) > 0) )
By :
IF EXISTS ( SELECT 1 FROM dbo.tbl_job_directmatch AS JD INNER JOIN dbo.tbl_jobs_test AS JT ON JT.prim_pos_id = JD.title_id WHERE JT.job_id = @JobID UNION SELECT 1 FROM dbo.tbl_job_nearmatch AS JN INNER JOIN dbo.tbl_jobs_test AS JT ON JN.title_id = JT.prim_pos_id WHERE JT.job_id = @JobID ) BEGIN ... END
How many rows does the dbo.tbl_jobs_test table have please ?
Can you give the definition of the dbo.GetJobSuitabilityScore() function please ?
Nicolas.
This might not be the magic silver bullet you are looking for, but before you spend too much time trying to improve performance doing anything else try to make simple change outlined below.
declare j1 cursor for --ORIGINAL declare j1 cursor for LOCAL FAST_FORWARD --MODIFIED
Now onto issues in the plan. You have multiple TABLE SCANs on the same table [tbl_jobs_test], you should consider CLUSTERED INDEX or at least non-Clustered covered index to prevent multiple trips to the table trying to get very little data. If you pay attention to the ! (exclamation point) the plan it gives you missing index warning
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tbl_jobs_test] ([job_id]) GO
SQL Server estimates improvements of 100% which is unrealistic but once you have created that index you will get a new plan and will have more other issues to fix.
Hopefully, this will get you started on how to fix your stored procedure.