SQL Server Limitations Question
Ron Cichoski
I think I know the answer to this one but here goes. We are developing our
first SQL server app (we have used Oracle and Informix primarily in the
past) and we have run into two SQL Server limitations. The first one is the
16 table limit in a select. A long time ago someone called me when they had
the same problem with Sybase and I said "what the heck do you need to join
16 tables for anyway?" Especially back then the power was just not there.
But now here I am in the same situation. Most of the other tables in the
join are primary code/description tables. Is there any way around this? Is
this just a limitation for performance reasons and there's some setting to
over-ride it? The app does analysis on automotive sales, marketing, and
production data. Therfore, the users like to run a report sorted by by
region, company, division, brand, channel, vehicle line, blah, blah, blah,
which means they run into the other limitation of only 16 items in an order
by clause. Anyway, is there a way aound these two limitations and still
keep the thing as one select? Thanks!
Ron
Mark McNulty
In article cdcb...@adventco.flashnet, "Ron Cichoski" <rcic...@adventcon.com> () writes:
> I think I know the answer to this one but here goes. We are developing our
> first SQL server app (we have used Oracle and Informix primarily in the
> past) and we have run into two SQL Server limitations. The first one is the
> 16 table limit in a select.
> Anyway, is there a way aound these two limitations and still
> keep the thing as one select? Thanks!
One select? That might not be possible (unless you are talking about
select * from ... select *... or unions).
The most common trick I've seen is to use temp. tables to replace some of
your joins. I think this might be tough to do in your situation, because
if its some end-user ad-hoc query, you might not know the tables or how
to join them. In Sybase 4.9 (and probably MS-SQL Server), temp. tables can
actually give you much better performance than joins. Be careful though,
you don't want to do all sorts of work-arounds that aren't needed in the
next version.
Or you might try unions or nested selects.
Or upgrade to Sybase System 11.
I agree with your first impression- why do the users need to join 16 tables?
MS-SQL server isn't the most powerful RDBMS out there, and the users might
bring it to its knees running queries like the one your describing.
You might want to call up IQ software or Crystal Reports and ask them how
they handle 16+ tables in MS-SQL server.
Good luck,
Mark McNulty
mmc...@jyacc.com
Note- these views are mine and not those of JP Morgan or JYACC.
Russell Fields
No override possible, it is a structural limitation.
How to get around it? In a SQL batch, select the first 16 table join into
a temp table, then join that table to the remaining n tables for the final
result.
Roy Harvey
Ron,
One approach is to use a temporary table. Do some of the joins and
save the results (SELECT INTO is handy here), then join the rest of
the tables to that one.
If the tables tend to string together in a hierarchy (Join Division to
Region to District to Area to Territory) you might get away with
making a de-normalized table with all the information at all the
levels. If you can resolve the referential integrity headaches,
making this a permenant table can really help retrieval performance.
Roy
Ron Cichoski <rcic...@adventcon.com> wrote:
>I think I know the answer to this one but here goes. We are developing our
>first SQL server app (we have used Oracle and Informix primarily in the
>past) and we have run into two SQL Server limitations. The first one is the
>16 table limit in a select. A long time ago someone called me when they had
>the same problem with Sybase and I said "what the heck do you need to join
>16 tables for anyway?" Especially back then the power was just not there.
>But now here I am in the same situation. Most of the other tables in the
>join are primary code/description tables. Is there any way around this? Is
>this just a limitation for performance reasons and there's some setting to
>over-ride it? The app does analysis on automotive sales, marketing, and
>production data. Therfore, the users like to run a report sorted by by
>region, company, division, brand, channel, vehicle line, blah, blah, blah,
>which means they run into the other limitation of only 16 items in an order
>by clause. Anyway, is there a way aound these two limitations and still
>keep the thing as one select? Thanks!
>
>Ron
>
Jay Lazarus
On 16-table limits:
> Is there any way around this?
No.
Another post stated:
> Or you might try unions or nested selects.
Won't work. I quote from the System 11 manual:
"Maximum number of tables participanting in a query: 16 (Includes all
work tables, result tables, tables referenced by views (the view itself
is not counted), correlations and self-joins."
They're a pain whenever we've hit them. Odd that the "low-end"
(actually very well done) "Sybase"/"Powersoft" product
(SQL Anywhere: aka: Watcom) doesn't impose it,
but it's a _totally_ distinct engine.
We've hit 'em too in databases that are well normalized.
Flattening may work, but it'll buy you _a lot_ of maintenance pain.
Even with 16 tables, performance isn't a problem in an OLTP database,
but might become one in large DSS apps. However, we're careful
to check the actual access paths used (via showplan), and add
indexes where they're needed (e.g. all programmatic searches,
all foreign keys, all common combinations of app specific search
critiera)
Suggestion: Stay away from the flattening. Use a stored
procedure to build your work table(s), then retrieve
the results by the appropriate select from the work table within
the stored procedure.
> Or upgrade to Sybase System 11.
The above quote is from the System 11 manual.
> Is this just a limitation for performance reasons and there's some
setting to over-ride it?
No setting. It's hard coded into the dbms logic, and exhibits itself
in the system catalogs as well. (See sysreferences).
It exists in both Sybase (4.x, 10, and 11, despite news posts to
the contrary) and Microsoft.
Why: Best guess: Query optimization is hard enough without arbitrary
combinarorial explosions imparted by large numbers of tables
in the query. Also, they got greater speed (with the current
price we all are paying) by flattening their system catalogs.
Go ahead, you try to optimize both the query and access paths/order
without setting some limits, just do it, I double dog dare ya...
Steve Jorgensen
I have had the very same problem. I am working on an application with some
pretty complex structures with respect to a few big tables and many very
small ones. It doesn't matter how small the code/description tables are,
though.
It is possible, in most cases, to work around the limitation with clever
coding, but it gets tedious. I'm going to switch to Oracle Workgroup
Server to solve my problem.
Ron Cichoski <rcic...@adventcon.com> wrote in article
<01bc4e5a$a2d93ba0$cdcb...@adventco.flashnet>...
> I think I know the answer to this one but here goes. We are developing
our
> first SQL server app (we have used Oracle and Informix primarily in the
> past) and we have run into two SQL Server limitations. The first one is
the
> 16 table limit in a select. A long time ago someone called me when they
had
> the same problem with Sybase and I said "what the heck do you need to
join
> 16 tables for anyway?" Especially back then the power was just not there.
...
Michael Jenc
Mark McNulty wrote:
>
> In article cdcb...@adventco.flashnet, "Ron Cichoski" <rcic...@adventcon.com> () writes:
> > first SQL server app (we have used Oracle and Informix primarily in the
> > past) and we have run into two SQL Server limitations. The first one is the
> > 16 table limit in a select.
> > Anyway, is there a way aound these two limitations and still
> > keep the thing as one select? Thanks!
>
> select * from ... select *... or unions).
>
> The most common trick I've seen is to use temp. tables to replace some of
> your joins. I think this might be tough to do in your situation, because
> if its some end-user ad-hoc query, you might not know the tables or how
> to join them. In Sybase 4.9 (and probably MS-SQL Server), temp. tables can
> actually give you much better performance than joins. Be careful though,
> you don't want to do all sorts of work-arounds that aren't needed in the
> next version.
>
> MS-SQL server isn't the most powerful RDBMS out there, and the users might
> bring it to its knees running queries like the one your describing.
>
The problem with MS SQL Server and it's 16 table limit is the query
optimizer.
The time it takes to optimize a query rises exponentially to the number
of tables
used. So it's a question of performance. With my query which used 16
tables
the parsing and compiling process took 40 seconds (due to optimizing)
while the
query (which finally has not been optimized by MS SQL Server) processed
within
less than a second...
BTW I know lot of databases where queries with much more than 16 tables
are
very common. Mostly it is because of lookup fields. I.e. code numbers
are replaced
by some text to support multiple languages with the same database.
Surely this
is not used with the common samples of salesmen and customers...
In SQL Server I also use the 'workaround' with the temporary tables
which really
isn't too bad.
Ciao
Michael
Vic Bachulis
If you've ever looked at the joins considered by SQL, you are dealing with
N!. 16! is a LOT of joins
to consider. At that point, even if SQL muddles its way through and figures
out how, precisely, it
should join 16 such tables, you are CPU bound as it wades through hundreds
of thousands of
possible join combinations. Personally, I utilize temp tables when and
wherever possible, but the
tradeoff is that you're now DISK bound (not necessarily CPU bound). Another
approach is to try
and second-guess the users and figure out what the top queries are they
want to run and then
front-load your query parser with some "cheater" code that does large
joins. Crude, but *highly*
effective (I know - I've spent the last 3 months writing and re-writing a
query parser and tuning
same).
>
> I agree with your first impression- why do the users need to join 16
tables?
> MS-SQL server isn't the most powerful RDBMS out there, and the users
might
> bring it to its knees running queries like the one your describing.
>
Erland Sommarskog
"Vic Bachulis" <v-vi...@microsoft.com> skriver:
>If you've ever looked at the joins considered by SQL, you are dealing with
>N!. 16! is a LOT of joins
>to consider. At that point, even if SQL muddles its way through and figures
>out how, precisely, it
>should join 16 such tables, you are CPU bound as it wades through hundreds
>of thousands of
>possible join combinations. Personally, I utilize temp tables when and
>wherever possible, but the
>tradeoff is that you're now DISK bound (not necessarily CPU bound). Another
>approach is to try
>and second-guess the users and figure out what the top queries are they
Hi!
This is a standard mail that I tend to send to people who post
articles with irregular line length, that is one line long, one
short. "But I don't post any such articles!", you say. Neverthe-
less your article appeared that way in my newsreader.
There are usually two reasons why this happen: 1) you are using
a right margin of more than 80 characters. 2) you have a posting
software which autowraps, but for your eyes only, and then breaks
the lines differently when it inserts the article into the news
system.
In the first case, the reader can adapt by widening his window.
That is, if he has one. I often read news from a character-cell
terminal which has a fixed width of 80 characters. And in any
case, I find lines that are extremely wide tedious to read.
Incidently, this is why newspapers are set in narrow columns
to faciliate reading. If you are typing wide lines, I would
encourage you to narrow down your posting window. 60-70 columns
is a reasonable width.
In the other case, things are more deceivable. Unless you are tied
to the posting software your Internet provider has, I would re-
commend you changing newsreaders. None of the Windows-based news-
reader I have tried - WinVN, News Xpress and Free Agent - fold
the text differently on the screen and in the posting. If you
are stuck with your software, you have to be careful to not exceed
60 columns in width. (Or whatever you find is safe.)
I like to point out that I'm sending this mail just as much for
your benefit as my own. Supposedly you want people to read your
articles, and I often find myself skipping ill-formatted articles,
unless the topic is *very* interesting.
As I said this is a standard mail, and I don't keep track of the
people I send it too. If you receive two or more copies within a
few days, please accept my apologies. (If you received your second
copy a month later, it was probably because you didn't learn! :-)
Your sincerely,
Erland Sommarskog
som...@algonet.se
---------------
This is a demonstration of what happens when the lines in an article are longer
than a regular
terminal screen. Some words gets broken right in the middle, but what is much wo
rse is that
some lines are long, while other lines are short. This is not very fun to read a
t all.
--
Erland Sommarskog, Stockholm, som...@algonet.se
F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.
Robert Vieira
In general, all the options provided here are good ones - but there's
one I haven't seen suggested yet - views. If you have commonly joined
files - why not build a view over them. Then you only have one virtual
table to select in the place of many.
In places where the joins are used repeatedly, then I would probably
skip the view idea for the tempdb option, but views allow for some
custom indexing and performance improvements that tempdb may not give
you quite as readily.
Rob Vieira