Please help the performance issue.

[Xu, Wei]

Hi,
I have wrote the following sql sentence.Do you have comments to improve
the performance.I have created all the indexed. But it's still very
slow.Thanks
The primary key is proj_ID and Task_UID.

SELECT PR.PROJ_NAME AS PRName, PR.PROJ_ID As PRProjID, PR.TASK_UID As
PRTaskUID, 'Dev' AS GroupType,
Feat.PROJ_ID As FeatProjID, Feat.TASK_UID As FeatTaskUID, Feat.FeatureID AS
FeatureID,
dbo.CreateFIDSort(Feat.FeatureID) as FIDSort, Feat.FeatureName AS
FeatureName, Feat.Entity AS Entity,
Feat.CmtStatus AS CmtStatus, SE.AttrName AS SE, SE.ValueVariant AS SEHRID,
NetworkElement.ValueVariant As NetworkElement,
Pers.FirstName as FirstName, Pers.MiddleInitial as Middle, Pers.LastName as
LastName,
CASE WHEN Priority.ValueVariant Is Null Then 9999 When Priority.ValueVariant
= 0 Then 9999
Else Priority.ValueVariant END AS Priority, 'Doc' AS DocType, Doc.PROJ_ID AS
DocProjID, Doc.TASK_UID AS DocTaskUID,
Doc.ID_Code AS DocNum, Doc.Entity As DocEnt, DocName.Task_Name AS DocName,
DocNotes.Task_RTF_Notes AS DocNotes,
DocDetails.Proj_ID AS DateProjID, DocDetails.Task_UID AS DateTaskUID,
DocDetails.Task_Type
AS DetailTaskType, DocDetDates.TASK_FINISH_DATE AS CWVFinish,
DocDetDates.TASK_BASE_FINISH
AS BasFinish, DocDetDates.TASK_ACT_FINISH AS ActFinish, DocDetDur.TASK_DUR
AS TotalDur, DocDetDur.TASK_REM_DUR AS RemDur,
DocDetDates.TASK_ACT_START AS ActStart, Sortnum.DocNum As DocSortNum,
PR.PROJ_NAME AS PR1Name, 'Disp' As FeatDisp

FROM CPR_enum_ReltoProj Rel WITH (nolock)
INNER JOIN CPR_PATH ReltoFeat with (nolock) ON Rel.PROJ_ID =
ReltoFeat.PRED_PROJ_ID
AND Rel.TASK_UID = ReltoFeat.PRED_Task_UID AND Rel.PROJ_NAME LIKE 'R26.0'
AND ReltoFeat.EDGE_ID = 1
INNER JOIN CPR_TASK_FeatCmtStat Feat WITH (nolock) ON ReltoFeat.SUCC_PROJ_ID
= Feat.PROJ_ID
AND ReltoFeat.SUCC_Task_UID = Feat.TASK_UID AND Feat.CmtStatus <> 'Concept'
AND Feat.CmtStatus <> 'Identified'
AND Feat.CmtStatus Is Not Null
LEFT JOIN ( CPR_PATH FeattoPR WITH (nolock)
INNER JOIN CPR_ENUM_PRtoProj PR WITH (nolock)
ON FeattoPR.PRED_PROJ_ID = PR.PROJ_ID AND FeattoPR.PRED_TASK_UID =
PR.TASK_UID )
ON Feat.PROJ_ID = FeattoPR.SUCC_PROJ_ID AND Feat.TASK_UID =
FeattoPR.SUCC_TASK_UID AND FeattoPR.EDGE_ID = 1
LEFT JOIN CPR_ContainerAttr SE WITH (nolock) ON Feat.PROJ_ID = SE.PROJ_ID
AND Feat.TASK_UID = SE.TASK_UID
AND SE.AttrName in ('SEM','SEA')
LEFT JOIN CPR_Person Pers WITH (nolock) ON Pers.HRID = SE.ValueVariant
LEFT JOIN CPR_ContainerAttr NetworkElement WITH (nolock) ON Feat.PROJ_ID =
NetworkElement.PROJ_ID
AND Feat.TASK_UID = NetworkElement.TASK_UID AND NetworkElement.AttrName =
'NetElem'
LEFT JOIN CPR_ContainerAttr Priority WITH (nolock) ON Feat.PROJ_ID =
Priority.PROJ_ID
AND Feat.TASK_UID = Priority.TASK_UID AND Priority.AttrName = 'FPA'
LEFT JOIN ( CPR_PATH FeattoDoc WITH (nolock)
INNER JOIN CPR_ENUM_AllDocs Doc WITH (nolock)
ON FeattoDoc.SUCC_PROJ_ID = Doc.PROJ_ID AND FeattoDoc.SUCC_TASK_UID =
Doc.TASK_UID AND FeattoDoc.EDGE_ID = 1
AND Doc.ID_Code NOT LIKE '[<]%' AND Doc.Entity in
('FDD','SRD','SRAD','FFRD','VRAD')
LEFT JOIN CPR_DOCSORTNUM Sortnum WITH (nolock) ON Doc.Entity =
Sortnum.DocEnt
INNER JOIN MSP_TASKS DocName WITH (nolock) ON Doc.PROJ_ID = DocName.PROJ_ID
AND Doc.TASK_UID = DocName.TASK_UID
INNER JOIN CPR_ENUM_Task_RTF_Notes DocNotes WITH (nolock) ON Doc.PROJ_ID =
DocNotes.PROJ_ID
AND Doc.TASK_UID = DocNotes.TASK_UID
LEFT JOIN ( CPR_PATH DoctoDet WITH (nolock)
INNER JOIN CPR_ENUM_TASK_Task_Type DocDetails WITH (nolock) ON
(DoctoDet.SUCC_PROJ_ID = DocDetails.PROJ_ID
AND DoctoDet.SUCC_TASK_UID = DocDetails.TASK_UID AND DocDetails.Task_Type
In ('WriteRev', 'RwkRFA', 'PubLive', 'Waived','Review','RFA', 'CustRev'))
LEFT JOIN MSP_TASKS DocDetDates WITH (nolock) ON
(DocDetails.PROJ_ID = DocDetDates.PROJ_ID AND DocDetails.TASK_UID =
DocDetDates.TASK_UID)
LEFT JOIN CPR_ENUM_TASK_Durations DocDetDur WITH (nolock)
ON ( DocDetails.PROJ_ID = DocDetDur.PROJ_ID AND DocDetails.TASK_UID =
DocDetDur.TASK_UID))
ON ( DocName.PROJ_ID = DoctoDet.PRED_PROJ_ID AND DocName.TASK_UID =
DoctoDet.PRED_TASK_UID AND DoctoDet.EDGE_ID = 1))
ON (Feat.PROJ_ID=FeattoDoc.PRED_PROJ_ID AND
Feat.TASK_UID=FeattoDoc.PRED_TASK_UID AND FeattoDoc.EDGE_ID = 1)
WHERE Feat.FeatureID NOT LIKE '[<]%'
ORDER BY PRName, FIDSort, FeatureID, DocSortNum, DocProjID,DocTaskUID,
DateProjID, DateTaskUID

[Tom Moreau]

You didn't post your DDL. How about using the Index Tuning Wizard?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Xu, Wei" <xu*@lucent.com> wrote in message
news:dn********@netnews.net.lucent.com...
Hi,
I have wrote the following sql sentence.Do you have comments to improve
the performance.I have created all the indexed. But it's still very
slow.Thanks
The primary key is proj_ID and Task_UID.

SELECT PR.PROJ_NAME AS PRName, PR.PROJ_ID As PRProjID, PR.TASK_UID As
PRTaskUID, 'Dev' AS GroupType,
Feat.PROJ_ID As FeatProjID, Feat.TASK_UID As FeatTaskUID, Feat.FeatureID AS
FeatureID,
dbo.CreateFIDSort(Feat.FeatureID) as FIDSort, Feat.FeatureName AS
FeatureName, Feat.Entity AS Entity,
Feat.CmtStatus AS CmtStatus, SE.AttrName AS SE, SE.ValueVariant AS SEHRID,
NetworkElement.ValueVariant As NetworkElement,
Pers.FirstName as FirstName, Pers.MiddleInitial as Middle, Pers.LastName as
LastName,
CASE WHEN Priority.ValueVariant Is Null Then 9999 When Priority.ValueVariant
= 0 Then 9999
Else Priority.ValueVariant END AS Priority, 'Doc' AS DocType, Doc.PROJ_ID AS
DocProjID, Doc.TASK_UID AS DocTaskUID,
Doc.ID_Code AS DocNum, Doc.Entity As DocEnt, DocName.Task_Name AS DocName,
DocNotes.Task_RTF_Notes AS DocNotes,
DocDetails.Proj_ID AS DateProjID, DocDetails.Task_UID AS DateTaskUID,
DocDetails.Task_Type
AS DetailTaskType, DocDetDates.TASK_FINISH_DATE AS CWVFinish,
DocDetDates.TASK_BASE_FINISH
AS BasFinish, DocDetDates.TASK_ACT_FINISH AS ActFinish, DocDetDur.TASK_DUR
AS TotalDur, DocDetDur.TASK_REM_DUR AS RemDur,
DocDetDates.TASK_ACT_START AS ActStart, Sortnum.DocNum As DocSortNum,
PR.PROJ_NAME AS PR1Name, 'Disp' As FeatDisp

FROM CPR_enum_ReltoProj Rel WITH (nolock)
INNER JOIN CPR_PATH ReltoFeat with (nolock) ON Rel.PROJ_ID =
ReltoFeat.PRED_PROJ_ID
AND Rel.TASK_UID = ReltoFeat.PRED_Task_UID AND Rel.PROJ_NAME LIKE 'R26.0'
AND ReltoFeat.EDGE_ID = 1
INNER JOIN CPR_TASK_FeatCmtStat Feat WITH (nolock) ON ReltoFeat.SUCC_PROJ_ID
= Feat.PROJ_ID
AND ReltoFeat.SUCC_Task_UID = Feat.TASK_UID AND Feat.CmtStatus <> 'Concept'
AND Feat.CmtStatus <> 'Identified'
AND Feat.CmtStatus Is Not Null
LEFT JOIN ( CPR_PATH FeattoPR WITH (nolock)
INNER JOIN CPR_ENUM_PRtoProj PR WITH (nolock)
ON FeattoPR.PRED_PROJ_ID = PR.PROJ_ID AND FeattoPR.PRED_TASK_UID =
PR.TASK_UID )
ON Feat.PROJ_ID = FeattoPR.SUCC_PROJ_ID AND Feat.TASK_UID =
FeattoPR.SUCC_TASK_UID AND FeattoPR.EDGE_ID = 1
LEFT JOIN CPR_ContainerAttr SE WITH (nolock) ON Feat.PROJ_ID = SE.PROJ_ID
AND Feat.TASK_UID = SE.TASK_UID
AND SE.AttrName in ('SEM','SEA')
LEFT JOIN CPR_Person Pers WITH (nolock) ON Pers.HRID = SE.ValueVariant
LEFT JOIN CPR_ContainerAttr NetworkElement WITH (nolock) ON Feat.PROJ_ID =
NetworkElement.PROJ_ID
AND Feat.TASK_UID = NetworkElement.TASK_UID AND NetworkElement.AttrName =
'NetElem'
LEFT JOIN CPR_ContainerAttr Priority WITH (nolock) ON Feat.PROJ_ID =
Priority.PROJ_ID
AND Feat.TASK_UID = Priority.TASK_UID AND Priority.AttrName = 'FPA'
LEFT JOIN ( CPR_PATH FeattoDoc WITH (nolock)
INNER JOIN CPR_ENUM_AllDocs Doc WITH (nolock)
ON FeattoDoc.SUCC_PROJ_ID = Doc.PROJ_ID AND FeattoDoc.SUCC_TASK_UID =
Doc.TASK_UID AND FeattoDoc.EDGE_ID = 1
AND Doc.ID_Code NOT LIKE '[<]%' AND Doc.Entity in
('FDD','SRD','SRAD','FFRD','VRAD')
LEFT JOIN CPR_DOCSORTNUM Sortnum WITH (nolock) ON Doc.Entity =
Sortnum.DocEnt
INNER JOIN MSP_TASKS DocName WITH (nolock) ON Doc.PROJ_ID = DocName.PROJ_ID
AND Doc.TASK_UID = DocName.TASK_UID
INNER JOIN CPR_ENUM_Task_RTF_Notes DocNotes WITH (nolock) ON Doc.PROJ_ID =
DocNotes.PROJ_ID
AND Doc.TASK_UID = DocNotes.TASK_UID
LEFT JOIN ( CPR_PATH DoctoDet WITH (nolock)
INNER JOIN CPR_ENUM_TASK_Task_Type DocDetails WITH (nolock) ON
(DoctoDet.SUCC_PROJ_ID = DocDetails.PROJ_ID
AND DoctoDet.SUCC_TASK_UID = DocDetails.TASK_UID AND DocDetails.Task_Type
In ('WriteRev', 'RwkRFA', 'PubLive', 'Waived','Review','RFA', 'CustRev'))
LEFT JOIN MSP_TASKS DocDetDates WITH (nolock) ON
(DocDetails.PROJ_ID = DocDetDates.PROJ_ID AND DocDetails.TASK_UID =
DocDetDates.TASK_UID)
LEFT JOIN CPR_ENUM_TASK_Durations DocDetDur WITH (nolock)
ON ( DocDetails.PROJ_ID = DocDetDur.PROJ_ID AND DocDetails.TASK_UID =
DocDetDur.TASK_UID))
ON ( DocName.PROJ_ID = DoctoDet.PRED_PROJ_ID AND DocName.TASK_UID =
DoctoDet.PRED_TASK_UID AND DoctoDet.EDGE_ID = 1))
ON (Feat.PROJ_ID=FeattoDoc.PRED_PROJ_ID AND
Feat.TASK_UID=FeattoDoc.PRED_TASK_UID AND FeattoDoc.EDGE_ID = 1)
WHERE Feat.FeatureID NOT LIKE '[<]%'
ORDER BY PRName, FIDSort, FeatureID, DocSortNum, DocProjID,DocTaskUID,
DateProjID, DateTaskUID

[Erland Sommarskog]

Xu, Wei (xu*@lucent.com) writes:

I have wrote the following sql sentence.Do you have comments to
improve the performance.I have created all the indexed. But it's still
very slow.Thanks
I am afraid that is very difficult ot give much input given just the
query text. The fact that there are indexes, does not mean that they
are ussed, because they may not be the right indexes.

Normally, I would ask you to post CREATE TABLE and CREATE INDEX statements
for the query, but this is a very complex query, and I doubt that anyone
would actually analyse it to suggest better indexes or any other tweaks.
And it does not help, having no idea what the meaning of the query is.

There is one thing, though, that I can comment about the query:
dbo.CreateFIDSort(Feat.FeatureID) as FIDSort,

I don't know how many rows your query returns, but if it returns many rows,
the call to the scalar UDF can be costly. You can always try to take it,
to see what the effect it has.

Apart from that, I can only advice you to look at the query plan and running
the query with SET STATISTICS IO ON, to get an idea of exactly what in the
query that is taking time.

It seems to me that the query plan should start with the condition
CPR_PATH.PROJ LIKE 'R26.0' (change to "=" if you can), provided that
that column is indexed. But this is only a guess on my part, that that
column is selective enough.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

how many of your tables have more then 100 rows?

which tables have more then a million rows?

[xu,wei]

Thanks so much. I use the ASP + Sql Server2000, I guess the problem caused
by so many "left join". There are a lot of data in database now. I have
created some indexes. ( I thinks there are not big problems for this
indexes.) I want create some indexes on views but after my test I found it
is same speed as create the indexes on tables. Hope you can give more help.
Thanks again.

[Erland Sommarskog]

xu,wei (xu*@lucent.com) writes:

Thanks so much. I use the ASP + Sql Server2000, I guess the problem
caused by so many "left join". There are a lot of data in database now.
I have created some indexes. ( I thinks there are not big problems for
this indexes.) I want create some indexes on views but after my test I
found it is same speed as create the indexes on tables. Hope you can
give more help.

Unfortunately I can only refer to what I said my previous post. There is a
limit of what can be done over newsgroups. Performance tuning a query given
only the query text is impossible - need to know table and indexes for
that. And for a complex query like this, I would need more than so. Even
with access to the database, it can be a difficult task.

I did mention two things you could check:
1) What happens if you exclude the call to the UDF?
2) Is there an index on CPR_PATH.PROJ?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Doug]

again, which are teh big tables????

many left joins doesn't cause performance issues.

[jsfromynr]

Hi There,
Some minor suggestions

try not to use LIKE as in this case
AND Rel.PROJ_NAME LIKE 'R26.0'
Replace it with
AND Rel.PROJ_NAME = 'R26.0'
And Try to replace <> as below
AND Feat.CmtStatus <> 'Concept'
AND Feat.CmtStatus <> 'Identified'
AND Feat.CmtStatus Is Not Null

Let us say other CMTStatus which you want to check are
'Stage1','Stage2' etc
Then You can use
Feat.CmtStatus In ('Stage1','Stage2',.....)
These are the minor suggestions without looking at the database tables
and other aspects like table size , indexes it would be difficult to
arrive at some conclusion

With Warm regards
jatinder Singh

[xu,wei]

The tables CPR_PATH,CPR_TASK_FeatCmtStat more than 100 rows.

The tables Cpr_containerattr and msp_tasks, cpdr_alldocs more than 1
million row

"Doug" <dr*********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

how many of your tables have more then 100 rows?

which tables have more then a million rows?