May 28, 2004 at 3:30 pm
There is a parameter in WHERE clause. I want to use the following result (say A) inner join another query (say B) which also has the same parameter in WHERE clause to get the query C. The query speed is slow.(we can take the parameter criteria away from where clause in A and B, and put it in C's where clause). I do not know if there is a good way to make the query as efficient as possible.
Thanks for your concern, and thank you for your any answer in advance!
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
May 28, 2004 at 4:51 pm
Ok without knowing the execution plan, the table structure, or indexes the query looks fine as is except maybe the LIKE could be your biggest slow down point.
However this is the way I would write it for readability and to collapse the code a bit. I assumed AttrID was part of the LLAttrData table but had nothing to clue me on the ValStr column comes from (Note: I will for the examples assume VarStr came from LLAttrData).
SELECT
D.DataID,
D.[Name],
L.VerNum,
L.DefID,
MAX(
CASE WHEN
(L.AttrID = 2 AND L.DefID IN (1682385, 1682453)) OR
(L.AttrID = 3 AND L.DefID IN (1682417, 1682445, 1682425))
THEN L.ValStr ELSE NULL END) AS DwgNo,
MAX(
CASE WHEN
(L.AttrID = 5 AND L.DefID = 1682385) OR
(L.AttrID = 6 AND L.DefID = 1682453) OR
(L.AttrID = 7 AND L.DefID IN (1682445, 1682425)) OR
(L.AttrID = 8 AND L.DefID = 1682417)
THEN L.ValStr ELSE NULL END) AS Sheet,
MAX(
CASE WHEN
(L.AttrID = 6 AND L.DefID = 1682417) OR
(L.AttrID = 4 AND L.DefID IN (1682385, 1682453)) OR
(L.AttrID = 5 AND L.DefID IN (1682445, 1682425))
THEN L.ValStr ELSE NULL END) AS Version,
MAX(
CASE WHEN
(L.AttrID = 7 AND L.DefID = 1682453) OR
(L.AttrID = 8 AND L.DefID IN (1682385, 1682445, 1682425)) OR
(L.AttrID = 11 AND L.DefID = 1682417)
THEN L.ValStr ELSE NULL END) AS Title
FROM
dbo.DTree D
INNER JOIN
dbo.LLAttrData L
ON
D.VersionNum = L.VerNum AND
D.DataID = L.[ID]
WHERE
D.[Name] LIKE '%parametr%' AND
L.DefID IN (1682417, 1682385, 1682445, 1682425, 1682453)
GROUP BY
D.DataID,
D.[Name],
L.VerNum,
L.DefID
But there are alternate ways always to write things.
You might try adding to the where clase
AND L.AttrID IN (2,3,4,5,6,7,8,11)
but if any items in DTree don't have an item in LLAttrData at all then they wouldn't appear.
Then you might also try using a subquery or two to pre handle each table abit and join the results together.
Maybe it would make since to replace
FROM
dbo.DTree D
INNER JOIN
dbo.LLAttrData L
ON
D.VersionNum = L.VerNum AND
D.DataID = L.[ID]
WHERE
D.[Name] LIKE '%parametr%' AND
L.DefID IN (1682417, 1682385, 1682445, 1682425, 1682453)
with something like this
FROM
dbo.DTree D
INNER JOIN
(
SELECT L1.AttrID, L1.DefID, L1.VerNum, L1.[ID], L1.ValStr FROM dbo.LLAttrData L1
WHERE
L1.DefID IN (1682417, 1682385, 1682445, 1682425, 1682453) AND
L1.AttrID IN (2,3,4,5,6,7,8,11)
) L
ON
D.VersionNum = L.VerNum AND
D.DataID = L.[ID]
WHERE
D.[Name] LIKE '%parametr%'
Or the inner join could have the casess and the group bys to narrow down before join if made since.
Or maybe
FROM
(SELECT D1.VersionNum, D1.[Name], D1.DataID FROM dbo.DTree D1 WHERE D1.[Name] LIKE '%parametr%') D
INNER JOIN
dbo.LLAttrData L
ON
D.VersionNum = L.VerNum AND
D.DataID = L.[ID]
WHERE
L.DefID IN (1682417, 1682385, 1682445, 1682425, 1682453)
There are many many ways to try to optimize the query itself. I need to know lots more details such as counts in each table, counts if applying the base query stuff to each table as an individual query to know the impact and speed, execution plans on those might help, more info is really needed to know what may work best.
May 31, 2004 at 8:31 am
Thank you very much! this is my first time to post a question and English is my second
language, so I can not describe my questions clearly. Sorry about that.
My co-worker want to create two basic queries A and B (I will show B later). 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) .
I tested some ways you provided, and it is faster 10 seconds than before.
Query A (viewLLAttr_by_version_01), I have posted last Fri. the followings are B and C:
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, 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 tell 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 6:56 am
Try this for Query C
SELECT viewLLAttr_by_version_a.DataID,
oQ.[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
(SELECT iQ.[Name], iQ.DataID, iQ.MaxVersionNum FROM viewDTree_MaxVersion_01 iQ WHERE iQ.[Name] LIKE '%parameter%') oQ
ON
viewLLAttr_by_version_a.DataID = oQ.DataID
AND
viewLLAttr_by_version_a.VerNum = oQ.MaxOfVersionNum
The reason I am doing the subquery around viewDTree_MaxVersion_01 is to narrow it down before the join. By doing this few rows are involved in that set for the overall join.
When you do the other way you actually join all the tables from both views based on the ON condition and then it searches for the [Name] like value.
Also LIKE with '%xxxx%' is extrememly poor in performance. If you always have the begining of the string do change to LIKE 'xxxx%' instead, if not it is okay.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply