Bernard, have you tried valentina? It is very fast!!! :-D
cheers andre On 1/15/08, Bernard Devlin <[EMAIL PROTECTED]> wrote: > Part of a web application I'm working on requires a large number of > queries joining several tables, and each table containing millions of > rows. Accessing the database (Frontbase) using either the JDBC > drivers or the Rev drivers provided by Frontbase, resulted in single > queries that would take anywhere between 300-900ms. As there could be > hundreds of such queries in order to produce one response, I had to > look at ways to speed this up. I don't believe that it is a question > of writing better SQL - the queries have to spider across the data, > and keep a track of the route they take to get to each node in the > graph. > > I tuned the database, making sure that all the 'where' columns were > indexed, and checked the query plan to ensure that it was precisely > those indexes being used in the queries. I gave the database 100mb of > RAM cache, and even tried loading the core tables 100% into RAM. But > still I could not really get much better performance. From my > testing, the data did not seem to be found in RAM until after the > first time it was retrieved from disk, despite my setting the tables > to pre-load in RAM. Perhaps over time RAM-cached data would have > shown a much better performance than at this stage. > > I decided to try HSQLDB - a rdbms written in Java, and which can > contain all its tables in RAM, so I could be sure that the data was in > RAM. After exporting the core tables to CSV format (and transforming > them to SQL Inserts using Rev), it took about 1 hour to start the > database, and when started the database took 1.5gb of RAM. But the > queries were still slow (my guess is that HSQLDB is just not designed > to take that much data). > > So, I took the CSV data and imported it into a stack, turning each > table into a custom property set. I could now search the data in a > tiny fraction of a second -- literally. The hundreds of queries > involved in making a single response are completed in 200ms. That's > right -- I can now run the entire series of queries in less time than > it took to do a single query accessing a rdbms. > > Obviously, custom properties being used in this way are not > appropriate if one needs referential integrity, locking, concurrent > access, etc. But for a read-only query, Rev has provided a very > satisfactory solution. Moreover, whilst HSQLDB would take an hour and > 1.5gb of RAM, Rev could load the 100mb of CSV data in 2-4 secs, making > the frequent updating of data and subsequent restarting of the > application feasible. > > After following Pierre Sahores' tutorial on creating persistent Rev > CGI processes, I now have these queries returning a web page in 200ms, > instead of the 30 secs they would have taken using Java and a > database. I know all of this flies in the face of many people's > expectations, and it is certainly not the position I saw myself being > in 2 weeks ago. The rest of the application is written in Java, but > this core feature has been made possible using Rev. I expect that > associative arrays in e.g. PHP might also be as fast in this regard. > > Bernard > _______________________________________________ > use-revolution mailing list > use-revolution@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-revolution > -- http://www.andregarzia.com All We Do Is Code. _______________________________________________ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution