I think I know what the problem is

Post #344,068 by drook 6/23/11 5:17:43 AM Reply	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 by static 6/23/11 5:51:15 AM Reply	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. Static Scribblings http://staticsan.blogspot.com/
Post #344,089 by malraux 6/23/11 11:54:11 AM Reply	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 by drook 6/23/11 12:40:59 PM Reply	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 by S1mon_Jester 6/23/11 12:52:32 PM Reply	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.

Welcome to IWETHEY!