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 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.