Expansion Diary Part 4
Work Queues
No matter how efficient your TSQL, of course the application's method of using your data can make all the difference. We're finding horrible inefficiencies in our application logic, and making some nice improvements along the way. Our Work Queue system is a prime candidate for re-thinking.
A Work Queue is a named set of data, such as "Leads Not Contacted In The Last 7 Days," or "Contracts Out," or "Waiting on Paperwork." Things like that.
On any given page, we might want to show a grid of Work Queues in a nice table showing each Queue's name, and the # of records in the Queue. You can click through and "enter" the queue. This brings you to a frameset which allows you to page through the Queue with "Next" and "Previous" options, or list the Queue, go to a specific record, etc.
The problem with Queues is twofold: 1) Generating all the SQL required for each Queue, and 2) Actually using the Queue. Let's take each in turn:
1) Generating the SQL required for each Queue. For any given Queue, we have to show the total number of records in the Queue, and the number that "Need Work Today" (based on a follow-up date). Further complicating matters is that each Queue may need to show those numbers only for the currently logged in "rep" or some such thing like that. A Queue typically has 20 columns of information to be viewed when you "list" the Queue. That's a lot of variations for each Queue.
2) Using the Queue. Users typically want to page forward and backward through the Queue, list it out, and sort it's columns. Often, they list it, sort it, scroll to the record they are looking for, and pull up a record. Then, the repeat, to find another record. Instead of "searching." Users are users, after all... Complicating this, is the fact that some Queues are quite large, many with hundreds, some with thousands of records.
Complicating matters further, we have a semi-religious attachment to Stored Procedures versus dynamic SQL.
I'm looking hard to find the "best way" to handle this, and I'm not happy with any of the solutions considered thus far. If any of you have ideas or experiences in this sort of thing, please share your thoughts.
Legacy Comments
|
Mike Swaim
2004-01-29 |
re: Expansion Diary Part 4 I'm guessing that you're generating the SQL for each work queue on the fly based on the user selecting from some set of parameters. A problem with this approach is that the server has to generate a new plan for each query. Here's something that we did at a company that I worked for several years ago; we built an "engine" out of a set of stored procedures. We would write our parameters into a set of tables and call the first procedure. It would do a select into a temporary table, and then call the second procedure. That procedure would select into a second temp table, and then call a third procedure, which would reuse the first temp table, and so forth. This worked really well for us, and was MUCH faster than generated SQL. Also, as a side effect, we could easily have named queries for the ones that users often executed. -Mike |
|
Mike Swaim
2004-01-29 |
re: Expansion Diary Part 4 Let me clarify something. In the engine, each SP would do a select based on the previous one's results. So you'd start out with a large result set, and quickly winnow the results down to what you wanted. -Mike |