Am Montag, den 29.10.2007, 01:23 -0400 schrieb Andrew Jensen: > Marc's point is well taken. This isn't really testing apples and > oranges...but let's get real about this. > > What I have here is a MS Access database. It isn't that large - file > size 45 Megs. The largest tables are just under 129,000 records. > > Using Base as the front end I can open that database and it opens on the > desktop in a snap. Virtually no delay at all. > I can open a data view window, it pops onto the screen, on the largest > table and jump from record 1 to record 128,469 and it takes ~3 > seconds...this is with Base now..not MS Access as the front end. > I can run queries contained in that MDB file that perform summary > calculations on those tables and none of them take more then 7 seconds > to put the answer on the screen. In other words using the jet database > engine and ADO I can actually work with this data, using Base as the > front end. > > > OK now move that data into a Base embedded database. The size of the > file on disk drops to 30 Megs. Well, big whoop, who cares. > That's it for good news, really. There is no way anyone would actually > work with this database. It is incredibly slow. It consumes memory like > there is no tomorrow. > I've put up numbers on this before I won't do so now. I will say that it > is not as slow as it used to be. > > Using Derby yesterday I only got the one table moved in. A table with > 16,361 rows. Open the database, it opens pretty fast ALMOST as fast as > the MDB backend database. Open the dataview window and it opens pretty > quick, not a pop like using the MDB file, but less then 2 seconds..so OK. > Jump to the last record - go to lunch- OK not lunch, but you might as > well go refill your coffee cup. And the whole time the CPU is pegged. > The memory usage is almost as bad as with the HSQL embedded database - > but like Marc says I can't really compare the two directly.
One thing to keep in mind here is to watch out for having the same indexes - although it looks as if in your case this would not do a big change. > Now all three examples there are using the same front end..so where is > the difference. Well, that is easy, I think...memory allocation > > When the Base > MDB combination opens that table the memory allocated > goes up by ~2 Megs - in one step. > When the Base > HSQL combination opens the table the memory allocated > goes up ~35Megs in increments, large ones it seems. > When the Base > MDB is told to go to the last row memory allocated jumps > by ~42 Megs - in one gulp. > When the Base > HSQL combination does the same it starts allocating > memory in ~128K blocks for ~10Megs more. ( close and open that table > again and the first thing it does is free a block of memory and then > starts allocating blocks again..this time smaller then 128K until the > total allocated memory now exceeds when it was opened just a few seconds > ago ) > I can't give you exact numbers but Derby is even worse in how it does > this, it appeared. Interesting observation ... maybe it would be worth while telling the JVM running HSQL to use more memory (or less, who knows ;). > In fact that is not the full picture of the HSQL embedded database. > Using OO.org 2.3 there is a new problem. Open and close that table > repeatedly and each time you do it gets slower, on the third pass it > took 57 seconds to go from the first to the last record and each time it > allocates a total amount of memory greater then the previous time. This is the point where commiting the data from HSQL to file comes into play (dunno if repacking to .odb is involved, I think so). That is one main cause of coffee breaks using internal HSQL. > So next weekend, I have three days with the house completely to > myself..and I will try and get very precise, as precise as I can, > numbers for all this. Will it help, I don't know...but it seems to me > that if memory allocation is actually the problem then that is something > that can most addressed. > > During the week I'll run some ideas for the actual tests to execute past > folks. For evaluating the difference between HSQL and Derby the best would be a setup with equal databases hosted on a local server, as said. >From making some quick tests using internal HSQL vs. a MySQL 3.x server on a local net I remember MySQL getting slow on network performance when doing massive copy or select actions. That's where HSQL was a lot faster. On some selects to MySQL base was really slow and XMySQL (an elderly administration tool) was lightning fast. Another issue coming to my mind: How much memory does your computer have? Especially win xp is known to be rather slow when having too less memory. Some observations you made and reported over the time I could not reproduce at all when trying on a machine running win98se with 1GB RAM. Marc --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
