How to speed up the following query in SQL Server 7

  • My co-worker asked me to create two basic queries A and B. He doesn't want

    to put any parameter in these two basic queries, and want to put it in query C. He wants to

    create two views instead of A and B, and get C from A and B (I tested this way, it is so slow.

    It took 20 seconds for 5 rows. If put the parameter in A and B, the query will be faster.

    But in view, you can not use parameter. I do not know if stored procedure can speed up or not) .

    Thanks for your concern, and thank you for your any answer in advance!

    Query A (viewLLAttr_by_version_01):

    SELECT DTree.DataID, DTree.Name, LLAttrData.VerNum,

        LLAttrData.DefID, MAX(CASE WHEN ((DefID = 1682417 AND

        AttrID = 3) OR

        (DefID = 1682385 AND AttrID = 2) OR

        (DefID = 1682445 AND AttrID = 3) OR

        (DefID = 1682425 AND AttrID = 3) OR

        (DefID = 1682453 AND AttrID = 2)) THEN ValStr ELSE NULL

        END) AS DwgNo, MAX(CASE WHEN ((DefID = 1682417 AND

        AttrID = 8) OR

        (DefID = 1682385 AND AttrID = 5) OR

        (DefID = 1682445 AND AttrID = 7) OR

        (DefID = 1682425 AND AttrID = 7) OR

        (DefID = 1682453 AND AttrID = 6)) THEN ValStr ELSE NULL

        END) AS Sheet, MAX(CASE WHEN ((DefID = 1682417 AND

        AttrID = 6) OR

        (DefID = 1682385 AND AttrID = 4) OR

        (DefID = 1682445 AND AttrID = 5) OR

        (DefID = 1682425 AND AttrID = 5) OR

        (DefID = 1682453 AND AttrID = 4)) THEN ValStr ELSE NULL

        END) AS Version, MAX(CASE WHEN ((DefID = 1682417 AND

        AttrID = 11) OR

        (DefID = 1682385 AND AttrID = 8) OR

        (DefID = 1682445 AND AttrID = 8) OR

        (DefID = 1682425 AND AttrID = 8) OR

        (DefID = 1682453 AND AttrID = 7)) THEN ValStr ELSE NULL

        END) AS Title

    FROM DTree INNER JOIN

        LLAttrData ON (DTree.VersionNum = LLAttrData.VerNum) AND

        (DTree.DataID = LLAttrData.ID)

    WHERE (((DTree.Name) LIKE '%parametr%') AND ((LLAttrData.DefID)

        = 1682417 OR

        (LLAttrData.DefID) = 1682385 OR

        (LLAttrData.DefID) = 1682445 OR

        (LLAttrData.DefID) = 1682425 OR

        (LLAttrData.DefID) = 1682453))

    GROUP BY DTree.DataID, DTree.Name, LLAttrData.VerNum,

        LLAttrData.DefID

     

    Query B(viewDTree_MaxVersion_01):

    SELECT DataID, Name, MAX(VersionNum)

        AS MaxOfVersionNum

    FROM DTree

    WHERE (Name LIKE '%parameter%')//want to put in C.

    GROUP BY DataID, Name

    If there is no where clause (parameter) in A and B , B will take round 28 seconds for 116660 rows. And A will take round 13 seconds for 22538 rows.

    Query C:

    SELECT viewLLAttr_by_version_a.DataID,

        viewDTree_MaxVersion_01.Name,

        viewLLAttr_by_version_a.DefID,

        viewLLAttr_by_version_a.DwgNo,

        viewLLAttr_by_version_a.Sheet,

        viewLLAttr_by_version_a.Version,

        viewLLAttr_by_version_a.Title

    FROM viewLLAttr_by_version_a INNER JOIN

        viewDTree_MaxVersion_01 ON

        viewLLAttr_by_version_a.DataID = viewDTree_MaxVersion_01.DataID

         AND

        viewLLAttr_by_version_a.VerNum = viewDTree_MaxVersion_01.MaxOfVersionNum

    WHERE (viewDTree_MaxVersion_01.Name LIKE '%parameter%')

    C will take round 20 seconds for 5 rows.

    Now, I will you the table structure.

    DTree:

    DataID Not Null, PK(no), FK(no), it is an index but not unique

    name   Not Null, PK(yes), FK(no), it combine with other attributes will be the unique index.

    VersionNum Null, PK(no), FK(no).

    LLAttrData:

    AttrID Not Null, PK(no), FK(no).

    DefID  Not Null, PK(no), FK(no), combined with another attribute is the unique index.

    ID     Not Null, PK(no), FK(no).

    ValStr Null,     PK(no), FK(no).

    VerNum Not Null, PK(no), FK(no), VerNum, AttrID and ID will be the unique index.

     

     

  • You can start by building your WHERE clauses into the JOINS, should make a significant improvement.

  • Thank you for your replying. I am a newer in SQL Server. Could you please give me more details about it?

    Thanks again!

  • Lily I noticed that you WHERE clause being:

    Name LIKE '%parameter%'

    will not use the index becasue of the % in front of the parameter.  If you code Name LIKE 'parameter%'  SQL Server will use the index and therefore be much faster.  If this is what you want then of course you have no choice but are you sure you want to search for this string anywhere in the name as opposed to searching for a name that begins with this string?

     

    I am also confused.  You defined one of your tables as:

    LLAttrData:

    AttrID Not Null, PK(no), FK(no).

    DefID  Not Null, PK(no), FK(no), combined with another attribute is the unique index.

    ID     Not Null, PK(no), FK(no).

    ValStr Null,     PK(no), FK(no).

    VerNum Not Null, PK(no), FK(no), VerNum, AttrID and ID will be the unique index.

    If DefID indexed?  It should be since you use it to join with the other table.  But you say that "VerNum, AttrID and ID will be the unique index."  Then is  DefID another index?  Make sure your tables have a primary key which should be the clustered index.  I suspect "VerNum, AttrID and ID" should be the clustered index, or as you seem to be using it that way .  Or Perhaps just "VerNum, and ID" should be the clustered index as you aren't using AttrID as join criteria (not in these queries anyway) Reconsider your indexes to help speed up things.

    Francis

  • there is no primary key and foreign key in LLAttrData table, only has two indexes:

    index                 Columns indexed by                Unique

    LLAttrData_DefID      DefID 

                                 DefVerN

    LLAttrData_ID         AttrID                               yes

                                ID

                                VerNum

Viewing 5 posts - 1 through 4 (of 4 total)

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