select FS_Item.ItemKey as CompentItemKey,round(RQ_ORD_QTY,0)-ISSUED_QTY as RemainQty,
FS_Item.MakeBuyCode,FS_MOLine.MOLineKey,
FS_MOLine.NeededDate,FS_MOHeader.MOHeaderKey,FS_MOLine.ItemKey
--COM_TYP as ComponentType,
--
from dbo.ORDER_MANUFACTCONFIG M (readuncommitted)
join FS_Item (readuncommitted) on M.COMP_WC1=FS_Item.ItemNumber
join FS_MOHeader (readuncommitted) on M.MO_NUMBER=FS_MOHeader.MONumber
JOIN FS_MOLine (readuncommitted) on FS_MOHeader.MOHeaderKey=FS_MOLine.MOHeaderKey
and FS_MOLine.MOLineNumber=M.LN_NO
where ORDER_TYPE='M'
AND (LN_STA='4' OR LN_STA='3')
AND RQ_ORD_QTY-ISSUED_QTY>0 and COM_TYP<>'Y' and round(RQ_ORD_QTY,0)-ISSUED_QTY>0
order by FS_MOLine.NeededDate,FS_MOHeader.MOHeaderKey,FS_MOLine.MOLineKey
这条语句执行时间超长,我把order by 排序去掉的话30秒不到就出来了,请高人指点我应该如何去优化他.
FS_Item.MakeBuyCode,FS_MOLine.MOLineKey,
FS_MOLine.NeededDate,FS_MOHeader.MOHeaderKey,FS_MOLine.ItemKey
--COM_TYP as ComponentType,
--
from dbo.ORDER_MANUFACTCONFIG M (readuncommitted)
join FS_Item (readuncommitted) on M.COMP_WC1=FS_Item.ItemNumber
join FS_MOHeader (readuncommitted) on M.MO_NUMBER=FS_MOHeader.MONumber
JOIN FS_MOLine (readuncommitted) on FS_MOHeader.MOHeaderKey=FS_MOLine.MOHeaderKey
and FS_MOLine.MOLineNumber=M.LN_NO
where ORDER_TYPE='M'
AND (LN_STA='4' OR LN_STA='3')
AND RQ_ORD_QTY-ISSUED_QTY>0 and COM_TYP<>'Y' and round(RQ_ORD_QTY,0)-ISSUED_QTY>0
order by FS_MOLine.NeededDate,FS_MOHeader.MOHeaderKey,FS_MOLine.MOLineKey
这条语句执行时间超长,我把order by 排序去掉的话30秒不到就出来了,请高人指点我应该如何去优化他.
在order by 的上键索引吧
order by 上面建索引应该可以提高点
2。将 tempdb 数据库文件的原始大小设置为一个合理大小以避免当需要更多空间时文件自动扩展。如果 tempdb 数据库扩展得过于频繁,性能会受到影响。
3。将文件增长增量百分比设置一个合理大小以避免 tempdb 数据库文件按太小的值增长。如果文件增长远小于写入 tempdb 数据库的数据量,则 tempdb 可能需要一直扩展。这将影响性能。
说明各个表的数据量没有上面的信息是没办法给意见你的
where 后面建组合索引
因为楼主没有给出表结构,没有给出表大小,没有给出索引,怎么下结论?
楼主可以实验 SET STATISTICS PROFILE ON 看看哪个步骤效率低下,对症下药。建议不要写这么长的语句,SQL2005 基于结果集的语言,不同于别的,可以改写为:select …………, from table1
join (select …… from a join b on ……) table2,(结果集2)
…………
结果集估计返回的数据最好小,这样可以按照自己的“意愿”大约猜测到会用到哪些索引,效果如何,
SELECT
SYSIDX.TableName,
SYSIDX.IDX_NAME,
TempTable.INDEX_ID,
TempTable.NAME,
SYSIDX.ROWS
FROM
(SELECT
OBJECT_name(ID)as TableName,
name as IDX_NAME,
ROWS
FROM SYS.SYSINDEXES)
SYSIDX,
(SELECT
OBJECT_NAME(IDX.OBJECT_ID) TableName ,
IDX.NAME as IDX_NAME,
IDX.INDEX_ID,
IDX.TYPE_DESC,
M.NAME,
IDX.OBJECT_ID
FROM SYS.INDEXES IDX,(SELECT
A.OBJECT_ID,
A.INDEX_ID,
A.COLUMN_ID,
B.NAME
FROM sys.index_columns A
INNER JOIN sys.columns B
ON A.OBJECT_ID=B.OBJECT_ID
AND A.COLUMN_ID=B.COLUMN_ID) M
WHERE
IDX.OBJECT_ID=M.OBJECT_ID
AND IDX.INDEX_ID=M.INDEX_ID)
TempTable
WHERE
TempTable.TableName=SYSIDX.TableName
and SYSIDX.IDX_NAME=TempTable.IDX_NAME
AND TempTable.TableName not like 'sys%'
and TempTable.INDEX_ID<>1
order by TempTable.OBJECT_ID,TempTable.INDEX_ID