Database development
in [Java Programming]
|
Prev: Null pointer issues
Next: Urgent
From: Pitch on 26 Apr 2010 05:14 In article <3d3dbd92-01d3-4ad7-bb94-3855c7e2b221 @y6g2000prk.googlegroups.com>, junw2000(a)gmail.com says... > > When I work on database development projects, I use JDBC and SQL. Many > people use hibernate/spring. Can somebody explain the pros and cons of > using JDBC and SQL vs using hibernate/spring on database > developments? I always believed that ORM systems are forcing you to write your own business-rules layer apart from the persistence layer. That way database access is kept simple and easy mantainable. Also, this multi-tier architecture allows for easier load-balancing, architecture changes, integration with other systems, development.. -- stirr your cofee properly
From: Lew on 26 Apr 2010 06:45 Zlatko Duric wrote: >>>>> But there is some information about all those objects I'd like to >>>>> store in a single table or maybe two of them, that'd be super-fast to >>>>> reach, without having to look for all those >>>>> parent/children/node/parameters/other links ... Lew wrote: >> Wrong approach. Zlatko Duric wrote: > Well, I don't mean to change the model. > > I want to keep and use all this stuff that's already in there. > But there are few things I think can benefit from an approach like this: > > - reports > there are a few "reports" being produced from the system. For some > 10000 objects, there are 200k mysql queries - I am not much into big > systems (yet) but this seems waaaaaaaaaaay too much to me. If I had the > data from this in an additional table (I know this means duplicating > some data), I could filter out what I need in one single query. You can do that with views or join queries without duplicating the data. > - lists > when accessing the data, users only fetch a certain small amount of > objects - 15, 20, maybe 100 at a time. So to avoid most of the node > links and stuff, I could get a quick filter of the data needed, without > having to join this and that. What do you mean by "most of the node links and stuff"? Duplicated data will require more accesses, code complication and risk than "to join this and that". > Now, I know there are some disadvantages too, like having to worry > whether I update the object and the table at the same time or so. > I also have to make sure that all the data is in sync, since it's now > duplicated. But I still can't resist wondering if this would be a good > option. No. > Luckily I still have a couple of months work already planned, so I get > to think about it :P Time isn't necessary, correct thinking is. -- Lew
From: Lew on 26 Apr 2010 06:48 junw2000(a)gmail.com says... >> When I work on database development projects, I use JDBC and SQL. Many >> people use hibernate/spring. Can somebody explain the pros and cons of >> using JDBC and SQL vs using hibernate/spring on database >> developments? Pitch wrote: > I always believed that ORM systems are forcing you to write your own > business-rules layer apart from the persistence layer. That way database > access is kept simple and easy mantainable. ORM doesn't force business rules into a separate layer and raw JDBC calls don't force them into the same layer as persistence. > Also, this multi-tier architecture allows for easier load-balancing, > architecture changes, integration with other systems, development.. -- Lew
From: Zlatko Duric on 26 Apr 2010 07:37 On 04/26/2010 12:45 PM, Lew wrote: >> there are a few "reports" being produced from the system. For some >> 10000 objects, there are 200k mysql queries - I am not much into big >> systems (yet) but this seems waaaaaaaaaaay too much to me. If I had >> the data from this in an additional table (I know this means >> duplicating some data), I could filter out what I need in one single >> query. > > You can do that with views or join queries without duplicating the data. Maybe I could, if I knew what am I looking for in the first place :) >> - lists >> when accessing the data, users only fetch a certain small amount of >> objects - 15, 20, maybe 100 at a time. So to avoid most of the node >> links and stuff, I could get a quick filter of the data needed, >> without having to join this and that. > > What do you mean by "most of the node links and stuff"? Well, my objects are documents - those are "nodes". But so are the "folders" holding the documents. And so are their parents. And I want, for example, all the docs that have the keyword FOO and their parent is "Reports". That's messy - iterate through all the folders to find the stuff I need. > > Duplicated data will require more accesses, code complication and risk > than "to join this and that". > >> Now, I know there are some disadvantages too, like having to worry >> whether I update the object and the table at the same time or so. >> I also have to make sure that all the data is in sync, since it's now >> duplicated. But I still can't resist wondering if this would be a good >> option. > > No. > >> Luckily I still have a couple of months work already planned, so I get >> to think about it :P > > Time isn't necessary, correct thinking is. > That's why I'm posting here, so you can do my thinking :) -- Zlatko
From: Martin Gregorie on 26 Apr 2010 15:13
On Mon, 26 Apr 2010 13:37:46 +0200, Zlatko Duric wrote: > > Well, my objects are documents - those are "nodes". But so are the > "folders" holding the documents. And so are their parents. And I want, > for example, all the docs that have the keyword FOO and their parent is > "Reports". > Thats what additional indexes are for. For example, to support the following example you'd want indexes on doc.keyword and folder.name, since they'll probably be used often for data selection and sorting. We'll assume that the DB designer was sensible and put indexes on both table's prime keys. Your example requirement could be satisfied with a single query, which would involve a prime key join and use the additional indexes to select the rows to be included in the dataset. Something like this: SELECT required fields from folder f, document d where f.key = d.folderkey and d.keyword = 'FOO' and f.name = 'Reports'; Any decent RDBMS should be able to optimise that type of query and would return just the required result set. > That's messy - iterate through all the folders to find the stuff I need. > Why would you need to do that? A correctly written query will only return the data you need, present it in the order you want and not slow database updates down by requiring additional reference tables which must be maintained. -- martin@ | Martin Gregorie gregorie. | Essex, UK org | |