September 27, 2002 at 2:36 pm
Hi,
Does any one see any improvements that can be made to the following queries. They are taking a long time.
Query #1:
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
Query #2:
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
FROM LOAD_ERROR_LU_ADDRESS
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) ) )
Thanx and I appreciate your inputs.
-Bheem
September 27, 2002 at 4:34 pm
Do you have any indexes? Are they table or views?
September 27, 2002 at 4:49 pm
#1 needs indexes. That's the only thing I could see.
#2 probably same thing.
Steve Jones
September 28, 2002 at 2:15 am
DECLARE @V_WIRE_CENTER INT
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
AND A.WIRE_CENTER=@V_WIRE_CENTER
AND B.TERMINAL_NAME IS NULL
ORDER BY A.TERMINAL_NAME
CREATE CLUSTERED INDEX C_LEAD_SEQ_LU_ADDRESS ON LEAD_SEQ_LU_ADDRESS (TERMINAL_NAME, WIRE_CENTER)
CREATE CLUSTERED INDEX C_LEAD_SEQ_TAPER_DA ON LEAD_SEQ_TAPER_DA (TERMINAL_NAME, WIRE_CENTER)
CLUSTERED INDEX IS PREFERABLE ON THIS TABLE IF NOT POSSIBLE CREATE NON CLUSTERED INDEX
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
FROM LOAD_ERROR_LU_ADDRESS
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)
)
)
CREATE CLUSTERED INDEX C_LOAD_ERROR_LU_ADDRESS ON LOAD_ERROR_LU_ADDRESS (WIRE_CENTER, TERMINAL_NAME, LIVING_UNIT_ID, STATE_CD)
CLUSTERED INDEX IS PREFERABLE ON THIS TABLE IF NOT CREATE NON CLUSTERED INDEX
Make sure the sequence of the index columns is right, depends on the density of the values change it.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 28, 2002 at 2:16 am
I remove the where clause also from the query....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 30, 2002 at 10:19 am
Steve and Prakash,
Your suggestions are wonderful. That's what "index analysis" also suggested. I am going to go with your suggestions.
Thanx..
-Bheem
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply