HelloIm working on a call centre application. Primarily there are 2 main tables, customer and calls. The customer table holds the customer detail records and the calls table has a record for each customer contact (CallDate, CallTime, CallOutcome - this code is a number between 1 and 30). In my situation, a customer will only be contacted once per day, and there will be continued contacts until the outcome code falls below 20.Obviously, the last contact, ie the record in the calls table with largest date, will be of most importance for many of the queries i run. A lot of the time ill have to pull a list of the customer records with the most recent contact. The below query will do this ok, but it runs quite slow.Simply, should i include an extra field in both the calls and contacts table called CallNo. On adding a contact the client application will increased the customer.CallNo field by 1, and in the record added to the calls table the CallNo field will be set to equal this.Then i can simply link the master.primarykey = calls.foreignkey and Callno = CallNo.Is this a practical solution under the circumstances?Hope you can helpRegardsGarySELECT customer.ID, Calls.ID AS FinalCallID, Calls.CallDate AS FinalCallCallDate, Calls.CallTime AS FinalCallCallTime, Calls.TSR AS FinalCallTSR, Calls.CallOutcome AS FinalCallCallOutcome, customer.button, customer.TimeOfLastCallFROM dbo.Calls INNER JOIN (SELECT ID, MAX(CallDate) AS MaxCallDate FROM dbo.Calls GROUP BY ID) T ON dbo.Calls.ID = T.ID AND dbo.Calls.CallDate = T.MaxCallDate RIGHT OUTER JOIN dbo.customer ON T.ID = dbo.customer.ID
Should i change the table design for performance
Posted - 2009-01-06 : 08:38:44
