Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Unanswered: Query using mathematical function of values from 2 tables has a performance problem

    When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.

    Why is this happening?
    Is there a way to bypass this problem?

    Long query ( values from 2 tables ) :
    SELECT
    MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
    FROM
    DATA data,
    ATTR attribute,
    TREE_ELEMENT elm,
    TREE_ELEMENT subject
    WHERE
    data.elmId=elm.id
    AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
    AND subject.id=elm.subjectId
    AND subject.name = ‘test’


    Short query ( values from 1 table ) :
    SELECT
    MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal
    FROM
    DATA data,
    ATTR attribute,
    TREE_ELEMENT elm,
    TREE_ELEMENT subject
    WHERE
    data.elmId=elm.id
    AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 )
    AND subject.id=elm.subjectId
    AND subject.name = ‘test’


    Long query execution plan:
    Execution Tree
    --------------
    Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( ( sign ( [attribute].[keyValue]--2027587559 ) *sign ( [attribute].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
    |--Nested Loops ( Inner Join )
    |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
    | |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
    | | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
    SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
    | | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
    SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
    | |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
    |--Table Spool
    |--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
    SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )



    Short query execution plan:
    Execution Tree
    --------------
    Stream Aggregate ( DEFINE: ( [Expr1004]=MAX ( [partialagg1005] ) ) )
    |--Nested Loops ( Inner Join )
    |--Stream Aggregate ( DEFINE: ( [partialagg1005]=MAX ( ( sign ( [data].[keyValue]--2027587559 ) *sign ( [data].[keyValue]--2027587559 ) -1 ) * ( -1*[data].[val] ) ) ) )
    | |--Hash Match ( Inner Join, HASH: ( [elm].[id] ) = ( [data].[elmId] ) , RESIDUAL: ( [data].[elmId]=[elm].[id] ) )
    | |--Nested Loops ( Inner Join, OUTER REFERENCES: ( [subject].[id] ) )
    | | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_NAME_IDX] AS [subject] ) ,
    SEEK: ( [subject].[name]=’test’ ) ORDERED FORWARD )
    | | |--Index Seek ( OBJECT: ( [TREE_ELEMENT].[TREE_ELEMENT_APP_ID_IDX] AS [elm] ) ,
    SEEK: ( [elm].[subjectId]=[subject].[id] ) ORDERED FORWARD )
    | |--Clustered Index Scan ( OBJECT: ( [DATA].[PK__DATAS_SAMPL__485B9C89] AS [data] ) )
    |--Index Seek ( OBJECT: ( [ATTR].[TREE_Z_IDX] AS [attribute] ) ,
    SEEK: ( [attribute].[keyValue]=-2027587559 OR [attribute].[keyValue]=345647222 OR [attribute].[keyValue]=1569146115 OR [attribute].[keyValue]=1569153803 ) ORDERED FORWARD )

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just a quick comment:
    I don't actually see a(ny) join(s) - instead I see you using WHERE clauses; which is not advised!
    The execution plan is assuming INNER JOINS which might not be what you want either.
    George
    Home | Blog

Posting Permissions

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