请教一个查询优化的问题。

Question

试下把
C_id in (1)
改成
C_id =1如果保证
id in (select ...)
中的子查询之多返回一个，改成
id = (select ...)

Answer 1

--先试下这个SELECT A.X 
INTO #
FROM A,B,C
WHERE   A.ID=B.A_ID
AND B.B_id =C.B_ID
AND C.ID IN (1)SELECT COUNT(*) FROM # WHERE dbo.IsMatchByTime(X,'pattern')=1DROP TABLE #

Answer 2

--TRY
SELECT COUNT(*) FROM (SELECT A.X 
FROM A,B,C
WHERE      A.ID=B.A_ID
    AND B.B_id =C.B_ID
    AND C.ID =1) T WHERE  dbo.IsMatchByTime(X,'pattern')=1
--在A.ID ,B.A_ID,B.B_id,C.B_ID上建立索引

Answer 3

回Yang,肯定是有多个我才会用in的

Answer 4

回复 qianjin036a:
我也看过执行计划，是不是就是你说的showplan_all？现在确实应该是对每一行记录都执行了我这个函数。
他的执行方法是先将in谓词的表做inner join,然后再cluster index scan然后再执行filter（也就是调用我的函数），我现在的问题是它为什么要执行这个函数，那不摆明做无用功吗，我如何能够使他在前面条件不满足的时候就不去做后面的多余判断了

Answer 5

那这样试试:
;with cit as(
select X from A where id in (select A_id from B where B_id in(select B_id from C where C_id=1))
)
select count(*) from cit where dbo.IsMatchByTime(X,'pattern')=1 

Answer 6

试过5楼给出的sql没有?理论上说应该不会出现那种状况, 我在2005上是没问题的, LOB的读取很正常。

Answer 7

请问楼上这个是什么意思，建立临时表的语句吗？这个表cit会自动释放吗？
我用的是.net的sqlCommand来执行
由于持久层不是我写的，因此必须要找到一个只需要一条查询语句就可以返回结果的方法。

Answer 8

回qianjin036a ，不行，还是跑去读取lob的值了。回加内特，5楼的方法还是不行现在可行的方法就只有临时表。但是请教各位为什么会这样，我前面的限制条件已经能确定了啊，为什么它还要进行后面的比较？由于我这个函数使用了正则表达式，理论上说速度肯定是会有影响。所以我不能让他每次都执行这个函数，而且事实上前面的条件已经限制住了，它为什么还要进行后面的判断？

Answer 9

with是用来创建一个公用表表达式,可作为子句加入select语句中,不会产生临时表.
与5楼类似.你的函数是否能重写?如果能重写,试试对函数进行优化.

Answer 10

在函数中用正则表达式去匹配 NTEXT 列的值,看一下就感到是件很可怕的事情...

Answer 11

1）select * from productuseinfo where distributionid in (select distributionid from distribution where productid in(select productid from product where catelogid in (1))) and dbo.IsMatchByTime(usetrackwithtime,'','2001-08-01 00:00:00','2008-08-04 00:00:00' )=12）  |--Hash Match(Inner Join, HASH:([cia].[dbo].[Distribution].[DistributionID])=([cia].[dbo].[ProductUseInfo].[DistributionID]))3）       |--Hash Match(Inner Join, HASH:([cia].[dbo].[Product].[ProductID])=([cia].[dbo].[Distribution].[ProductID]))4）       |    |--Clustered Index Scan(OBJECT:([cia].[dbo].[Product].[PK_Product]), WHERE:([cia].[dbo].[Product].[CatelogID]=(1)))5）       |    |--Clustered Index Scan(OBJECT:([cia].[dbo].[Distribution].[PK_Distribution]))6）       |--Filter(WHERE:([cia].[dbo].[IsMatchByTime]([Expr1009],N'','2001-08-01 00:00:00.000','2008-08-04 00:00:00.000')=(1)))7）            |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(nvarchar(4000),[cia].[dbo].[ProductUseInfo].[UseTrackWithTime],0)))8）                 |--Clustered Index Scan(OBJECT:([cia].[dbo].[ProductUseInfo].[PK_ProductUseInfo]))
以上是执行计划，我的函数是dbo.IsMatchByTime(usetrackwithtime,'','2001-08-01 00:00:00','2008-08-04 00:00:00' )
谢谢各位了同时再问下上面我说的问题：（但是请教各位为什么会这样，我前面的限制条件已经能确定了啊，为什么它还要进行后面的比较？由于我这个函数使用了正则表达式，理论上说速度肯定是会有影响。所以我不能让他每次都执行这个函数，而且事实上前面的条件已经限制住了，它为什么还要进行后面的判断？）
他为什么要进行后面的调用函数来判断？我前面不是已经限制住了吗？

Answer 12

加内特老大，跪谢了，你能给我说下具体是怎么回事吗？
还有我也是KG蜜啊，看在KG份上，帮我义不容辞啊～～～

Answer 13

最顶上一层是代表最后执行吗？
我应该怎么建立索引？这些字段都是int型

Answer 14

--TRY:SET NOCOUNT ON 
GO
CREATE INDEX IX_CtID_PID ON Product(catelogid,productid)
GO
CREATE INDEX IX_PID_DID ON distribution(productid,distributionid)
GO
CREATE INDEX IX_DID ON productuseinfo(distributionid)
GO
SET STATISTICS IO ON 
SET STATISTICS PROFILE ON 
GOSELECT COUNT(*)
FROM   productuseinfo a WITH(INDEX=IX_DID) 
INNER JOIN distributionid b
ON a.distributionid=b.distributionid
INNER JOIN Product c
ON b.productid=c.productid
WHERE  catelogid =1
AND dbo.IsMatchByTime(usetrackwithtime,'','2001-08-01 00:00:00','2008-08-04 00:00:00' )=1 --看结果怎么样，再把执行计划帖上来。

Answer 15

1）SELECT COUNT(*)  FROM   productuseinfo a WITH(INDEX=IX_DID)           INNER JOIN distribution b              ON a.distributionid=b.distributionid          INNER JOIN Product c              ON b.productid=c.productid  WHERE  catelogid =1      AND dbo.IsMatchByTime(usetrackwithtime,'','2001-08-01 00:00:00','2008-08-04 00:00:00' )=12）  |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[globalagg1009],0)))3）       |--Stream Aggregate(DEFINE:([globalagg1009]=SUM([partialagg1008])))
4）            |--Parallelism(Gather Streams)5）                 |--Stream Aggregate(DEFINE:([partialagg1008]=Count(*)))6）                      |--Filter(WHERE:([cia].[dbo].[IsMatchByTime](CONVERT_IMPLICIT(nvarchar(4000),[cia].[dbo].[ProductUseInfo].[UseTrackWithTime] as [a].[UseTrackWithTime],0),N'','2001-08-01 00:00:00.000','2008-08-04 00:00:00.000')=(1)))7）                           |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[UserID], [a].[DistributionID], [Expr1011]) WITH UNORDERED PREFETCH)8)                                |--Nested Loops(Inner Join, OUTER REFERENCES:([b].[DistributionID], [Expr1010]) WITH UNORDERED PREFETCH)9)                                |    |--Nested Loops(Inner Join, OUTER REFERENCES:([c].[ProductID]))10)                                |    |    |--Parallelism(Distribute Streams, RoundRobin Partitioning)11)                                |    |    |    |--Index Seek(OBJECT:([cia].[dbo].[Product].[IX_CtID_PID] AS [c]), SEEK:([c].[CatelogID]=(1)) ORDERED FORWARD)12)                                |    |    |--Index Seek(OBJECT:([cia].[dbo].[Distribution].[IX_Distribution] AS [b]), SEEK:([b].[ProductID]=[cia].[dbo].[Product].[ProductID] as [c].[ProductID]) ORDERED FORWARD)13)                                |    |--Index Seek(OBJECT:([cia].[dbo].[ProductUseInfo].[IX_DID] AS [a]), SEEK:([a].[DistributionID]=[cia].[dbo].[Distribution].[DistributionID] as [b].[DistributionID]) ORDERED FORWARD)14)                                |--Clustered Index Seek(OBJECT:([cia].[dbo].[ProductUseInfo].[PK_ProductUseInfo] AS [a]), SEEK:([a].[UserID]=[cia].[dbo].[ProductUseInfo].[UserID] as [a].[UserID] AND [a].[DistributionID]=[cia].[dbo].[ProductUseInfo].[DistributionID] as [a].[DistributionID]) LOOKUP ORDERED FORWARD)

Answer 16

速度快了，lob physical reads从14000多降到0了
应该是正常了。
请问老大你这是怎么弄的？这是什么原理
有什么关于数据库优化方面的书吗？
感谢啊

Answer 17

书啊?Inside SQLServer 2005系列咯，值的一看。

Answer 18

再试一下这个(把强制索引去掉)
SELECT COUNT(*)
FROM   productuseinfo a  
        INNER JOIN distributionid b
            ON a.distributionid=b.distributionid
        INNER JOIN Product c
            ON b.productid=c.productid
WHERE  catelogid =1
    AND dbo.IsMatchByTime(usetrackwithtime,'','2001-08-01 00:00:00','2008-08-04 00:00:00' )=1