October 14, 2003 at 8:03 am
I'm running into major performance problems with the following statement:
UPDATE [WebDB].dbo.ACL
SETSendNews = Cust.SendNews,
FROM[EmailDB].dbo.Customer as Cust,
[WebDB].dbo.ACL as ACL
WHERECust.CustID = ACL.waCustID
AND Cust.SendNews <> ACL.SendNews
ANDCust.DateACLUpdated <= ACL.Updated
This statement runs in a stored proc on the [EmailDB] database and attempts to update a table in the [WebDB] database. The two databases are located on the same physical server. I am running Sql Server 7.0 on Windows 2000.
This statement is part of a larger proc that has no problem with performance. However, this is the only statement that attempts to update the table on the diff DB. All other statements update tables in EmailDB while simply JOINing to tables in WebDB.
The SendNews field is char(1) field that will contain either Y or N.
DateACLUpdate and Updated are datetime fields.
Display Estimated Execution plan shows 3 main areas using up peformance:
1. Clustered Index Scan on Cust.CustID = 21%
2. Clustered Index Scan on WebDB.ACL.waCustID = 32%
3. Hash Match / Inner Join CustID = waCustID= 47%
My question is simply - any ideas on making this faster?
Thanks,
Matthew
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet
October 14, 2003 at 10:54 am
Other than hardware, you might try running it in batches, so get a count and run 100 at a time or something. Sometimes that helps speed things up. Are there indexes on the SendNews fields? Not sure that it will help as the <> means you have to scan every row.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 14, 2003 at 12:30 pm
Thanks for your reply.
I'm not sure an index on SendNews would help, because it only contains 1 of 2 possible values ('Y' or 'N').
I guess this is the problem then, based on what you said. It's not necessarily the fact the tables are on separate DBs, but more that the Fields being joined on are just "not unique enough" causing a table scan on 2 large (800k+ records) tables.
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Matthew Mamet
October 15, 2003 at 3:04 am
I had a problem with performance accessing data using views from two databases. In the end I combined the two databases into one and the performance improved considerably.
This may not be a realistic solution for you, so you could try an alternative, such as replicating the table on WebDB, although it does seem a shame to do this when the data is immediately availble to you.
Also check that you don't have auto-close switched on in the EmailDB database, as this would cause a big delay if the database had closed before you ran your query.
And as Steve Jones said, Indexing the SendNews fields will also help, even for Y/N columns.
October 15, 2003 at 8:28 am
I have had similar issues when using <> operators when comparing character fields. I have had some pretty good luck using the charindex or patindex functions in place of the <>. So you might rewrite the following line
Cust.SendNews <> ACL.SendNews
like this
Patindex(Cust.SendNews,ACL.SendNews)<>0. In the cases that I made similar changes, I had around a 50% increase in performance.
quote:
I'm running into major performance problems with the following statement:UPDATE [WebDB].dbo.ACL
SETSendNews = Cust.SendNews,
FROM[EmailDB].dbo.Customer as Cust,
[WebDB].dbo.ACL as ACL
WHERECust.CustID = ACL.waCustID
AND Cust.SendNews <> ACL.SendNews
ANDCust.DateACLUpdated <= ACL.Updated
This statement runs in a stored proc on the [EmailDB] database and attempts to update a table in the [WebDB] database. The two databases are located on the same physical server. I am running Sql Server 7.0 on Windows 2000.
This statement is part of a larger proc that has no problem with performance. However, this is the only statement that attempts to update the table on the diff DB. All other statements update tables in EmailDB while simply JOINing to tables in WebDB.
The SendNews field is char(1) field that will contain either Y or N.
DateACLUpdate and Updated are datetime fields.
Display Estimated Execution plan shows 3 main areas using up peformance:
1. Clustered Index Scan on Cust.CustID = 21%
2. Clustered Index Scan on WebDB.ACL.waCustID = 32%
3. Hash Match / Inner Join CustID = waCustID= 47%
My question is simply - any ideas on making this faster?
Thanks,
Matthew
Sincerely,
Matthew Mamet
Web Developer
embarc LLC
Steve
October 15, 2003 at 10:41 am
what about two statements? One where the WHERE clause has
AND Cust.SendNews = 'Y'
AND ACL.SendNews = 'N'
and the other with the reverse? With an index on Sendnews, this may be faster.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 16, 2003 at 1:55 am
Sorry, but I am confused by your code. Maybe I am just used to a different syntax, maybe this is the reason for your problem: To me it seems you are joining [WebDB].dbo.ACL twice. How is this:
UPDATE ACL
SET ACL.SendNews = Cust.SendNews,
FROM [EmailDB].dbo.Customer as Cust,
[WebDB].dbo.ACL as ACL
WHERE Cust.CustID = ACL.waCustID
AND Cust.SendNews <> ACL.SendNews
AND Cust.DateACLUpdated <= ACL.Updated
In your original statement you used [WebDB].dbo.ACL twice (in UPDATE and in FROM) which makes me think you are updating the cross-product, which leads to the same result but takes square-times the time... or am I on the wrong track ??
Kay
October 16, 2003 at 10:01 am
Not sure about that. In a limited test, it appears I get the same execution plan either way.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 19, 2003 at 7:25 pm
Try each of the following. Let me know if either one has a significant speed improvement
UPDATE ACL
SET ACL.SendNews = Cust.SendNews
FROM [EmailDB].dbo.Customer as Cust INNER JOIN [WebDB].dbo.ACL as ACL
ON Cust.CustID = ACL.waCustID
WHERE Cust.SendNews <> ACL.SendNews
AND Cust.DateACLUpdated <= ACL.Updated
UPDATE ACL
SET ACL.SendNews = (SELECT Cust.SendNews
FROM [EmailDB].dbo.Customer as Cust
WHERE Cust.CustID = ACL.waCustID)
WHERE EXISTS (SELECT * FROM [EmailDB].dbo.Customer as Cust2
WHERE Cust2.CustID = ACL.waCustID
AND Cust2.SendNews <> ACL.SendNews
AND Cust2.DateACLUpdated <= ACL.Updated)
Roy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply