额解决了。。/****** Script for SelectTopNRows command from SSMS ******/ SELECT [Id] ,[OrderNumber] ,[OrderPrice] ,[Crafts] ,[IsBite] ,[IsUrgent] FROM [DB_Test].[dbo].ProductionOrder order by case when [IsUrgent]=1 then 1 else 4 end, case when [IsBite]=1 then 2 else 4 end, case when [Crafts]!='' then 3 else 4 end
为什么效率会这么低 !!!!! ProductionOrder表数据量38W条记录,用多列排序规则,SQL执行了1900多ms, 可不可以优化一下- -!!!(386816 行受影响) SQL Server 执行时间: CPU 时间 = 1482 毫秒,占用时间 = 2005 毫秒。
3 4 5 6 7 8 9 10 11
/****** Script for SelectTopNRows command from SSMS ******/ SELECT [Id] ,[OrderNumber] ,[OrderPrice] ,[Crafts] ,[IsBite] ,[IsUrgent] FROM [DB_Test].[dbo].ProductionOrder order by case when [IsUrgent]=1 then 1 else 4 end, case when [IsBite]=1 then 2 else 4 end, case when [Crafts]!='' then 3 else 4 end (386816 行受影响) SQL Server 执行时间: CPU 时间 = 1107 毫秒,占用时间 = 1969 毫秒。
第一 尝试下构造函数 select *,unicode(isUrgent+IsBite)+case when isnull(unicode(Crafts),0)>0 then 1 else 0 end as orderPrority from production order by orderPrority
select *,isUrgent+IsBite+case when isnull(unicode(Crafts),0)>0 then 1 else 0 end as orderPrority from productionOrder order by orderPrority
要不把你order by 的三个字段建一个联合索引试试
试一下这个SELECT [Id] ,[OrderNumber] ,[OrderPrice] ,[Crafts] ,[IsBite] ,[IsUrgent] FROM [DB_Test].[dbo].ProductionOrder order by case when [IsUrgent]=1 then 1 when [IsBite]=1 then 2 when [Crafts]!='' then 3 else 4 end
SELECT [Id]
,[OrderNumber]
,[OrderPrice]
,[Crafts]
,[IsBite]
,[IsUrgent]
FROM [DB_Test].[dbo].ProductionOrder order by
case when [IsUrgent]=1 then 1 else 4 end,
case when [IsBite]=1 then 2 else 4 end,
case when [Crafts]!='' then 3 else 4 end
为什么效率会这么低 !!!!! ProductionOrder表数据量38W条记录,用多列排序规则,SQL执行了1900多ms, 可不可以优化一下- -!!!(386816 行受影响) SQL Server 执行时间:
CPU 时间 = 1482 毫秒,占用时间 = 2005 毫秒。
3
4
5
6
7
8
9
10
11
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [Id]
,[OrderNumber]
,[OrderPrice]
,[Crafts]
,[IsBite]
,[IsUrgent]
FROM [DB_Test].[dbo].ProductionOrder order by
case when [IsUrgent]=1 then 1 else 4 end,
case when [IsBite]=1 then 2 else 4 end,
case when [Crafts]!='' then 3 else 4 end
(386816 行受影响) SQL Server 执行时间:
CPU 时间 = 1107 毫秒,占用时间 = 1969 毫秒。
第一 尝试下构造函数
select *,unicode(isUrgent+IsBite)+case when isnull(unicode(Crafts),0)>0 then 1 else 0 end as orderPrority from production
order by orderPrority
select *,isUrgent+IsBite+case when isnull(unicode(Crafts),0)>0 then 1 else 0 end as orderPrority
from productionOrder
order by orderPrority
,[OrderNumber]
,[OrderPrice]
,[Crafts]
,[IsBite]
,[IsUrgent]
FROM [DB_Test].[dbo].ProductionOrder order by
case when [IsUrgent]=1 then 1
when [IsBite]=1 then 2
when [Crafts]!='' then 3
else 4
end