September 23, 2008 at 9:19 am
Hey guys
Im trying to update telephone numbers using the below script.
This thing perform's stupidly slow. I have read forum after forum, web page after web page and it's official. IM NOW IRRATATED WITH THIS SCRIPT!!!!
The pages i read say that its best to create a clustered index on fields that are used in the where clause, which i have done. The thing is that the where clause is a nested query that references the outer query.
It insists on doing a index scan on the fields that are ironically the ones in the where clause. How do i get the query to do a seek instead?
Script below..
UPDATE
A
SET
[Number1] =(SELECT TOP 1 T.TelephoneNumber
FROM
[Sys_ContactNumber] T (NOLOCK)
LEFT JOIN Sys_Tel_Selection_Matrix_Temp O (NOLOCK)
ON O.OrderNumber = T.LineStatusID
WHERE
T.ReferenceNo = A.AccountID
ORDER BY
O.Orderid ASC, COALESCE(T.SysDateUpdated, T.SysDateCreated) DESC)
FROM
Cam_CampaignAll A
WHERE
A.Number1 IS NULL
Any help would be most welcome.
By the way, im using SQL 2005.
Regards.
September 23, 2008 at 9:30 am
Can you post the exec plan please? (saved as a .sqlplan file, zipped and attached)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2008 at 12:00 am
Please find the attachment on the reply. Let me give you a little background on this.
I have a contact number table that has telephone numbers which apply to accounts in my Cam_CampaignAll table.
The likelyhood of there being more than one number per account is high. Each number has a status (valid, left message and so on.) The purpose of the script is to load the most recent number in in a pecking order, (That being status). I use the Sys_Tel_Selection_Matrix_Temp table to determine the status pecking order that the numer may be selected in.
Referenceno in the Sys_ContactNumber has a clustered index. I cant make it a primary key, it has duplicates as explained above.
AccountID in the Cam_CampaignAll table is a clustered index as well, i could make this a primary key. But doesnt eliminate the idex scan element.
thanx for the help so far.
September 25, 2008 at 6:42 am
I have found that sometimes it helps performance to break up a problem into smaller, easier to solve, sub-problems. Also, nested SELECTs that reference the outer SELECT can cause a major performance problem. That being said, here is how I would go about solving this. Since I have no data to work with, I have no idea if this actually works or not. 🙂
Scott
--Find the accounts that we care about
DECLARE @Accounts TABLE (AccountID int, Orderid int, SysDate datetime)
INSERT INTO @Accounts (AccountID)
SELECT C.AccountID
FROM Cam_CampaignAll C
WHERE C.Number1 IS NULL
--Find the OrderID we want (We want the best OrderID we can find regardless of date)
UPDATE A1
SET Orderid = x.MinOrderID
FROM @Accounts A1
INNER JOIN (
SELECT A.AccountID, MIN(O.Orderid) MinOrderID
FROM @Accounts A
INNER JOIN Sys_ContactNumber T ON A.AccountID = T.ReferenceNo
INNER JOIN Sys_Tel_Selection_Matrix_Temp O ON O.OrderNumber = T.LineStatusID
GROUP BY A.AccountID) x ON A1.AccountID = x.AccountID
--Find the Date we want (We want the latest date for the previously found OrderID)
UPDATE A1
SET SysDate = x.MaxSysDate
FROM @Accounts A1
INNER JOIN (
SELECT A.AccountID, MAX(COALESCE(T.SysDateUpdated, T.SysDateCreated)) MaxSysDate
FROM @Accounts A
INNER JOIN Sys_ContactNumber T ON A.AccountID = T.ReferenceNo
LEFT JOIN Sys_Tel_Selection_Matrix_Temp O ON O.OrderNumber = T.LineStatusID
AND O.Orderid = A.Orderid
GROUP BY A.AccountID) x ON A1.AccountID = x.AccountID
--Now we can update the number
UPDATE C
SET Number1 = T.TelephoneNumber
FROM Cam_CampaignAll C
INNER JOIN @Accounts A ON C.AccountID = A.AccountID
INNER JOIN Sys_ContactNumber T ON A.AccountID = T.ReferenceNo
AND ( T.SysDateUpdated = A.SysDate
OR T.SysDateCreated = A.SysDate)
LEFT JOIN Sys_Tel_Selection_Matrix_Temp O ON O.OrderNumber = T.LineStatusID
AND O.Orderid = A.Orderid
September 25, 2008 at 9:15 am
Mark,
First of all you should not create your clustered index by the fields in the where clause. You should create non-clustered indexes on the fields in the where clause. Your clustered index should be all small as possible. If you do not have a field that is small (8 bytes or less) then create an identity field and use that.
Second, in order to get the query to use an indexes make sure you have the following NON-CLUSTERED indexes:
1. On [Sys_ContactNumber] Using field Reference_No as the first field and LineStatusID as the second add 2 included columns SysDateUpdated and SysDateCreated into the non-clustered index. This is your most important index based on the information you have provided. If this does not provide a index seek then first update your statistics if that does not fix the problem then you probably do not have enough unique values in the AccountId field to justify a seek condition and the index scan may perform better than a seek.
2. Create a non-clustered index on Sys_Tel_Selection_Matrix_Temp using in order field OrderNumber with included column OrderID
3. Non-clustered Index Cam_CampaignAll using field Number1. Right now you have it as the clustered index.
Again each of these will perform faster if your clustered indexes are kept small. Identities and non-clustered indexes are wonderful things.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply