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.
| posted on Thursday, January 29, 2004 1:07 PM