would like to do query tuning for this stored procedure

saptek9 2016-09-27 12:55:33

Nicolas Souquet 2016-09-27 21:39:08
Hi Saptek9,

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.

Vlady Oselsky 2016-10-03 20:02:04
Since Aaron has not answered yet, I will provide a link to his blog post Cursor Options First thing that jumps out at me, you are not using any options which cause poor performance when it comes to cursors in SQL Server.

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.