January 6, 2009 at 6:42 am
Hello
Im 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 help
Regards
Gary
SELECT 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.TimeOfLastCall
FROM 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
[\code]
January 6, 2009 at 8:37 am
One question, you wouldn't have an outer join to the customer table would you? You have to have a customer to have a call right?
If so, this should work just fine:
SELECT 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.TimeOfLastCall
FROM dbo.Customer c
JOIN dbo.Calls ca
ON c.ID = ca.ID
and ca.CallDate = (SELECT TOP(1) ca2.CallDate
FROM dbo.Calls ca2
WHERE ca2.ID = ca.ID
ORDER BY ca2.CallDate DESC)
I'd suggest that the clustered index on the Calls table be the ID & CallDate field.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 6, 2009 at 9:00 am
Hi Grant
I like your implementation, one question....
Can i have 2 fields as clustered indexes?
(Im using sql server 2000)
Regards
Gary
January 6, 2009 at 9:04 am
Sure, you can have two fields on the clustered index. I'm assuming the cluster is not the primary key, but you can have two fields on the key too.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 6, 2009 at 9:35 am
Super. Grant do you know any books where i can learn about writing optimized code to help with these situations?
January 6, 2009 at 9:43 am
Itzik Ben-Gan's books, Inside SQL Server 2005 T-SQL Querying and T-SQL Programming. Also the performance tuning one by Kalen Delaney. Joseph Sack's books are fantastic too.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 8, 2009 at 10:11 am
Instead of using sub query you can CTE.
Following is code,which will gives you better performance.
WITH Calls_CTE(ID,CallDate)
AS(
SELECT ID,MAX(CallDate) AS CallDate
FROM
dbo.calls
GROUP BY
ID
)
SELECT
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.TimeOfLastCall
FROM
dbo.Customer c
JOIN
dbo.Calls ca
ON
c.ID = ca.ID
JOIN
Calls_CTE AS CTE
ON
ca.ID = CTE.ID AND Ca.CalDate = CTE.CallDate
January 12, 2009 at 3:16 am
Hello
Thanks for this alternative method also, i can think of 2 stored procedures that will benifit from it.
Theres so many ways to do the same thing once youve learnt the syntax of SQL! It sometimes seems like a high mountain to climb to get to the stage where you can be confident the routines you write are anyway close to optimized.
Regards
Gary
January 12, 2009 at 3:25 am
I think ive just found a snag to the previous code
Im on SQL Server 2000 and i read CTEs are not supported?
From what ive read also, my first example using a derived table, is in essence the same as using CTE performance wise?
January 12, 2009 at 9:20 am
gary (1/12/2009)
I think ive just found a snag to the previous codeIm on SQL Server 2000 and i read CTEs are not supported?
From what ive read also, my first example using a derived table, is in essence the same as using CTE performance wise?
Yeah, a derived table is basically the same as a CTE (with some exceptions). That's what you will have to use in 2000.
Just a point of reference, you're posting to the 2005 forum, so you're likely to receive answers that are specific to 2005.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 13, 2009 at 1:04 pm
I have a similar situation as the OP and solved it by creating a calls_history table that has the same columns as the calls table. I then setup an ON INSERT TRIGGER to delete the current record from the calls table then insert the new record. In addition, the TRIGGER copies the newly inserted data into the calls_history table.
Essentially, what I am left with is a calls table that only contains the most recent record and a calls_history table that contains ALL (historical and current) records. When I need only the most recent record (which is usually the case), I join to the calls table. For reporting purposes I can use the calls_history table.
January 14, 2009 at 3:34 am
Hi
Thats an interesting take.
My situation is that previously we used an Access databases with 1 table that had fields for 3 calls, the database was split in 2 with up to 20 users in each, still there was often corruption and table locks.
I already moved to SQL a server database with a customers table and calls table. The customers table still has fields for one call, when the agent makes a call its recorded here (as i said theres only one call done per day). Then in the morning, before reports are run, those "call" fields are used to insert call records to the calls table. The "call" fields in customer are then set to null ready for the next days calling. This enables fast reporting during the day on agent performance, no joins while users are all logged on and doing up to 300 calls per day, was manageable to implement. (Access project front end)
I wanted the 2nd version to be fully rationailzed (was thinking of adding Phone Numbers table, SalesLeads table etc etc) but the more i look into it the more i think that i dont have the SQL experience to implement this in a live system, and that im better sticking to a design that, although a bit more cumbersome, i know i can manage, fulfills the job requirements and the performs well.
Going forward i think i may either..
a) Add a trigger to the customer table to add a record to the calls table everytime a call is recorded. This will save having to do the batch update every morning. This would also mean the last call is always in the customers table and most of my queries would just use the customers table with no joins.
b) Just perfomance tune the existing queries and build my skills until im ready to redo the design properly.
erm?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply