Limits on rows in a table

[chloe.crowder@bl.uk]

Hi

We received the following in an email from a third-party supplier (who
naturally has a solution for the problem as described). It sounds like
gibberish to me, but does anyone have any comments?

<quote>
SQL in its current incarnation hits a performance brick wall when a
table contains more than about 75 million rows. This is not a
configuration limit as the table could be grown a lot larger but the
performance issue generates problems for ??????; primarily during
search and retrieval of archived objects; although if the database
engine is being heavily hit for retrieval the archiving process can
slow down as well.
</quote>

Chloe Crowder
British Library

[Robert Klemme]

Re: Limits on rows in a table

chloe.crowder@b l.uk wrote:[color=blue]
> Hi
>
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
>
> <quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??????; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
> </quote>[/color]

I've never heard something like this before. Maybe it's a problem
limited to a specific application. Maybe the app uses the DB in bad
ways (wrong indexing...)?

robert

[David Portas]

Re: Limits on rows in a table

chloe.crowder@b l.uk wrote:[color=blue]
> Hi
>
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
>
> <quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??????; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
> </quote>
>
> Chloe Crowder
> British Library[/color]

"SQL in its current incarnation..."

SQL is a language and it has no such limitations. Did they mean to
refer to Microsoft SQL Server? Either way, their claim is plain
nonsense.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: Limits on rows in a table

(chloe.crowder@ bl.uk) writes:[color=blue]
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
>
><quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??????; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
></quote>[/color]

SQL [Server] in its current incarnation? Nah, rather the current
incarnation of the application from the supplier hits a brick wall, and
the supplier needs to clean up its act.

OK, a 75-million is no game for kids, and it requires more careful coding
and design than a 750000 row table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Stu]

Re: Limits on rows in a table

Erland is correct; I personally am running an app that generates
40,000,000 rows of data a day, and it performs well (and it's not a
super server, either). I also suck data out of app by a vendor that
has less than 100,000 rows per day, and it takes hours using their
method.

Not saying you should point the finger at your vendor, but if the
performance of their app is mission-critical, and they value your
business, you need to have a conversation with them about SQL tuning.
Could be something you're doing, or it could be something they're
doing. But it's probably not the engine (unless you're running SQL
Server on a bare-bones server).

Stu

[1492a2001@terra.es]

Re: Limits on rows in a table


chloe.crowder@b l.uk wrote:[color=blue]
> Hi
>
> We received the following in an email from a third-party supplier (who
> naturally has a solution for the problem as described). It sounds like
> gibberish to me, but does anyone have any comments?
>
> <quote>
> SQL in its current incarnation hits a performance brick wall when a
> table contains more than about 75 million rows. This is not a
> configuration limit as the table could be grown a lot larger but the
> performance issue generates problems for ??????; primarily during
> search and retrieval of archived objects; although if the database
> engine is being heavily hit for retrieval the archiving process can
> slow down as well.
> </quote>[/color]

Translation 1:

We charge little money for our services, and we need to have profits so
we contract amateurs to do the job and when there are problems we
complain against something/someone not related to us. If you want a
solution pay us more money.

Translation 2:

We didn't think about a table with 75 million rows, we don't want to
look like stupids and lose you as a customer.

etc, etc...

One thing is sure, your supplier is not honest, don't trust them!
[color=blue]
>
> Chloe Crowder
> British Library[/color]

[chloe.crowder@bl.uk]

Re: Limits on rows in a table

Thanks everyone - I was 99.9% sure it was rubbish and the problem was
with their app, but I thought I should check.

Chloe