July 22, 2008 at 12:42 am
I get the error:
Msg 4414, Level 16, State 1, Line 15
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
It is a sql 2000 database for analyzing scores and setups. It is a view that consolidates almost the entire database, thus giving you a "birds eye view" of all the data.
Will it help If I port this to SQL 2005?
July 22, 2008 at 12:56 am
No. The max number of tables in a query n SQL 2005 is 256.
My I suggest you break the query down, create temp tables to store interim results then join the temp tables to get the final result.
The broken down query may even run faster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 2:05 am
You are right. It is much faster this way.
Thanks a lot!!!!!!
July 22, 2008 at 2:44 am
What kinnd of query needs 256-table join!?
N 56°04'39.16"
E 12°55'05.25"
May 5, 2009 at 6:52 pm
When you use several views that each one of then has several tables plus Cumulative queries. That's how!
May 6, 2009 at 1:57 am
juan_wiski (5/5/2009)
When you use several views that each one of then has several tables plus Cumulative queries. That's how!
Ouch. That's just asking for performance issues, from the compiling if nothing else.
http://scarydba.wordpress.com/2009/04/24/unpacking-the-view/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply