256 table limit. Adding joins dynamically.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

256 table limit. Adding joins dynamically.

Author

Topic

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 07:33:06

Hi All,

I'm stuck on this 256 table limit. My system occasionally blows up when using custom reports. Hence I have a solution, if the limit trips over 256 then it switches to stored procs. Ok that's slow but that isn't the question!.

I'd like to take a view (which may call other views etc.) and add joins to it until I trip the 256 limit, I can then store the figures (all of this storing and reading part is done).

So how can I do this?! I could go into every view and add the following until it crashes with the 256 limit. Is there any way to do this??

join person person1
on person1.person_id = view.person_id
join person person2
on person2.person_id = view.person_id
join person person3
on person3.person_id = view.person_id
-- CRASH HERE WITH 256 TABLE LIMIT.
join person person4
on person4.person_id = view.person_id

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-03-16 : 07:41:49

WOW !!!
That's crazy stuff man !!

There has to be a better way to solve this - why on earth are you having queries with more than 256 tables?

Duane.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-03-16 : 08:01:33

I agree, if you are hitting this limit you have a signficant flaw in your database design.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 08:02:13

Why do you need to join the same table more than 256 times?

Madhivanan

Failing to plan is Planning to fail

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 08:53:43

There is no choice the dbase is very complex and I cannot re-engineer it (under any circumstances). I need to get an accurate table usage count from each view. The only way I could see to do this was to keep doing joins until the 256 limit is hit, obviously I don't leave the extra joins in!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 08:56:32

>> I need to get an accurate table usage count from each view

What do you want to get from those joined tables?
Did you mean counts from each table?

Madhivanan

Failing to plan is Planning to fail

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:05:16

I need to get the figure for however many tables are joined together, this will then allow me to make sure that I switch to procedures before hitting the 256 table join limit.

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:08:41

E.g. looking at a slightly nonsense view, subselects and joins onto the same table multiple times must be coped with.

create view myview as

select p1.person_id from
sims.sims_person p1
inner join sims.sims_person p2
on p1.person_id = p2.person_id
where p1.surname = (select top 1 surname from sims.sims_person where surname = 'slater')

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:10:53

Just for info this proc would fail....

-- exec calcTableExpense myview -- TO RUN

-- RESULTS

level objid name
----------- ----------- -------------
1 1291359865 myview
2 1582628681 sims_person

expense
-----------
2

----------------------------

create procedure calcTableExpense (@named_object sysname)

as
begin

declare @level int, @rows int
select @level = 1

declare @objectCounter table
(level int , objid int, name sysname, type varchar(2) )

insert @objectCounter (level , objid , name , type )
select @level, id, name, type
from sysobjects where name = @named_object
select @rows = @@rowcount

while @rows > 0
begin
select @level = @level + 1

insert @objectCounter (level , objid , name , type )
select distinct @level, d.depid, o.name, o.type
from sysdepends d
join sysobjects o
on o.id = d.depid
join @objectCounter oc
on d.id = oc.objid
and oc.level = @level -1 -- calculate depedencies of previous level
where oc.type ='V' -- only a view will incur further expense

select @rows = @@rowcount
end

select * from @objectCounter
select count(*) as expense from @objectCounter

return
end

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:12:56

Sorry but this is really complex! I'm hoping one of you really experienced guys knows enough to help me out here!!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-16 : 09:23:33

You haven't explained what you are trying to do well enough for anyone to help you.

Until you can do that, don't expect much help.

CODO ERGO SUM

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:25:25

Ok. I shall try again... If a view uses 50 joins, then another uses 208 joins e.g. 258 joins, sql server will fail. You cannot join over 256 tables together. I need an accurate count for each view. Is that clear?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 09:31:05

So you want to know how many joins are used in a view?

Madhivanan

Failing to plan is Planning to fail

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:35:33

How many tables are used towards the table limit of 256 (within the view).

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-16 : 09:37:18

Tim, as you have found out there is no way to break the 256 table limit. Therefore you have 2 options (as I see it):

1) Maintain whatever basic logic that is involved with all these joins but break it out in intermediate "rollup tables". So that the result of some of these views are new tables. The obvious problem here is that the data is not necessarly "real-time".

or (and this is what everyone else was trying to get you to explain)

2) Describe your overall objective and existing data model to an extent that we can see why you think you need all these joins.

Be One with the Optimizer
TG

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:46:18

Yeah this is not good, my DBA doesn't know how to do this either and he's very experienced. The long method is to take each view and manually add joins to it until it breaks. e.g. 56 join view would allow 200 inner joins before it broke. Yuck. I have around 400 views to do this with I think.

My system is HUGE, many screens rely on the table structures and there is no way I can re-design. I cannot easily make rollup tables as this would have business implications although would certainly work. It would be weeks of work to re-design the reports engine to cope with these new tables (we wrote our own reports engine).

My overall objective is simply to allow the users to run whatever crazy report they wish containing as much historical data etc. as they like without it crashing!

I guess no-one has ever solved this!

Appreciate your help :-)

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:49:05

Oh just to mention (before you all get very bored!!), my current C# code is very clever and does work by switching to procs just before the 256 limit is maxed out (at 240 table joins) so 95% of my reports do now work, problem is I don't have a very accurate table count for each view, the proc I posted (which you can run yourself) was giving me the figures, but they are incorrect in the circumstances that I also showed (in the example view I posted).

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-16 : 09:57:37

>> "my current C# code is very clever"

Igor?

CODO ERGO SUM

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 09:59:38

Reports engine is written in C# which calls the views to generate the reports... forget it!!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-16 : 10:04:47

[code]select sysobjects.name,
count(distinct sysdepends.depid) as components
from sysobjects
inner join sysdepends on sysobjects.id = sysdepends.id
where sysobjects.type = 'V'
group by sysobjects.name[/code]

But I refuse to believe that any of this is necessary, and if you took the time to actually tell us what is going on I bet we could find a better way for you to do it.

Tim F
Starting Member

35 Posts

Posted - 2006-03-16 : 10:33:36

Fine! I've created a table and a view that runs off it. Tell me how many tables this uses towards the 256 limit. Your select will say 1, this is incorrect.

create table all_names (person_id int, surname varchar(100))

insert into all_names
(person_id, surname)
values
(1, 'slater')

insert into all_names
(person_id, surname)
values
(2, 'slater')

insert into all_names
(person_id, surname)
values
(3, 'jones')

create view myview as

select p1.person_id from
all_names p1
inner join all_names p2
on p1.person_id = p2.person_id
where p1.surname = (select top 1 surname from all_names where surname = 'slater')

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources