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
