Iddc 优化

来自ling
跳转至： 导航， 搜索

张权

CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T405PrjFlowItemHandleRoleExt] (
  [prjId] ASC
)

CREATE NONCLUSTERED INDEX [idx_chkNodeId]
ON [dbo].[T405PrjFlowItemHandleRoleExt] (
  [chkNodeId] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T405PrjFlowItemHandle] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T405PrjFlowItemHandle] (
  [itemId] ASC
)
CREATE NONCLUSTERED INDEX [idx_enabled]
ON [dbo].[T405PrjFlowItemHandle] (
  [enabled] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T502ProjFlowItemProgressExt] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T502ProjFlowItemProgressExt] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T502ProjFlowItemProgressExt] (
  [sflowVerCode] ASC
)

CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T502ProjFlowItemProgressExt] (
  [bizflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_nodeId]
ON [dbo].[T502ProjFlowItemProgressExt] (
  [nodeId] ASC
)
CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T502ProjFlowItemProgressExt] (
  [itemId] ASC
)

CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T701Fqinst] (
  [prjId] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T404PrjFlowItemSingle] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T404PrjFlowItemSingle] (
  [itemId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T404PrjFlowItemSingle] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T404PrjFlowItemSingle] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T404PrjFlowItemSingle] (
  [bizflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_nodeId]
ON [dbo].[T404PrjFlowItemSingle] (
  [nodeId] ASC
)

CREATE NONCLUSTERED INDEX [idx_singleId]
ON [dbo].[T404PrjFlowItemSingle] (
  [singleId] ASC
)

CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T402PrjFlowNodeWeight] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T402PrjFlowNodeWeight] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T402PrjFlowNodeWeight] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T402PrjFlowNodeWeight] (
  [bizflowVerCode] ASC
)

CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T405PrjFlowItemHandle] (
  [itemId] ASC
)
CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T405PrjFlowItemHandle] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_enabled]
ON [dbo].[T405PrjFlowItemHandle] (
  [enabled] ASC
)
CREATE NONCLUSTERED INDEX [idx_singleId]
ON [dbo].[T405PrjFlowItemHandle] (
  [singleId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sreviewYear]
ON [dbo].[T405PrjFlowItemHandle] (
  [reviewYear] ASC
)

CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [bizflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_nodeId]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [nodeId] ASC
)
CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [itemId] ASC
)
CREATE NONCLUSTERED INDEX [idx_chkflowVerCode]
ON [dbo].[T405PrjFlowItemHandleExt] (
  [chkflowVerCode] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [bizflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_nodeId]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [nodeId] ASC
)
CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [itemId] ASC
)
CREATE NONCLUSTERED INDEX [idx_chkflowVerCode]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [chkflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_roleid]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [roleid] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_staffCode]
ON [dbo].[T405PrjFlowItemHandleFilter] (
  [staffCode] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T406BizItemOperRole] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T406BizItemOperRole] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T406BizItemOperRole] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T406BizItemOperRole] (
  [bizflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_nodeId]
ON [dbo].[T406BizItemOperRole] (
  [nodeId] ASC
)
CREATE NONCLUSTERED INDEX [idx_itemId]
ON [dbo].[T406BizItemOperRole] (
  [itemId] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T503ProjectSingle] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_singleId]
ON [dbo].[T503ProjectSingle] (
  [singleId] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T503ProjectSingleReviewYear] (
  [prjId] ASC
)
CREATE NONCLUSTERED INDEX [idx_reviewYear]
ON [dbo].[T503ProjectSingleReviewYear] (
  [reviewYear] ASC
)
CREATE NONCLUSTERED INDEX [idx_singleId]
ON [dbo].[T503ProjectSingleReviewYear] (
  [singleId] ASC
)


CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[TD01OpenCodeBase] (
  [prjId] ASC
)

CREATE NONCLUSTERED INDEX [idx_staffCode]
ON [dbo].[T503ProjectSingleAssignStaff] (
  [staffCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T503ProjectSingleAssignStaff] (
  [prjId] ASC
)


CREATE NONCLUSTERED INDEX [idx_projectTeamId]
ON [dbo].[T503ProjectTeamAssignStaff] (
  [projectTeamId] ASC
)
CREATE NONCLUSTERED INDEX [idx_staffCode]
ON [dbo].[T503ProjectTeamAssignStaff] (
  [staffCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_prjId]
ON [dbo].[T503ProjectTeamAssignStaff] (
  [prjId] ASC
)




CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T401PrjFlow] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T401PrjFlow] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T401PrjFlow] (
  [bizflowVerCode] ASC
)

CREATE NONCLUSTERED INDEX [idx_sflowId]
ON [dbo].[T201StandFlow] (
  [sflowId] ASC
)
CREATE NONCLUSTERED INDEX [idx_sflowVerCode]
ON [dbo].[T201StandFlow] (
  [sflowVerCode] ASC
)
CREATE NONCLUSTERED INDEX [idx_bizflowVerCode]
ON [dbo].[T201StandFlow] (
  [bizflowVerCode] ASC
)

==

SELECT ROW_NUMBER() OVER (ORDER BY RAND()) AS PAGE_ROW_NUMBER, *
FROM (
	SELECT client.*
	FROM TA01Client client, (
			SELECT a.clientId
			FROM (
				SELECT DISTINCT a.createFisY AS createFisY, a.clientId
				FROM T501ProjectBiz a
					LEFT JOIN T503ProjectTeamAssignStaff b ON a.prjId = b.prjId
				WHERE 1 = 1
					AND b.staffCode = '122664'
			) a, (
					SELECT clientId, MAX(createFisY) AS createFisY
					FROM T501ProjectBiz
					WHERE 1 = 1
					GROUP BY clientId
				) b
			WHERE a.clientId = b.clientId
				AND a.createFisY = b.createFisY
			UNION
			SELECT a.clientId
			FROM (
				SELECT DISTINCT a.createFisY AS createFisY, b.clientId
				FROM T501ProjectBiz a
					LEFT JOIN T503ProjectSingle b ON a.prjId = b.prjId
					LEFT JOIN T503ProjectSingleAssignStaff c ON a.prjId = c.prjId
				WHERE b.singleId = c.singleId
					AND c.staffCode = '122664'
			) a, (
					SELECT b.prjId, b.createFisY, c.clientId
					FROM (
						SELECT clientId, MAX(createFisY) AS createFisY
						FROM T501ProjectBiz
						WHERE 1 = 1
						GROUP BY clientId
					) a, T501ProjectBiz b, T503ProjectSingle c
					WHERE (a.clientId = b.clientId
						AND a.createFisY = b.createFisY
						AND b.prjId = c.prjId)
				) b
			WHERE a.clientId = b.clientId
				AND a.createFisY = b.createFisY
			UNION
			SELECT a.clientId
			FROM (
				SELECT DISTINCT a.createFisY AS createFisY, c.clientId
				FROM T501ProjectBiz a
					LEFT JOIN T503ProjectTeamAssignStaff b ON a.prjId = b.prjId
					LEFT JOIN T503ProjectSingle c ON a.prjId = c.prjId
					LEFT JOIN T503ProjectSingleAssignStaff d ON d.prjId = a.prjId
				WHERE c.singleId = d.singleId
					AND b.staffCode = '122664'
			) a, (
					SELECT b.prjId, b.createFisY, c.clientId
					FROM (
						SELECT clientId, MAX(createFisY) AS createFisY
						FROM T501ProjectBiz
						WHERE 1 = 1
						GROUP BY clientId
					) a, T501ProjectBiz b, T503ProjectSingle c
					WHERE (a.clientId = b.clientId
						AND a.createFisY = b.createFisY
						AND b.prjId = c.prjId)
				) b
			WHERE a.clientId = b.clientId
				AND a.createFisY = b.createFisY
		) t
	WHERE client.id = t.clientId
) PAGE_TABLE_ALIAS

==

SELECT t6.itemId, t6.sortFlag, t6.refId6, t6.itemType, t6.isMustOutCome
	, t6.ssCode6, t6.sectionType, t6.itemProperty, t6.blankRow6, t6.fontSize6
	, t6.fontWeight6, t6.fontFamily6, t6.fontColor6, t6.chapterHead, t6.itemContent
	, t6.tmplContent, t6.richContent, t6.comments, t6.dataRef6, t7.replaceType
	, t7.charSort, t7.characters, t7.fontSize AS fontSize7, t7.fontWeight AS fontWeight7, t7.fontFamily AS fontFamily7
	, t7.fontColor AS fontColor7, t7.style, t8.wordSort, t8.markedWords
FROM (
	SELECT t4.prjId, t4.ontTmplId AS ontTmplId6, t4.ontVerCode AS ontVerCode6, t4.refId AS refId6, t4.itemId
		, t4.richContent, t4.comments, t4.tmplContent, t4.sortFlag, t4.itemType
		, t4.isMustOutCome, t4.ssCode AS ssCode6, t4.sectionType, t4.itemProperty, t4.blankRow AS blankRow6
		, t4.fontSize AS fontSize6, t4.fontWeight AS fontWeight6, t4.fontFamily AS fontFamily6, t4.fontColor AS fontColor6, t4.chapterHead
		, t4.itemContent, t4.dataRef AS dataRef6
	FROM T705ElInstCommonItem t4
	WHERE t4.prjId = ?
	UNION
	SELECT t5.prjId, t5.ontTmplId AS ontTmplId6, t5.ontVerCode AS ontVerCode6, t5.refId AS refId6, t5.itemId
		, t5.richContent, t5.comments, t5.tmplContent, t5.sortFlag, t5.itemType
		, t5.isMustOutCome, t5.ssCode AS ssCode6, t5.sectionType, t5.itemProperty, t5.blankRow AS blankRow6
		, t5.fontSize AS fontSize6, t5.fontWeight AS fontWeight6, t5.fontFamily AS fontFamily6, t5.fontColor AS fontColor6, t5.chapterHead
		, t5.itemContent, t5.dataRef AS dataRef6
	FROM T705ElInstItem t5
	WHERE t5.prjId = ?
		AND t5.ssCode = ?
) t6
	LEFT JOIN T705InstConItemSpecialChar t7
	ON (t6.prjId = t7.prjId
		AND t6.ontTmplId6 = t7.ontTmplId
		AND t6.ontVerCode6 = t7.ontVerCode
		AND t6.itemId = t7.itemId)
	LEFT JOIN T705InstConItemMarkedWords t8
	ON (t6.prjId = t8.prjId
		AND t6.ontTmplId6 = t8.ontTmplId
		AND t6.ontVerCode6 = t8.ontVerCode
		AND t6.itemId = t8.itemId)
WHERE (t6.prjId = ?
	AND t6.ontTmplId6 = ?
	AND t6.ontVerCode6 = ?)
ORDER BY t6.sortFlag ASC

==

SELECT t6.itemId, t6.sortFlag, t6.refId6, t6.itemType, t6.isMustOutCome
	, t6.ssCode6, t6.sectionType, t6.itemProperty, t6.blankRow6, t6.fontSize6
	, t6.fontWeight6, t6.fontFamily6, t6.fontColor6, t6.chapterHead, t6.itemContent
	, t6.tmplContent, t6.richContent, t6.comments, t6.dataRef6, t7.replaceType
	, t7.charSort, t7.characters, t7.fontSize AS fontSize7, t7.fontWeight AS fontWeight7, t7.fontFamily AS fontFamily7
	, t7.fontColor AS fontColor7, t7.style, t8.wordSort, t8.markedWords
FROM (
	SELECT t4.prjId, t4.ontTmplId AS ontTmplId6, t4.ontVerCode AS ontVerCode6, t4.refId AS refId6, t4.itemId
		, t4.richContent, t4.comments, t4.tmplContent, t4.sortFlag, t4.itemType
		, t4.isMustOutCome, t4.ssCode AS ssCode6, t4.sectionType, t4.itemProperty, t4.blankRow AS blankRow6
		, t4.fontSize AS fontSize6, t4.fontWeight AS fontWeight6, t4.fontFamily AS fontFamily6, t4.fontColor AS fontColor6, t4.chapterHead
		, t4.itemContent, t4.dataRef AS dataRef6
	FROM T705ElInstCommonItem t4
	WHERE t4.prjId = 1
	UNION
	SELECT t5.prjId, t5.ontTmplId AS ontTmplId6, t5.ontVerCode AS ontVerCode6, t5.refId AS refId6, t5.itemId
		, t5.richContent, t5.comments, t5.tmplContent, t5.sortFlag, t5.itemType
		, t5.isMustOutCome, t5.ssCode AS ssCode6, t5.sectionType, t5.itemProperty, t5.blankRow AS blankRow6
		, t5.fontSize AS fontSize6, t5.fontWeight AS fontWeight6, t5.fontFamily AS fontFamily6, t5.fontColor AS fontColor6, t5.chapterHead
		, t5.itemContent, t5.dataRef AS dataRef6
	FROM T705ElInstItem t5
	WHERE t5.prjId = 1
		AND t5.ssCode = 2
) t6
	LEFT JOIN T705InstConItemSpecialChar t7
	ON (t6.prjId = t7.prjId
		AND t6.ontTmplId6 = t7.ontTmplId
		AND t6.ontVerCode6 = t7.ontVerCode
		AND t6.itemId = t7.itemId)
	LEFT JOIN T705InstConItemMarkedWords t8
	ON (t6.prjId = t8.prjId
		AND t6.ontTmplId6 = t8.ontTmplId
		AND t6.ontVerCode6 = t8.ontVerCode
		AND t6.itemId = t8.itemId)
WHERE (t6.prjId = 1
	AND t6.ontTmplId6 = 3
	AND t6.ontVerCode6 = 4)
ORDER BY t6.sortFlag ASC
取自“https://wiki.ling2.cn/index.php?title=Iddc_优化&oldid=14287”