Results 1 to 4 of 4

Thread: Tuning queries

  1. #1
    Join Date
    May 2002
    Posts
    62

    Unanswered: Tuning queries

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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... ?

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.
    MCDBA

  4. #4
    Join Date
    May 2002
    Posts
    62
    Thanx rnealejr and achorozy for your replies.

    The tables as large as 10 million rows. They are run through the stored procedure. Execution plan says there are table scans. Index analysis suggested to create two indexes.

    For query #1:

    CREATE NONCLUSTERED INDEX [LEAD_SEQ_TAPER_DA0] ON [dbo].[LEAD_SEQ_TAPER_DA]([TERMINAL_NAME], [WIRE_CENTER])

    For query #2:

    CREATE NONCLUSTERED INDEX [LOAD_ERROR_LU_ADDRESS0] ON [dbo].[LOAD_ERROR_LU_ADDRESS]([WIRE_CENTER], [TERMINAL_NAME], [STATE_CD], [LIVING_UNIT_ID], [OVERRIDE_TERMINAL_NAME], [OVERRIDE_STREET_NUM], [OVERRIDE_STREET_NAME], [OVERRIDE_STATE_CD], [OVERRIDE_LIVING_UNIT_ID])


    I am going to test after creating these indexes. The reason I had posted on the forum is whether there is any possiblity of changing the query and write it in a different way.

    Thanx once again.
    -Bheem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •