求一个sql语句优化
我这里有一个sql语句 的执行,的消耗如下
cpu reads writes duration
101430087701084
很简单的更新要花1秒以上的时候,求优化
执行内容
exec SpeedSectionMileage_tbl_AddOrUpdateForXML @xml=N'<Chapter><CTR_ID>96361</CTR_ID><SpeedRange>1</SpeedRange><Mileage>1.7</Mileage></Chapter><Chapter><CTR_ID>96361</CTR_ID><SpeedRange>2</SpeedRange><Mileage>8.6</Mileage></Chapter><Chapter><CTR_ID>96361</CTR_ID><SpeedRange>3</SpeedRange><Mileage>0.3</Mileage></Chapter><Chapter><CTR_ID>96361</CTR_ID><SpeedRange>4</SpeedRange><Mileage>0</Mileage></Chapter><Chapter><CTR_ID>96361</CTR_ID><SpeedRange>5</SpeedRange><Mileage>0</Mileage></Chapter>'
CREATE PROCEDURE SpeedSectionMileage_tbl_AddOrUpdateForXML
(
@Xml XML
)
AS
BEGIN
CREATE TABLE #table
(
[CTR_ID] BigInt,
[SpeedRange] Int,
[Mileage] Decimal(8,2)
);
INSERT #table
SELECT *
FROM (
SELECT
X.C.value('CTR_ID[1]', 'BigInt') AS CTR_ID,
X.C.value('SpeedRange[1]', 'Int') AS SpeedRange,
X.C.value('Mileage[1]', 'Decimal(8,2)') AS Mileage
FROM @Xml.nodes('Chapter') AS X(C) --注意：这里的X(C)命名空间是需要的
) t;
UPDATE [SpeedSectionMileage_tbl] SET
[CTR_ID] = e.[CTR_ID]
,[SpeedRange] = e.[SpeedRange]
,[Mileage] = e.[Mileage]
FROM #table AS e
WHERE [SpeedSectionMileage_tbl].SpeedRange=e.SpeedRange and [SpeedSectionMileage_tbl].CTR_ID=e.CTR_ID;
insert into [SpeedSectionMileage_tbl]
( [CTR_ID],[SpeedRange],[Mileage] )
select [CTR_ID],[SpeedRange],[Mileage]from #table where
not exists( select * from [SpeedSectionMileage_tbl] where #table.SpeedRange=SpeedRange and #table.CTR_ID=CTR_ID )
drop table #table
END
GO
