查询处理器用尽内部资源异常出(The query processor ran out of inte

我有它运行在SQL Server 2008下面的查询，它工作得很好，如果数据很小但是当它是巨大的我收到的例外。 有没有什么办法可以优化查询

select
        distinct olu.ID
            from olu_1 (nolock) olu

            join mystat (nolock) s
                on s.stat_int = olu.stat_int

            cross apply
                dbo.GetFeeds 
                        (
                                s.stat_id,
                                olu.cha_int, 
                                olu.odr_int,  
                                olu.odr_line_id, 
                                olu.ID
                        ) channels

            join event_details (nolock) fed
                on fed.air_date = olu.intended_air_date
                and fed.cha_int = channels.cha_int
                and fed.break_code_int = olu.break_code_int

            join formats (nolock) fmt
                on fed.format_int = fmt.format_int


            where
                fed.cha_int in (125, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 35, 36, 37, 38, 39, 40, 41, 43, 117, 45, 42, 44, 47, 49, 50, 51, 46, 52, 53, 54, 55, 56, 48, 59, 60, 57, 63, 58, 62, 64, 66, 69, 68, 67, 65, 70, 73, 71, 74, 72, 75, 76, 77, 78, 79, 82, 80, 159, 160, 161, 81, 83, 84, 85, 88, 87, 86, 89, 90, 61, 91, 92, 93, 95, 96, 97, 98, 99, 100, 94, 155, 156, 157, 158, 103, 104, 102, 101, 105, 106, 107, 108, 109, 110, 119, 111, 167, 168, 169, 112, 113, 114, 115, 116, 170, 118, 120, 121, 122, 123, 127, 162, 163, 164, 165, 166, 128, 129, 130, 124, 133, 131, 132, 126, 134, 136, 135, 137, 171, 138, 172, 173, 174) and
                fed.air_date between '5/27/2013 12:00:00 AM' and '6/2/2013 12:00:00 AM' and

                fmt.cha_int in (125, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 35, 36, 37, 38, 39, 40, 41, 43, 117, 45, 42, 44, 47, 49, 50, 51, 46, 52, 53, 54, 55, 56, 48, 59, 60, 57, 63, 58, 62, 64, 66, 69, 68, 67, 65, 70, 73, 71, 74, 72, 75, 76, 77, 78, 79, 82, 80, 159, 160, 161, 81, 83, 84, 85, 88, 87, 86, 89, 90, 61, 91, 92, 93, 95, 96, 97, 98, 99, 100, 94, 155, 156, 157, 158, 103, 104, 102, 101, 105, 106, 107, 108, 109, 110, 119, 111, 167, 168, 169, 112, 113, 114, 115, 116, 170, 118, 120, 121, 122, 123, 127, 162, 163, 164, 165, 166, 128, 129, 130, 124, 133, 131, 132, 126, 134, 136, 135, 137, 171, 138, 172, 173, 174) and
                fmt.air_date between '5/27/2013 12:00:00 AM' and '6/2/2013 12:00:00 AM' 

Answer 1:

从IN（的Transact-SQL）

包括IN子句中一个非常大的数量值（成千上万）会消耗资源并返回错误8623或8632.要解决此问题，存储在一个表中的IN列表中的项目。

因此，我会建议插入值到一个临时表，然后或者在加入到该表或从表中选择的IN

因此，像

DECLARE @TABLE TABLE(
        val INT
)

INSERT INTO @TABLE VALUES(1),(2),(3),(4),(5)

SELECT  *
FROM    MyTable
WHERE   ID IN (SELECT val FROM @TABLE)

SQL小提琴DEMO

Answer 2:

做从生产数据库备份（有许多行），并用它你开发机器上本地播放。 优化可能需要一段时间，可能实际上是十分困难的，如果你是新来的SQL。 打破查询分成几个临时表，并在结束toghether加入他们的行列。 要努力从查询中删除dbo.GetFeeds（...）函数，看看是否能功能的问题。

文章来源: The query processor ran out of internal resources exception