December 19, 2006 at 6:49 am
Hi all, having a lot of performance issues with the following query. Worked great in SQL 7.0 but performs poorly in SQL 2000. I think it has something to do with the IS NULLs mixed with th OR LIKE statements. It's a search stored proc for wild card seaches. Any suggestions?
Thanks
CREATE PROCEDURE dbo.sscn_sp_surg_case_Q
@surg_case_id int = NULL, -- Booking ID
@rqst_date varchar(40) = NULL, -- Booking Date
@rha_case_no varchar(10) = NULL, -- Case Number
@rha_id smallint = NULL, -- Regional Health Authority
@fac_id smallint = NULL, -- Facility
@pat_eval_id int = NULL, -- Score Id
@eval_scor float = NULL, -- Clinical Priority Score
@cncr varchar(1) = NULL, -- Cancer Diagnosis
@sscn_wait_list_cat_desc varchar(50) = NULL, -- Priority Level
@sscn_pat_id varchar(12) = NULL, -- Health Services Number
@sscn_pat_id_type smallint = NULL, -- HSN Province
@rha_alt_pat_id varchar(10) = NULL, -- Alt. Patient ID
@name_last varchar(50) = NULL, -- Patient Last Name
@name_1st varchar(50) = NULL, -- Given Name(s)
@born_date varchar(40) = NULL, -- Date of Birth
@sex tinyint = NULL, -- Sex
@pat_unvl_strt_perd_1 varchar(40) = NULL, -- Unavailable From Date #1
@pat_unvl_end_perd_1 varchar(40) = NULL, -- Unavailable To Date #1
@pat_unvl_xpln_id_perd_1 smallint = NULL, -- Unavailable Reason #1
@pat_unvl_strt_perd_2 varchar(40) = NULL, -- Unavailable From Date #2
@pat_unvl_end_perd_2 varchar(40) = NULL, -- Unavailable To Date #2
@pat_unvl_xpln_id_perd_2 smallint = NULL, -- Unavailable Reason #2
@pat_unvl_strt_perd_3 varchar(40) = NULL, -- Unavailable From Date #3
@pat_unvl_end_perd_3 varchar(40) = NULL, -- Unavailable To Date #3
@pat_unvl_xpln_id_perd_3 smallint = NULL, -- Unavailable Reason #3
@refr_date varchar(40) = NULL, -- Referring Date
@sscn_refr_md_id int = NULL, -- Physician Id
@md_name varchar(72) = NULL, -- Physician Name
@md_addr_line_1 varchar(50) = NULL, -- Address1
@md_addr_line_2 varchar(50) = NULL, -- Address2
@md_cmty_desc varchar(50) = NULL, -- City
@md_tel_no varchar(50) = NULL, -- Telephone #
@init_srgn_vsit_date varchar(40) = NULL, -- Initial Surgeon Visit Date
@sscn_srgn_id int = NULL, -- Surgeon
@rha_srgn_alt_id varchar(10) = NULL, -- Alt. Surgeon Id
@prim_proc_code varchar(15) = NULL, -- Primary Procedure Code
@prim_proc_desc varchar(255) = NULL, -- Primary Procedure Name
@scnd_proc_code_1 varchar(15) = NULL, -- Secondary Procedure #1
@scnd_proc_desc_1 varchar(255) = NULL, -- Secondary Procedure Name #1
@scnd_proc_code_2 varchar(15) = NULL, -- Secondary Procedure #2
@scnd_proc_desc_2 varchar(255) = NULL, -- Secondary Procedure Name #2
@scnd_proc_code_3 varchar(15) = NULL, -- Secondary Procedure #3
@scnd_proc_desc_3 varchar(255) = NULL, -- Secondary Procedure Name #3
@sscn_anae_type_id smallint = NULL, -- Anaesthetic
@sscn_enct_type_id smallint = NULL, -- Encounter Type
@surg_schd_date varchar(40) = NULL, -- Scheduled Date of Surgery
@sscn_surg_stat_id smallint = NULL, -- Current Status
@surg_defr_date varchar(40) = NULL, -- Surgery Deferred Date
@surg_defr_xpln_id smallint = NULL, -- Cancellation/Postponed Reason
@surg_iatv_date varchar(40) = NULL, -- Surgery Inactive Date
@sscn_surg_iatv_xpln_id smallint = NULL, -- Inactive Reason
@surg_case_xpln varchar(255) = NULL, -- General Comments
@DUMMY INT = NULL -- Place holder used to define the end of the parameter list.
AS
SELECT * FROM sscn_vw_cust_surg_case_form
WHERE
(@surg_case_id IS NULL OR surg_case_id = @surg_case_id) AND
(@rqst_date IS NULL OR CONVERT(varchar(30),rqst_date,109) LIKE @rqst_date) AND
(@rha_case_no IS NULL OR rha_case_no LIKE @rha_case_no) AND
(@rha_id IS NULL OR rha_id = @rha_id) AND
(@fac_id IS NULL OR fac_id = @fac_id) AND
(@pat_eval_id IS NULL OR pat_eval_id = @pat_eval_id) AND
(@eval_scor IS NULL OR eval_scor LIKE @eval_scor) AND
(@cncr IS NULL OR cncr LIKE @cncr) AND
(@sscn_wait_list_cat_desc IS NULL OR sscn_wait_list_cat_desc LIKE @sscn_wait_list_cat_desc) AND
(@sscn_pat_id IS NULL OR sscn_pat_id LIKE @sscn_pat_id) AND
(@sscn_pat_id_type IS NULL OR sscn_pat_id_type LIKE @sscn_pat_id_type) AND
(@rha_alt_pat_id IS NULL OR rha_alt_pat_id LIKE @rha_alt_pat_id) AND
(@name_last IS NULL OR name_last LIKE @name_last) AND
(@name_1st IS NULL OR name_1st LIKE @name_1st) AND
(@born_date IS NULL OR CONVERT(varchar(30),born_date,109) LIKE @born_date) AND
(@sex IS NULL OR sex = @sex) AND
(@pat_unvl_strt_perd_1 IS NULL OR CONVERT(varchar(30),pat_unvl_strt_perd_1,109) LIKE @pat_unvl_strt_perd_1) AND
(@pat_unvl_end_perd_1 IS NULL OR CONVERT(varchar(30),pat_unvl_end_perd_1,109) LIKE @pat_unvl_end_perd_1) AND
(@pat_unvl_xpln_id_perd_1 IS NULL OR pat_unvl_xpln_id_perd_1 LIKE @pat_unvl_xpln_id_perd_1) AND
(@pat_unvl_strt_perd_2 IS NULL OR CONVERT(varchar(30),pat_unvl_strt_perd_2,109) LIKE @pat_unvl_strt_perd_2) AND
(@pat_unvl_end_perd_2 IS NULL OR CONVERT(varchar(30),pat_unvl_end_perd_2,109) LIKE @pat_unvl_end_perd_2) AND
(@pat_unvl_xpln_id_perd_2 IS NULL OR pat_unvl_xpln_id_perd_2 LIKE @pat_unvl_xpln_id_perd_2) AND
(@pat_unvl_strt_perd_3 IS NULL OR CONVERT(varchar(30),pat_unvl_strt_perd_3,109) LIKE @pat_unvl_strt_perd_3) AND
(@pat_unvl_end_perd_3 IS NULL OR CONVERT(varchar(30),pat_unvl_end_perd_3,109) LIKE @pat_unvl_end_perd_3) AND
(@pat_unvl_xpln_id_perd_3 IS NULL OR pat_unvl_xpln_id_perd_3 LIKE @pat_unvl_xpln_id_perd_3) AND
(@refr_date IS NULL OR CONVERT(varchar(30),refr_date,109) LIKE @refr_date) AND
(@sscn_refr_md_id IS NULL OR sscn_refr_md_id = @sscn_refr_md_id) AND
(@md_name IS NULL OR md_name LIKE @md_name) AND
(@md_addr_line_1 IS NULL OR md_addr_line_1 LIKE @md_addr_line_1) AND
(@md_addr_line_2 IS NULL OR md_addr_line_2 LIKE @md_addr_line_2) AND
(@md_cmty_desc IS NULL OR md_cmty_desc LIKE @md_cmty_desc) AND
(@md_tel_no IS NULL OR md_tel_no LIKE @md_tel_no) AND
(@init_srgn_vsit_date IS NULL OR CONVERT(varchar(30),init_srgn_vsit_date,109) LIKE @init_srgn_vsit_date) AND
(@sscn_srgn_id IS NULL OR sscn_srgn_id = @sscn_srgn_id) AND
(@rha_srgn_alt_id IS NULL OR rha_srgn_alt_id LIKE @rha_srgn_alt_id) AND
(@prim_proc_code IS NULL OR prim_proc_code LIKE @prim_proc_code) AND
(@prim_proc_desc IS NULL OR prim_proc_desc LIKE @prim_proc_desc) AND
(@scnd_proc_code_1 IS NULL OR scnd_proc_code_1 LIKE @scnd_proc_code_1) AND
(@scnd_proc_desc_1 IS NULL OR scnd_proc_desc_1 LIKE @scnd_proc_desc_1) AND
(@scnd_proc_code_2 IS NULL OR scnd_proc_code_2 LIKE @scnd_proc_code_2) AND
(@scnd_proc_desc_2 IS NULL OR scnd_proc_desc_2 LIKE @scnd_proc_desc_2) AND
(@scnd_proc_code_3 IS NULL OR scnd_proc_code_3 LIKE @scnd_proc_code_3) AND
(@scnd_proc_desc_3 IS NULL OR scnd_proc_desc_3 LIKE @scnd_proc_desc_3) AND
(@sscn_anae_type_id IS NULL OR sscn_anae_type_id LIKE @sscn_anae_type_id) AND
(@sscn_enct_type_id IS NULL OR sscn_enct_type_id LIKE @sscn_enct_type_id) AND
(@surg_schd_date IS NULL OR CONVERT(varchar(30),surg_schd_date,109) LIKE @surg_schd_date) AND
(@sscn_surg_stat_id IS NULL OR sscn_surg_stat_id LIKE @sscn_surg_stat_id) AND
(@surg_defr_date IS NULL OR CONVERT(varchar(30),surg_defr_date,109) LIKE @surg_defr_date) AND
(@surg_defr_xpln_id IS NULL OR surg_defr_xpln_id LIKE @surg_defr_xpln_id) AND
(@surg_iatv_date IS NULL OR CONVERT(varchar(30),surg_iatv_date,109) LIKE @surg_iatv_date) AND
(@sscn_surg_iatv_xpln_id IS NULL OR sscn_surg_iatv_xpln_id LIKE @sscn_surg_iatv_xpln_id) AND
(@surg_case_xpln IS NULL OR surg_case_xpln LIKE @surg_case_xpln)
ORDER BY surg_case_id ASC
December 19, 2006 at 7:55 am
I would recmend you that apply the converstion on the date value parameter and used equel (=) rather then like operator and it will slove your problem. even you can remove converstion if you would like.
i.e;
(@surg_iatv_date IS NULL OR CONVERT(varchar(30),@surg_iatv_date,109) =surg_iatv_date)
cheers
December 19, 2006 at 8:02 am
I would suggest to check out the following (as it might also be helpful for future reference):
http://www.sql-server-performance.com/tips_performance.asp
December 19, 2006 at 8:13 am
I'll give that a try and see what happens. Is it because the optimizer in SQL 2000 processes the Like operator different than it did in SQL 7.0?
December 20, 2006 at 9:48 am
Also, make sure your variable datatypes match up to your column datatypes. This has oft been reported as the cause of performance degradation between 7 and 2000.
January 10, 2007 at 7:06 am
Hi all, thanks for all the insight, much appreciated.
Quest what the problem was. it was actually the order by clause at the end of the statement. the optimiser actually physically orders the data that may not even be brought back in the query prior to performing other operations in the query. Crazy.
Thanks
January 10, 2007 at 8:57 am
I bet that query will not scale.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply