CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jeeva
Jeeva

Reputation: 4663

The query processor ran out of internal resources exception

I have the following query which runs in Sql Server 2008, it works well if data is small but when it is huge i am getting the exception. Is there any way i can optimize the query

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'

Upvotes: 1

Views: 949

Answers (2)

mortb
mortb

Reputation: 9839

Do a backup from your production database (with many rows) and play with it locally on you development machine. The optimization may take some time it may actually be quite hard if you are new to sql. Break down the Query into several temporary tables and join them toghether in the end. Try and remove the dbo.GetFeeds(...) function from the Query to see if that function is the problem.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166356

From IN (Transact-SQL)

Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.

So I would recomend inserting the values into a temp table and then either joining onto that table or selecting the IN from the table

So something like

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 Fiddle DEMO

Upvotes: 1

Related Questions