How to speed up the following query in SQL Server 7

Question

Post reply

How to speed up the following query in SQL Server 7

lily li

SSC Enthusiast

Points: 116
More actions
June 1, 2004 at 6:18 am

#108220

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.

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

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

Raino SSChasing Mays Points: 648 More actions · Answer 1

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

lily li SSC Enthusiast Points: 116 More actions · Answer 2

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

Thanks again!

fhanlon SSChampion Points: 13131 More actions · Answer 3

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

lily li SSC Enthusiast Points: 116 More actions · Answer 4

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