Java: Best way to handle HUGE SQL query result

[KevySaysBeNice]

I am writing a program to pull documents out of a content managment system and move them somewhere else.

Anyway, there are around 4 million documents that need to be pulled and processed, but these documents are split up into groups of approx. 100,000.

This process needs to run only ONCE. Once it's complete, it will never run again.

The information I need to pull/process each document is stored in a row in a view I created, but the view is faily complex and is made up with lots of joins, and other joins with more views.


Anyway, my question is, what is the best way (with the smallest cost) to get the results of my database call?

For instance, I know it is possible to store results in a "result set" which I believe still maintains a link to the database, but I believe there are other ways of getting/storing results from a database call. I'm not sure what the best way to store all of this info is, that will be the quickest...

Thanks for any advice!

 

[generelz]

Yes, you will want to manually create a Connection to the database, issue a query via a Statement (or PreparedStatement), and then work with the results returned via a ResultSet.

The general prototype for something like this is:

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
 conn = getConnection();
 stmt = conn.createStatement();
 rs = stmt.executeQuery("select * from myview");
 while (rs.next())
 {
   //handle rows in result set
 }
}
catch (SQLException e)
{
  System.out.println("Something bad happened: " + e.getMessage());
}
finally
{
  if (conn != null) conn.close();
  if (stmt != null) stmt.close();
  if (rs != null) rs.close();
}

How you create this connection depends on the DB Vendor and driver you are using. If you have any more questions, feel free to ask.

 

[enlightenedby42]

In my fairly limited experience, one thing that will help a lot is just making the query work as well as possible.

Beyond that....there's really nothing to do but iterate through the result set. I'm far from clever, and I'm sure there are ways to make it efficient here and there.

If you have to hit and process each row in sequence, I don't really see how you could do much better than O. Your query can always be optimized if that's a bottleneck.

The code should be really simple if it's anything like similar stuff I've done. There's a ResultSet object that acts as a dataset with a next() method to iterate through the rows, so it'll be something like

while(result.hasNext()){
     do stuff
     result.next();
}

For a one shot thing doing that a few million times shouldn't be a huge deal really IMO.

edit: somebody beat me to it and did a way better job, but it's really not a huge thing to do something like this. I'm very much a rookie and can do this kind of stuff in my sleep.

 

[KevySaysBeNice]

Thanks guys!

The thing is that I just wasn't sure exactly how "ResultSet" works. As I mentioned, I was under the impression that it keeps a connection for the database, but I am not sure what EXACTLY that means. I mean, if that were to mean (for instance), that each .getNext() call required the database to be queried again with my original SQL, then that would be a problem.


Anyway, I think you are probably right, I need to make sure my query is as fast/cost efficient as possible. Problem is I'm new to SQL, and I'm having a somewhat difficult time. Plus, as far as I can tell, the database is not exactly designed wonderfully - or at least I can't access the information I need easily without a bunch of joins and things.

Anybody feel like helping me out here?

Here is the form of the tables in the database:

Table: Node

group      node_id       object_id     type                 children
--------------------------------------------------------------------------------------
2            4213            null             document        2
2            4214            2342           page               0
2            4215            2343           page               0
15          87875          null             document        1
15          87876          16732         page                0
....
...
....
(4 million more * number of pages each documenet has on average)
....

Table: Object

group     object_id        file_location
--------------------------------------------------------------------------------
2            2342               \\server\folder\somerandomname_0.tif
2            2343               \\server\folder\somerandomname_1.tif
15          16732             \\server\applesauce\inyerserverblahblah_0.tif
...
....
..
(4 million * how many pages)

Table: Fields

group        node_id          f_name           f_value
--------------------------------------------------------------------------------------------
2              4213              color               yellow
2              4213              name              Harry Potter
2              4213              wand              Pheonix Core
2              4213              subject           MC Chris, DQ Blizzard
2              4213              author             Starbuck and Final Five
15            87875            color                Pink
15            87875             myspace        HellNo
15            87875             Dexter            HellYes
.....
....
....
(4 million * however many attributes each has)

So, here is what I want to be able to do:

select * from theViewOrWhatever where group = 15

and returned, I'd like:

node_id      children     file_location                                                         color     name      wand     subject     author    myspace   Dexter 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
87875         1         \\server\applesauce\inyerserverblahblah_0.tif                        Pink     null      null         nulll      HellNo      HellYes
.....
.....
(other 100,000 or so documents in group 15)
....

Anyway, here are the things that I'm using to put this together:
-the node_id of the first page of a document is just the document's node_id + 1.
-I only need to get the file_location of the first page of a document, because all pages of a document are see-quen-sial-ee numbered, so I can just incriment the filename to get the next page (assuming there is one)
-The number of children is the number of "pages"
-The "group" is basically the smaller segments of documents. So, for instance, there might be 100,000 documents of group 2, another 100,000 documents for group 15, another for 27, etc. I just didn't feel like typing all 100,000 documents out.
-one can distinguish between a node of type "document" or "page" obviously by checking the "document" attribute, OR by checking to see how many children the node has (pages have 0 children, documents have as many children as the document has pages).


So, as of now, I'm making a view that has:
group node_id firstPage (which is the node_id + 1) children where type = document

and a view that has
node_id file_location by doing a left join on the node table and the object table where the node table's object_id equals the object table object ID

then, I can do a left join with my two views to get a table that has all of these attributes. This is taking forever though (literally?).

I don't know enough about how a database really works to know what the best way of managing my task is.

Perhaps I should just do a bunch of individual database calls, one for each document? It jsut seems like then we're talking about 300,000 database calls (approx) instead of 100,000, which seems like a huge jump?

Thanks for any help/advice. Sorry for the long long long post, but hopefully if you are reading this all then you are either a wonderful person or feel like taking a challenge/showing me how easy this really is

 

[generelz]

There's really quite a lot of information there.

Let's try and focus on one thing at a time. What are you most concerned with? Getting a working solution or simplifying your query/results? Optimizing your query? Efficiently processing the data returned by your query? Changing the contents of the view?

 

[KevySaysBeNice]

a) I'd like to be able to reuse the SQL because there are actually around 300 groups of documents. I don't want to babysit.

b) Speed. I need to finish these documents in a few months.

c) I also will be combining the single page tifs into a multipage tif, then I'll be converting the tifs to a PDF. On average, this will probably take around 2 seconds. So that's around the order of magnitude I'm working with.


Also, I said that there are around 4 million documents split into 100,000 or so groups, but in reality there are 4 million documents split into 300 or so groups, ranging in size from 10 documents to 200,000 - point being I would like to be able to basically reuse the same SQL queries so that I don't have to custom modify every single query. One of the issues is that there is different metadata (see: the "Fields" table) for each document even if it's in the same group. Overall there are 29 different possible pieces of metadata. Some documents could have 0 types, some might have 9, 10.

The easiet way to do this would probably be to make a bunch of queries, one for each document to get all the info I need, but would 3 or 4 million extra quries mean 3 million extra seconds?

 

[enlightenedby42]

a) I'd like to be able to reuse the SQL because there are actually around 300 groups of documents. I don't want to babysit.

b) Speed. I need to finish these documents in a few months.

c) I also will be combining the single page tifs into a multipage tif, then I'll be converting the tifs to a PDF. On average, this will probably take around 2 seconds. So that's around the order of magnitude I'm working with.


Also, I said that there are around 4 million documents split into 100,000 or so groups, but in reality there are 4 million documents split into 300 or so groups, ranging in size from 10 documents to 200,000 - point being I would like to be able to basically reuse the same SQL queries so that I don't have to custom modify every single query. One of the issues is that there is different metadata (see: the "Fields" table) for each document even if it's in the same group. Overall there are 29 different possible pieces of metadata. Some documents could have 0 types, some might have 9, 10.

The easiet way to do this would probably be to make a bunch of queries, one for each document to get all the info I need, but would 3 or 4 million extra quries mean 3 million extra seconds?

Well, on the last bit doing millions of queries means millions of hits to the database. You're going to want to execute your query or queries before you loop through the result set for something like this.

If by reusing the statement you mean searching for the same kind of data using different conditions in your "where" clause then you've just got to get user input somehow and parameterize that into the query, which is very simple. The query can be parameterized nicely, in other words, but it won't "self customize" to execute statements that pull different data from different tables. But you can easily pull the same data based on different parameters (such as the node_id or group in the tables you're dealing with).

Now....if you're needing to perform a 2 second operation on each row of the dataset independently....that's tricky. If you have 4,000,000 records, for example, and at two seconds a piece you need 8,000,000 seconds (at minimum, not even taking into account time needed for other parts of the program)..that works out to about 92 days according to my friend Google. That's pretty ugly, and a faster way to do that is way beyond my level of experience but I'm sure somebody around here has done that sort of thing.

Regarding the queries at least, you'll just build a query that pulls the appropriate range of records in one fell swoop and then iterates through a result set afterward. After all, if you can pull one record based on a given criteria, you can pull a bunch of records on a criteria or range of values as well. Instead of saying "where group = 15" you'd use something like "where group between 1 and 15" for example.

The program to handle something like this, especially for a one shot internal app, is actually a lot simpler than you'd think. That PDF conversion bit definitely throws a wrench in things though. Not so sure on that part...

 

[KingKaeru]

4 million records is a drop in the bucket and shouldnt be a problem. Make sure that your queries are optimized and that you have proper indexes defined on your tables.

If using mysql. Checkout the explain function for help on optimizing your queries.

Where are you seeing bottlenecks with this process?

 

[KevySaysBeNice]

I am far from an expert with databases and SQL.

I am an intern, and my experience with databases is limited to my databases class, which covered things like select, update, joins, and other stuff that frankly I don't remember but would probably come in handy right about now .

Anyway, KingKaeru, care to elaberate on exactly what you mean when you say "queries are optimized"? I imagine you are speaking of (obviously) not wasting calls to the database, or just poor logic (i.e. creating views that aren't needed and require a lot of processing/etc).

Sorry for the terribly boring thread subject

 

[KingKaeru]

Optimizing queries is a very general term. To really know if you're queries are optimized, it's best to use the tools available for the db that you're using. Like I said earlier Explain is a very handy tool to identify problem areas and bottlenecks within queries for mysql. In sql server, you have the sql query analyzer tool. You can't really optimize anything without first examining how it performs initially.

As for the resultsets and maintaining a connection to the database. That's probably not what will be your problem area. The driver and dbms should take care of that for you.

 

[generelz]

You probably need to take a step back and think about this problem in the most abstract way possible (let's not think about queries and optimization just yet). Have you written out some pseudo-code outlining the process you want to use to build the results? Have you characterized all the error conditions and contingencies you may need to deal with?

Once you do this, it would help to break down/characterize each step in terms of what it actually does - is this step primarily loading data, or processing data that was loaded previously? If you have a step which involves both of these things, break it into two different steps. Try to make each step as atomic as possible. Then, once you have things boiled down to "brass tacks" you can see what you could combine/optimize by using joins or views on the database level.

Does that make sense? Unfortunately school doesn't give you much in the way of planning out a project to build a non-trivial application such as this. I still find myself rushing headlong into things without taking a step back and thinking them through, only to find I wished I had done a little planning at the beginning.

 

[enlightenedby42]

Does that make sense? Unfortunately school doesn't give you much in the way of planning out a project to build a non-trivial application such as this. I still find myself rushing headlong into things without taking a step back and thinking them through, only to find I wished I had done a little planning at the beginning.

That is SO true. You do almost no projects of scale in school, or at least I haven't.

Really, until you deal with something good sized like the OP is dealing with, you have NO idea why abstraction is important, and why "conquer and divide" are important and how to really use any of those principles...

 

[KevySaysBeNice]

Well, I've been working on a similar problem (actually it's a more difficult problem from a planning/programming standpoint because the metadata I am getting for this project has to be dealt with more carefully... each "group" of documents has it's own specific requirments, and in the end I also am creating "Import Documents" for this system called "InMagic" or more specifically DB/TextWorks.


Anyway, I guess my point is I have the whole process basically done already, I will be piecing it together from old code ("old" = a month ago) but I have all of the tif combination tools, tif -> pdf, as well as a IBM CM uploader class..

Before on my other project (maybe I mentioned this, I don't remember) I had around 100,000 documents TOTAL to worry about, so I've been pretty sloppy with database queries and things. I think I query the database at least three times for each document.


Long story short, now with 4 million documents I need to make sure I'm not wasting a TON of time... I don't mind wasting a little time, but I don't have a good feel for what sort of time losses I'm talking about, if, say, I make 1 call that gets the majority of the information, then a second call that gets metadata for a document, etc.

 

[generelz]

Here are a couple of tips:

- Use parameterized PreparedStatements instead of regular Statements. These get compiled server-side and most enterprise strength db servers keep the query plans for these types of statements cached, meaning you don't take a performance hit form the server re-compiling and re-planning the query. This helps especially for large queries with lots of joins or other complex criteria.
- Try to JOIN in related data, rather than making an additional round trip to the database. For example, it looks like you could reasonably join in a node's fields without incurring much of a performance hit. And this would more than likely save you one round trip to the db for each node.
- If you have a query that takes a long time to run, use the database's tools to try and figure out why it's taking so long. For example, MySQL has the EXPLAIN command. SQL Server has the Query Profiler application. Other databases have similar utilities.

 

[KevySaysBeNice]

OK, here is my not so masterfully created SQL statement...

I was hoping you might be able to check it out and tell me something about it?

In particular, if i do this:

select * from everything where group = 1

I get around 117000 results, however if I do this:

select * from node where group = 1 and type like '%document%'

I get around 119000 results. I would figure that because I'm doing a LEFT JOIN I should have all of the rows possible (if that makes sense), correct? But instead, I'm missing 2000 rows...

create view everything as select
node.group,
node.node_id,
node.number_children as pages,
node2.object_id,
object.image_file_name,
cf1.cf_value division,
cf2.cf_value ship_to, 
cf3.cf_value sold_to, 
cf4.cf_value document_date, 
cf5.cf_value customer_order_no, 
cf6.cf_value mill_order_no 
from dbo.rwp_node node
left join dbo.rwp_node node2 on (node.node_id +1) = node2.node_id
left join dbo.rwp_object object on node2.object_id = object.object_id
left join dbo.rwp_control_fields cf1 on node.node_id = cf1.node_id
left join dbo.rwp_control_fields cf2 on node.node_id = cf2.node_id 
left join dbo.rwp_control_fields cf3 on node.node_id = cf3.node_id 
left join dbo.rwp_control_fields cf4 on node.node_id = cf4.node_id 
left join dbo.rwp_control_fields cf5 on node.node_id = cf5.node_id 
left join dbo.rwp_control_fields cf6 on node.node_id = cf6.node_id 
where
node.type like '%document%' and
cf1.cf_name like 'division' and 
cf2.cf_name like 'ship_to' and 
cf3.cf_name like 'sold_to' and 
cf4.cf_name like 'document_date' and
cf5.cf_name like 'customer_order_no' and 
cf6.cf_name like 'mill_order_no'

 

[generelz]

OK, here is my not so masterfully created SQL statement...

I was hoping you might be able to check it out and tell me something about it?

In particular, if i do this:

select * from everything where group = 1

I get around 117000 results, however if I do this:

select * from node where group = 1 and type like '%document%'

I get around 119000 results. I would figure that because I'm doing a LEFT JOIN I should have all of the rows possible (if that makes sense), correct? But instead, I'm missing 2000 rows...

My guess is the reason for the discrepancy is the additional constraints you're placing on the cfX fields at the end of the query.

Also, is there any reason you're using a completely unanchored LIKE query? Why don't you just say where node.type = 'document'. This should provide a performance boost. Additionally, are there indexes on all the "node_id" columns for the tables you're joining? That might provide a performance boost as well.

 

[KevySaysBeNice]

Hey, honestly, thank you very much for taking the time to read through all of this terribly boring crap that I'm posting.

Second, good point on the '%document%" thing. I was doing that because there are a few different types of documents and I wanted all of them, but it's easy enough to just specify "io_document" or "lf_document1" I suppose!


edit: OK, I lied, I didn't really think of this adding much execution time to the query, though looking back it is quite obvious - I suck

The BIG question is, how do I fix the last part. You are 100% right that the reason all the records are not showing up is because not all of the documents must have those fields... Which is to be expected.

The only solution I can think of is to make a view for each type of metadata (ie:
create view order_num as select cf.node_id, cf.cf_value as order_num from rwp_control_fields cf where cf.cf_name like 'order_num' and then I could join view to the table, that way regardless if the document had a given piece of metadata or not at least the document would show up... the problem is that would require me making 29 views and it seems like that doing it that way might be very slow, because each view would have to be processed on it's own, then joined?

edit: though, I'll try it and give a break down of execution times

 

[generelz]

Hey, honestly, thank you very much for taking the time to read through all of this terribly boring crap that I'm posting.

No problem

Second, good point on the '%document%" thing. I was doing that because there are a few different types of documents and I wanted all of them, but it's easy enough to just specify "io_document" or "lf_document1" I suppose!

edit: OK, I lied, I didn't really think of this adding much execution time to the query, though looking back it is quite obvious - I suck

Yeah, I wasn't sure if there was a reason you were doing that or not. The data you provided in the #4 post led me to believe the only thing that might appear in that column would be "document" so I was a little puzzled why you wouldn't be using strict equality.

The BIG question is, how do I fix the last part. You are 100% right that the reason all the records are not showing up is because not all of the documents must have those fields... Which is to be expected.

The only solution I can think of is to make a view for each type of metadata (ie:
create view order_num as select cf.node_id, cf.cf_value as order_num from rwp_control_fields cf where cf.cf_name like 'order_num' and then I could join view to the table, that way regardless if the document had a given piece of metadata or not at least the document would show up... the problem is that would require me making 29 views and it seems like that doing it that way might be very slow, because each view would have to be processed on it's own, then joined?

edit: though, I'll try it and give a break down of execution times

I'm not sure why you're so attached to views, personally. Making 29 views sounds very undesireable. Additionally, I'm not extremely familiar with views in SQL Server, but there could be performance implications to using views versus querying the tables directly (do views have indexes? are the indexes on the tables they include used? etc.)

In terms of retrieving the metadata, why do you need to distinguish on the name in the query itself? Could you instead just fetch all the metadata for a node (name/value pairs i assume) and determine the values programmatically, perhaps by adding it to a Map<String, String> or something? That would cut down on having to do 29 queries to only 1 query.