• Post Reply Bookmark Topic Watch Topic
  • New Topic

How to handle this large result set  RSS feed

Tanya Ruttenberg
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a seam application that generates some big queries. I have simplified it down to 7 tables and am still having a problem. I have 7 tables, 2 of which are mapping tables to handle the many-to-many relationships. One table is particularly large. The WHERE criteria can be from any of the 5 data tables (not mapping table) or any combination thereof.

I have been trying for awhile to figure out how best to construct the queries I need. The easiest thing would be to use seam's entityquery framework with restrictions. Like this excerpt:

This actually works very well for smaller data sets. I haven't figured out the threshold, but it works on a dataset of 25,228 (all the data where state='AZ'). When I try it on a larger dataset (ie all the data where state='TX', a bigger state), the query just times out.

Originally when I wrote this app, I constructed a SQL query that selected only the id of the entity I wanted based on the search criteria and then grabbed entities that matched the id list. That actually worked very well, but the way I did it was very "hack"ish and contained a lot of duplicated code. I'm trying to improve it now, but still unsure how to do it. When I try to construct my own SQL, the algorithm for building joins between the various tables eludes me.

The easiest thing, as I said, would be to restrict the dataset using the EntityQuery restrictions. Is there any way to handle such a large data set so that I can use this framework?
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!