Does any one see any improvements that can be made to the following queries. They are taking a long time.
SELECT a.wire_center, a.wire_center_name, a.terminal_name, street_num, street_name, unit, floor, building, a.id, living_unit_id, community, state_cd
FROM lead_seq_lu_address a left outer join lead_seq_taper_da b ON a.terminal_name=b.terminal_name and a.wire_center=b.wire_center
WHERE a.wire_center=@v_wire_center and b.terminal_name is NULL ORDER BY a.terminal_name
SELECT @v_ls_override_terminal_name = override_terminal_name, @v_ls_override_street_num = override_street_num, @v_ls_override_street_name = override_street_name, @v_ls_override_living_unit_id = override_living_unit_id, @v_ls_override_state_cd = override_state_cd
WHERE wire_center=@v_lslu_wire_center AND
( (terminal_name = @v_lslu_terminal_name) OR
( (terminal_name IS NULL) AND
(@v_lslu_terminal_name IS NULL) ) ) AND
( (living_unit_id = @v_lslu_living_unit_id) OR
( ( living_unit_id IS NULL) AND
( @v_lslu_living_unit_id IS NULL) ) ) AND
( (state_cd = @v_lslu_state_cd)OR ( ( state_cd IS NULL) AND ( @v_lslu_state_cd IS NULL) ) )
How large are these tables ? What does the execution plan reveal ? What indexes are being used ? Are any table scans being performed ? How long are they taking ? How are the queries being run - through a stored procedure... ?
Question on Query #1, you join on columns terminal_name and wire_center, are they indexed? If so which way are they indexed, since the second part of the condition looks at terminal_name is NULL. If terminal_name is part of the index and the first column, then I have a feeling SQL will do a scan since you are looking for NULL.
You may want to change the order of the index, also you may want to remove the ability of having NULL as a value. In some cases you don't have a choice, but if you do then disallow NULLs and use spaces.