DECLARE @topCount INT SET @topCount = 10 select top (@topCount) * from (SELECT * From (SELECT * FROM View_OrderWithAddressAndCustomer WITH (NOLOCK)) AS tbl WHERE EXISTS( SELECT TOP 1 1 FROM dbo.ConvertTbl('54627,54626,54625,54623,54622,54621,54620,54619,54618,54617,54616,54614', ',') as tmp WHERE tbl.PlatformOrderID = tmp.[key] OR tbl.ID = tmp.[key] OR tbl.WaybillNumber = tmp.[key] OR tbl.Email=tmp.[key]) ) as TempTable order by WaybillNumber ASC
说明: 按照"54627,54626,54625,54623,54622,54621,54620,54619,54618,54617,54616,54614" 查询对应ID(包括了三种情况的ID)的订单
dbo.ConvertTbl是一个函数,如下:
SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE FUNCTION [dbo].[ConvertTbl] ( @string as nvarchar(max),@separator varchar(10) ) RETURNS @tmp_tbl_keys TABLE ( [key] nvarchar(50) ) AS BEGIN while(charindex(@separator,@string)<>0) begin insert @tmp_tbl_keys([key]) values (substring(@string,1,charindex(@separator,@string)-1)) set @string = stuff(@string,1,charindex(@separator,@string),'') end insert @tmp_tbl_keys([key]) values (@string) RETURN END GO
要求: 当查询大量的订单时(订单号达到五百以上),查询很慢,要花上二十秒,请问可以如何优化这条SQL语句?
自定义函数能不用就不用,数据量大了,效率很成问题。
那请问有什么其他的解决方案,这个是前人写的代码,我SQL语句不是很熟.
不使用自定义函数,这个应该怎么写?求指点下.
@SimpleCoder: 把函数的结果先存到一个临时表,然后用前面那个表内连接这个临时表。
@清海扬波: 请问具体应该怎么写,麻烦了.
SELECT TOP (@topCount ) VO.* FROM View_OrderWithAddressAndCustomer VO(nolock)
INNER JOIN dbo.ConvertTbl('54627,54626,54625,54623,54622,54621,54620,54619,54618,54617,54616,54614',',') AS tmp
ON VO.PlatformOrderID = tmp.[key] OR VO.ID = tmp.[key] OR VO.WaybillNumber = tmp.[key] OR VO.Email = tmp.[key]
ORDER BY VO.WaybillNumber
--1.建议看下 dbo.ConvertTbl 这个表值函数的效率
--2.个人认为 on 后面的这些条件是不是没有建立所以,走了全表扫描?
--3.我上面都是胡说的,哈哈
你这个方法实际上和原先的效率一样,那几个条件字段都做了索引,可是还是很慢.
用xml 参数试验下