November 26, 2008 at 3:48 am
Im moving a database from Access to SQL server for a call centre environment for about 40 users, each updateing the database about twice per minute. Each user calls a subset of the customer table and records call status (No Answer/Callback/Sale etc etc), along with user name, date, time of call. The customers that havnt been contacted are called again the next day and so on until they are contacted
The databae has 2 tables: a customer table and a calls table,
I want a live dashboard style feature to see how many sales each user has so far for the day requiring an aggregate query. Relationally i should add a record to the calls table each time a customer is called but Im considering storing the calls for the day in extra fields in the customers table, and this data would be archived to the calls table nightly. Would this improve performance?
Whats your ideas. Im using an Access Project as the front end.
November 26, 2008 at 5:41 am
It really depends on your business needs. If you need to keep a history of the individual call itself, your method won't work. If all you care about is the aggregation, something along those lines should be fine. However, wouldn't you want to, instead of tracking calls per customer, track calls per salesperson? I mean, you won't have one customer getting five sales calls, but you will have one salesperson calling five customers, no? With that in mind, you'd want to aggregate, not within the customer table, but within a table not yet in evidence, the salesperson table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 26, 2008 at 10:03 am
Grant Fritchey (11/26/2008)
..I mean, you won't have one customer getting five sales calls, but you will have one salesperson calling five customers, no?...
Actually I could see it going either way. When they transpose the information nightly to the calls table, each call record may have more information about what customer they are communicating with than which person at their call center is on the call. Since each customer probably only has a limmited number of contacts per day, he wouldn't have to add many multiple copies of the call columns. If they stored all the calls per day for the salesperson together then it would make the dashboard report somewhat easier, but could require a very large number of columns in the table.
Since the data model has only has 2 tables, I don't really see how doing this kind of thing would increase performance. Writing the dashboard report with all the calls in the customer table would require some manipulation to group the calls by the salesperson, especially if the potential exists for multiple salespeople to call the same customer on the same day. Personally I don't think 40 users doing 2 inserts or updates per minute would really have much impact if all the records were going directly into the calls table, beacuse each salesperson would only be modifying one call reecord at a time, so there shouldn't be much lock escalation to page locks and blocking occuring.
November 26, 2008 at 10:46 am
Still, if it came down to it, I'd rather see inplace updates generating the aggregates than a series of columns going across a row. That's just horribly sloppy design. Again, for me, it comes down to the business requirements, but I don't find that skipping relational design translates to application speed. Development speed, short term, yeah, but not application speed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply