June 1, 2004 at 6:18 am
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.
June 1, 2004 at 7:33 am
You can start by building your WHERE clauses into the JOINS, should make a significant improvement.
June 1, 2004 at 9:11 am
Thank you for your replying. I am a newer in SQL Server. Could you please give me more details about it?
Thanks again!
June 1, 2004 at 10:04 am
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
June 1, 2004 at 10:22 am
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