Morning,

> yes i understand, but now with my 50 millions rows table i start to meet the limit of firebird where a simple prepare can take around 1 s to 1 min dependantly the charge of the server (see my previous post). next year it's will be around 100 millions rows and i will have no solutions ... this why i start to thing about sharding in an easy way, in a way out in fact

Hmm. I've not really heard of "sharding" as such, but what you propose
as a solution to your huge table problem, may not be the best one.

As an Oracle DBA, I work with tables holding hundreds of millions of
rows. Now I'm not 100% sure what preparing a statement on Firebird
should take so long on bigger tables and I can see how, with the present
state of things, that that will be a problem for you.

However, where I have these huge tables I can use Oracle Partitioning to
split them up into logical units based on the value in (a) specific
column(s) of the table. As long as this partitioning column is included
in a query, then a full table scan turns into a scan of one or two
partitions.

Instead of searching hundreds of millions of rows, I search a few
thousand instead.

Obviously, that assumes that an index cannot be used for that particular
query. However, the indexes can be partitioned to match the partitioning
of the table, so an index scan is then reduced to a few partitions
rather than a complete index lookup.

Performance is far better when partitioned, and there's no need for
cross database communications and synchronisation.

I think you also mentioned that Ebay doesn't allow joins and uses the
application to do the joining. If that's the case, I'm glad I don't work
for Ebay. I'll be willing to bet that the Ebay application is written in
Java - that sounds like the sort of thing that the Java developers (and
vendors) I come into contact always do, treat the database as a bit
bucket and reinvent the wheel - caching results, joins, referential
integrity, check constraints etc.

Everything that the database is designed to do efficiently and at great
speed using the resources of a big server is done at the client using a
small Java application - and screws the network while it is pulling all
that data over to "join" in the application.

Just my £0.02.


Cheers,
Norm.

PS. Sorry about the rant at the end there!

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767