|
|
I decided to extract my test objects into a simple maven based webapp for easier deployment since our main application is quite large. This would allow me to rule out the possibility that there was something in the main application that could be interfering and creating the bottleneck. Unfortunately, the simple webapp with just this code behaves precisely the same way.
In testing with MySQL as the backend, I experienced no issues. I generated random 300k records for both the non-composite and composite-key tables and both performed nearly identical in fetch time regardless of page size specified.
In testing with MSSQL as the backend, I started out with the same 300k records for both entity scenarios. I experiences the same stalling behavior when iterating the results as I did in the main application. I decided then to tweak the number of data records by removing subsets by plantId, reindexing, and then running the page size test case of 250. As the number of records in the composite table continued to drop due to my repeated exclusions, the result set iteration got faster and faster. Once I reached approximately 20k rows in the table, the time between non-composite key fetch versus composite key were nearly identical.
I reran the same MSSQL tests on a brand new database with just these two entity tables and observed the same performance degradation as I did on the main database was the numbers of rows dropped from 300k to 20k.
I would like to test this same scenario on Oracle, but I have to get Oracle 11g installed before I can do that. The only working copy of Oracle we have is 8i and I ran into issues trying to get the driver to work with such an old database.
|
|
|
|
|
|
|
|
