Post #344,064
6/22/11 11:41:29 PM
|
Re: I wasn't expecting that trend, either.
That second point about the app knowing why and the DB only knowing that it happened is a great one.
Referential integrity in the database is a good idea even if you have it in the app, from both a belt and suspenders point of view as well as supporting automated tools. If you've ever tried to reverse engineer a database with hundreds of tables and no RI you'll know what I mean. Referential integrity is important meta data as well as a means of data validation.
I don't work on one-app databases that often so my experience and preferences are heavily slanted towards risk management and maintenance in large, complex environments.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
|
Post #344,068
6/23/11 5:17:43 AM
|
I think I know what the problem is
I'm looking at a ton of shit code, and it's duplicating functionality all over the place that does the same thing multiple shitty ways. I know that it can be done via stored procedures, which would keep developers from having to touch it. "But who would write the procs?" you say. Yeah ... good question.
How about this, then, since it's my other big gripe.
We've got two different apps that have home-grown grid controls. Both are based on pulling back large datasets, everything available to a user in a specific context. Then the app code handles paging, sorting and filtering.
I've worked on systems with more data and more users and we had consistently faster page loads than I see on this system. We did all the paging/sorting/filtering in SQL.
Is this a case where performance sucks because we shouldn't be trying to do that, or just another case that the current implementation sucks?
--
Drew
|
Post #344,074
6/23/11 5:51:15 AM
|
Paging is tricky.
I've found you need to do two things: push to the database what is easier to do in the database (filtering and sorting, usually), and don't let the DB or ORM layers buffer if you don't need it.
My expertise here is web servers, specifically PHP on MySQL. MySQL does filtering, sorting and paging faster and more efficiently than in the PHP, so I expose these controls through the object interface. They get converted into the right SQL clauses as required. I also use an unbuffered query whenever I can get away with it and make it easy for the app to pull one row at a time (a generator, basically). I've seen this reduce memory usage by orders of magnitude and speed to increase substantially.
I think this approach is kind of halfway between your two extremes.
Wade.
|
Post #344,089
6/23/11 11:54:11 AM
|
Re: I think I know what the problem is
Paging, sorting, and filtering are what relational databases are there for in general, beyond simple persistent storage. Because you're using a portable API (SQL), the actual code controlling what the sort is, what the filter is, etc. is kept out in the app tier so most if not all of my issues with business logic don't apply.
There's an issue with scaling if you're not using read-only shards. Depending on the use cases you'll also want to investigate caching.
Also depending on the use cases (do they involve extremely complex filtering or reduction logic) you might be better off moving out of the database. The key is to keep it modularized to watch your separation of concerns.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
|
Post #344,090
6/23/11 12:40:59 PM
|
Grid views, basically
Customer (or vendor) gets a view of all current orders. They want sorting and filtering by clicking on column headings. It's trivially easy to create a control in Visual Studio that does a single query, then keeps everything in memory doing sorts, filters and paging on that data set.
Whether that's a good thing to do is the question. We just had a meeting on this and two of us insisted that we should be hitting the DB for every sort and filter operation. I can live with keeping it in memory for paging, because it avoids the pathological case: User is on page 2, a record is at the top of page 3. Before they click "next" someone clears an item on page 1 or 2. Now I'm on page 3 and I never saw that record, which is now on page 2.
Maybe that's not worth having a different mechanism for paging than for sorting/filtering. Maybe I'm just trying to find a middle ground between what we have and what I think we need, so there's less resistance to doing it.
--
Drew
|
Post #344,092
6/23/11 12:52:32 PM
|
Re: Grid views -- thoughts
My answer is "it depends". What's the size of the data? Is it enough to go to the browser and allow it to store/sort it (usually not).
Do you have enough memory to store it at the web server layer? (Sometimes yes, sometimes no)
Do you have an N tier? An Application Server between the Web Server and the DB that can store it there?
Finally, then look at storing it in the DB. Is the data static or changing? Dynamic data might force you to keep a cursor open (Not good). Static (or near static) and you can pull down the set of rows you need by providing a start/stop/ordering-filtering conditions.
|
Post #344,073
6/23/11 5:44:21 AM
|
And ORMs have gotten better, too.
When I first encountered the concept, the app in question had two completely bespoke ORMs, side-by-side (yeah, PHP is often like that). Next job, same situation. Only the ORM was basically non-existent. So I got to write one... I learnt a lot about scaling that time. We probably could have done RI in the database; I chose not to because my lead programmer had a habit of falling into "I have a hammer" thinking. :-/ He did less damage when I corralled him with my APIs. I don't work there anymore.
I think, too, that implementing RI in MySQL has gotten easier over major versions. ISTR that this wasn't the case a decade ago when I first tried it. That helps a lot when the app is still really looking after it. And I like challenging the assumptions of Java programmers I get to work with. :-)
And I agree that referential integrity is definitely useful in a DB that runs behind multiple apps at once.
Wade.
|
