Multiple tables select performance - is 90 seconds normal?

  • I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.

    The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.

    I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).

    I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).

    I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.

    I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.

    So my question is: Is it normal for such a type of read query to take 90 seconds to complete?

    Is there anything I could do to speed it up.

    Any other thoughts?

    Thanks

    http://90.212.51.111 domain

  • Can you post the query and which columns are indexed on which tables?

    Could be a valid run time, or could not. Hard to tell.

    Look in the plan for table scans, If you're getting one on a large table, it could easily get to that time.

  • Seems awfully long - sounds like something's missing.

    neil (12/4/2007)


    I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.

    Search is not the right word - was it a seek or a scan? makes a BIG difference.

    I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.

    You really should move up to current ANSI notation as to JOINS, so that you don't have cartesian products going on... It really does help clear up those kind of issues IMO.

    As Steve said though - post the query, so we can take a look.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Here is my query. 9 tables.

    They were seeks not scans.

    select [D1].[SID_0COMPANY] AS [SID_0COMPANY]

    FROM mdp.[/BIC/FZFIGL_C02] [F] JOIN mdp.[/BIC/DZFIGL_C02T] [DT] ON [F].[KEY_ZFIGL_C02T] = [DT].[DIMID]

    JOIN mdp.[/BI0/SFISCYEAR] [S2] ON [DT].[SID_0FISCYEAR] = [S2].[SID]

    JOIN mdp.[/BIC/DZFIGL_C022] [D2] ON [F].[KEY_ZFIGL_C022] = [D2].[DIMID]

    JOIN mdp.[/BIC/DZFIGL_C023] [D3] ON [F].[KEY_ZFIGL_C023] = [D3].[DIMID]

    JOIN mdp.[/BIC/DZFIGL_C02U] [DU] ON [F].[KEY_ZFIGL_C02U] = [DU].[DIMID]

    JOIN mdp.[/BIC/DZFIGL_C024] [D4] ON [F].[KEY_ZFIGL_C024] = [D4].[DIMID]

    JOIN mdp.[/BIC/DZFIGL_C02P] [DP] ON [F].[KEY_ZFIGL_C02P] = [DP].[DIMID]

    JOIN mdp.[/BIC/DZFIGL_C021] [D1] ON [F].[KEY_ZFIGL_C021] = [D1].[DIMID]

    where ( ( ( ( [DP].[SID_0CHNGID] = 0 ) )

    AND ( ( [D1].[SID_0COMPANY] IN ( 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121 ) ) )

    AND ( ( [DT].[SID_0FISCPER3] = 12 ) )

    AND ( ( [S2].[FISCYEAR] = N'2007' ) )

    AND ( ( [DP].[SID_0RECORDTP] = 0 ) )

    AND ( ( [DP].[SID_0REQUID] <= 2000000250 ) ) ) )

    The following tables have the following indexes.

    mdp.[/BIC/FZFIGL_C02]Clustered index on KEY_ZFIGL_C02P

    nonclustered nonunique index on KEY_ZFIGL_C02T

    nonclustered nonunique index on KEY_ZFIGL_C02U

    nonclustered nonunique index on KEY_ZFIGL_C021

    nonclustered nonunique index on KEY_ZFIGL_C022

    nonclustered nonunique index on KEY_ZFIGL_C023

    nonclustered nonunique index on KEY_ZFIGL_C024

    nonclustered nonunique index on all the above columns

    mdp.[/BIC/DZFIGL_C02T]nonclustered nonunique index on four columns

    SID_0FISCPER

    SID_0FISCVARNT

    SID_0FISCPER3

    SID_0FISCYEAR

    clustered index on DIMID

    mdp.[/BI0/SFISCYEAR]clustered index on two columns

    FISCVARNT

    FISCYEAR

    nonclustered unique index on SID

    mdp.[/BIC/DZFIGL_C022]nonclustered nonunique index on

    SID_0CHRT_ACCTS

    SID_0GL_ACCOUNT

    SID_0AC_DOC_NO

    SID_0ITEM_NUM

    SID_0PSTNG_DATE

    SID_0COORDER

    SID_ZMWSKZ

    SID_0CLEAR_DATE

    SID_0BCS_INITEM

    SID_0CS_ITEM

    SID_0CS_CHART

    clustered index on DIMID

    mdp.[/BIC/DZFIGL_C023]nonclustered nonunique index on

    SID_0AC_DOC_TYP

    SID_0MOVE_TYPE

    SID_0POST_KEY

    clustered index on DIMID

    mdp.[/BIC/DZFIGL_C02U]nonclustered nonunique index on

    SID_0DOC_CURRCY

    SID_0LOC_CURRC2

    SID_0LOC_CURRC3

    SID_0LOC_CURRCY

    SID_0UNIT

    clustered index on DIMID

    mdp.[/BIC/DZFIGL_C024]nonclustered nonunique index on

    SID_0WBS_ELEMT

    SID_0PART_PRCTR

    SID_0PCOMPANY

    SID_0PBUS_AREA

    SID_0LOC_CURTP2

    SID_0LOC_CURTP3

    SID_0REF_KEY1

    SID_0REF_KEY2

    clustered index on DIMID

    mdp.[/BIC/DZFIGL_C02P]nonclustered nonunique index on

    SID_0CHNGID

    SID_0RECORDTP

    SID_0REQUID

    clustered index on DIMID

    mdp.[/BIC/DZFIGL_C021]nonclustered nonunique index on

    SID_0COMP_CODE

    SID_0COSTCENTER

    SID_0PROFIT_CTR

    SID_0CO_AREA

    SID_0COMPANY

    clustered index on DIMID

    The Actual query plan showed:

    Index Seek 11%

    6 * clustered index seek at 11%

    3 * index seek also at 11%

    http://90.212.51.111 domain

  • This may or may not resolve the data a little faster but helps me a little bit if you can get the text version of the execution plan and post it.

    SELECT

    [D1].[SID_0COMPANY] AS [SID_0COMPANY]

    FROM

    mdp.[/BIC/FZFIGL_C02] [F]

    INNER JOIN

    mdp.[/BIC/DZFIGL_C02T] [DT]

    INNER JOIN

    mdp.[/BI0/SFISCYEAR] [S2]

    ON

    [DT].[SID_0FISCYEAR] = [S2].[SID]

    AND [S2].[FISCYEAR] = N'2007'

    AND [DT].[SID_0FISCPER3] = 12

    ON

    [F].[KEY_ZFIGL_C02T] = [DT].[DIMID]

    INNER JOIN

    mdp.[/BIC/DZFIGL_C022] [D2]

    ON

    [F].[KEY_ZFIGL_C022] = [D2].[DIMID]

    INNER JOIN

    mdp.[/BIC/DZFIGL_C023] [D3]

    ON

    [F].[KEY_ZFIGL_C023] = [D3].[DIMID]

    INNER JOIN

    mdp.[/BIC/DZFIGL_C02U] [DU]

    ON

    [F].[KEY_ZFIGL_C02U] = [DU].[DIMID]

    INNER JOIN

    mdp.[/BIC/DZFIGL_C024] [D4]

    ON

    [F].[KEY_ZFIGL_C024] = [D4].[DIMID]

    INNER JOIN

    mdp.[/BIC/DZFIGL_C02P] [DP]

    ON

    [F].[KEY_ZFIGL_C02P] = [DP].[DIMID]

    AND [DP].[SID_0CHNGID] = 0

    AND [DP].[SID_0RECORDTP] = 0

    AND [DP].[SID_0REQUID] <= 2000000250

    INNER JOIN

    mdp.[/BIC/DZFIGL_C021] [D1]

    ON

    [F].[KEY_ZFIGL_C021] = [D1].[DIMID]

    AND [D1].[SID_0COMPANY] IN ( 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121 )

  • ...and based on Antares' reformat - you might care to add on extra index per table covering each of the fields in the ON statement, and INCLUDE anything in the select.

    For example:

    Create index ix_d1

    on [D1].[SID_0COMPANY](DIMID,SID_OCOMPANY)

    Create index ix_dT

    on mdp.[/BIC/DZFIGL_C02T](DIMID,SID_0FISCYEAR,SID_0FISCPER3)

    Create index ix_dP

    on mdp.[/BIC/DZFIGL_C02P](DIMID,[SID_0CHNGID] ,[SID_0RECORDTP] ,SID_0REQUID])

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • One note

    mdp.[/BIC/FZFIGL_C02] Clustered index on KEY_ZFIGL_C02P

    nonclustered nonunique index on KEY_ZFIGL_C02T

    nonclustered nonunique index on KEY_ZFIGL_C02U

    nonclustered nonunique index on KEY_ZFIGL_C021

    nonclustered nonunique index on KEY_ZFIGL_C022

    nonclustered nonunique index on KEY_ZFIGL_C023

    nonclustered nonunique index on KEY_ZFIGL_C024

    nonclustered nonunique index on all the above columns

    I would drop the last index for sure and possibly the other non-clustered indexes as well. Just a question, do you always have the above table as your anchor table in queries (meaning it is the primary focus to the other tables? If so then odds are those indexes are not helping becuase they will not be used in the join most times unless you say where that column from that table has a specific value. However that said the last index if choosen by the query engine may take longer in the joins becuase only the first column will control the sorrt of the index thus all other joins will have to seek their values if the egine joins from the other tables to this table. Consider dropping that one at least and see if the time doesn't improve.

  • Matt Miller (12/4/2007)


    ...and based on Antares' reformat - you might care to add on extra index per table covering each of the fields in the ON statement, and INCLUDE anything in the select.

    For example:

    Create index ix_d1

    on [D1].[SID_0COMPANY](DIMID,SID_OCOMPANY)

    Create index ix_dT

    on mdp.[/BIC/DZFIGL_C02T](DIMID,SID_0FISCYEAR,SID_0FISCPER3)

    Create index ix_dP

    on mdp.[/BIC/DZFIGL_C02P](DIMID,[SID_0CHNGID] ,[SID_0RECORDTP] ,SID_0REQUID])

    I probably would remove before I considered adding. For example in the Matt suggestion

    Create index ix_d1

    on [D1].[SID_0COMPANY](DIMID,SID_OCOMPANY)

    here is the table as described in your post

    mdp.[/BIC/DZFIGL_C021] nonclustered nonunique index on

    SID_0COMP_CODE

    SID_0COSTCENTER

    SID_0PROFIT_CTR

    SID_0CO_AREA

    SID_0COMPANY

    clustered index on DIMID

    Consider this fact DIMID is the clustered index and SID_0COMPANY is in a non-clustered index. Under the hood of indexes the clustered index value is always also in the non-clustered index, because of this the index for SID_0COMPANY is actually to the Server

    SID_0COMPANY, DIMID

    But I think the key for you ultimately will be indexing.

  • BTW another thought, is this true

    [S2].[FISCYEAR] = N'2007'

    Is FISCYEAR really an NVARCHAR column if not but is a VARCHAR do like so

    [S2].[FISCYEAR] = '2007'

    but if it is numeric do like so

    [S2].[FISCYEAR] = 2007

    If not numeric I might consider changing to smallint or other numeric type to reduce implicit conversions the system may be doing to manage comparison.

  • I've uploaded the execution plan to

    Thanks

    http://90.212.51.111 domain

  • neil (12/4/2007)


    Here is the execution plan I saved as a .sqlplan file:

    Sorry I don't see it.

  • Sorry I had trouble posting it so I posted it to the web at:

    http://90.212.51.111 domain

  • Looking at it things look good as all the index lookups are seeks. However, I would still review and try all of my recommendations

    1) Try my altered query and see if the execution plan changes, I would hope to see the join between [/BIC/DZFIGL_C02T] [DT] AND mdp.[/BI0/SFISCYEAR] occur first.

    2) Drop the index from

    mdp.[/BIC/FZFIGL_C02] Clustered index on KEY_ZFIGL_C02P -- Keep this one for sure

    nonclustered nonunique index on KEY_ZFIGL_C02T -- Keep this one

    nonclustered nonunique index on KEY_ZFIGL_C02U <-

    nonclustered nonunique index on KEY_ZFIGL_C021 | Depending on if there are other queries

    nonclustered nonunique index on KEY_ZFIGL_C022 | run that would use these I would

    nonclustered nonunique index on KEY_ZFIGL_C023 | probably drop these

    nonclustered nonunique index on KEY_ZFIGL_C024 <-

    nonclustered nonunique index on all the above columns --- Drop this index

    3) Is FISCYEAR in mdp.[/BI0/SFISCYEAR] is a VARCHAR column do

    [S2].[FISCYEAR] = '2007'

    but if it is numeric do like so

    [S2].[FISCYEAR] = 2007

    4) You start in your join with 18 Mill rows then reduces to 1.5 million and then down further. The trick I would like to see is to get something less than 18 Mill in the first join step, doing so might drop the time it takes.

  • And what about replacing IN by CTE or temporary table?

    for example:

    WITH CTE_Code(Code) AS

    (

    SELECT 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121

    UNION ALL

    SELECT 66

    UNION ALL

    SELECT 34

    UNION ALL

    SELECT 36

    UNION ALL

    SELECT 37

    UNION ALL

    SELECT 35

    UNION ALL

    SELECT 33

    UNION ALL

    SELECT 38

    UNION ALL

    SELECT 39

    UNION ALL

    SELECT 112

    UNION ALL

    SELECT 121

    )

    SELECT ... with joining D1 and CTE_Code?

  • Sorry for mistake, it should be:

    WITH CTE_Code(Code) AS

    (

    SELECT 21

    UNION ALL

    SELECT 66

    UNION ALL

    SELECT 34

    UNION ALL

    SELECT 36

    UNION ALL

    SELECT 37

    UNION ALL

    SELECT 35

    UNION ALL

    SELECT 33

    UNION ALL

    SELECT 38

    UNION ALL

    SELECT 39

    UNION ALL

    SELECT 112

    UNION ALL

    SELECT 121

    )

    SELECT ... with joining D1 and CTE_Code

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply