Update Table in diff DB, same server

  • 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

    http://www.embarcgroup.com



    Matthew Mamet

  • 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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • 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

    http://www.embarcgroup.com



    Matthew Mamet

  • 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.

  • 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

    http://www.embarcgroup.com


    Steve

  • 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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • 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

  • Not sure about that. In a limited test, it appears I get the same execution plan either way.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • 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