Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: Very Slow running Update query Query

    I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on

    the query thats causing teh problem is

    Code:
    --//////////////////////////////////// this is the one thats running
    
    
    Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
    GO
    Update Provider_APC_2004_05
    	set EmAdmsCount12mths = 
    (Select COUNT(*)-1
    from Combined_Admissions
    where  ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or 
    	 (Combined_Admissions.PASNUMBER =  Provider_APC_2004_05.PDDISTNO)) and
    	 (Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
    	  Combined_Admissions.AdmMethod like 'Emergency%')-- and
    --	  CA.NHSorPrivate = 'NHS'))
    FROM Provider_APC_2004_05, Combined_Admissions
    any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one

    Dave

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd probably work on the cartesian join between _APC_2004_05 and Combined_Admissions... That's likely to cause significant problems all by itself!

    After you get that fixed, I'd look at eliminating the OR clause within the correlated sub-query. I don't imagine that performs very well either.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Thanks Pat,

    All i did was ammend an update query from BOL
    the OR is a necessary evil im afraid as we had to check out individual patients and there are 2 unique identifiers for them nhsno. and PatID each indvidual patient could have 0,1 or both of these records varying between each field so to get this as accurate as possible the or is required(tell me if my logics wrong)
    ill look at the cartesian join (I now know what that is) and see what i can come up with to replace this

    Again Thanks
    Dave

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't need to replace the cartesian join. Just dump it.

    "Combined_Admissions" Serves absolutely no purpose in the outer from clause. You are not updating it or joining to it. Your join is being performed in your subquery instead. So unless MSSQL's query parser is smart enough to ignore this table, then this would probably work faster right off the bat:

    Update Provider_APC_2004_05
    set EmAdmsCount12mths =
    (Select COUNT(*)-1
    from Combined_Admissions
    where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
    (Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
    (Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
    Combined_Admissions.AdmMethod like 'Emergency%')-- and
    -- CA.NHSorPrivate = 'NHS'))


    But I suspect you'd get more speed without using a correlated subquery in your SET clause, which might cause the statement to be executed once for every single row in your update table. Try this syntax instead, replacing PKey with the Primary Key of your update table:

    update Provider_APC_2004_05
    Set EmAdmsCount12mths = Subtotals.EmAdmsCount12mths
    from Provider_APC_2004_05
    inner join
    (Select Provider_APC_2004_05.PKey, Count(*)-1 as EmAdmsCount12mths
    from EmAdmsCount12mths
    inner join Combined_Admissions
    on Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber
    or Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO
    where Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate)
    AND Provider_APC_2004_05.AdmDate
    and Combined_Admissions.AdmMethod like 'Emergency%') Subtotals
    on Provider_APC_2004_05.PKey = Subtotals.Pkey

    You may also get more efficiency by breaking the process into steps, such as loading the results of the subquery into a temporary table and/or running your updates in smaller batches.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Cheers Blindman ill try and run this as soon as possible, The servers is down for maintainance at the mo so will have a look at some point next week,

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think Fergus is a great name for a cat.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Thanks Guys,
    Just managed to have a look at this yesterday,
    the real problem was the OR statement dumping this trduced the time of the query to abt 1 minute, so my boss told me to ditch the pasnumber field and we would have to accrpt the errors, oh the joys of incomplete datasets,

    and Blindman i think fergus is a lovable rogue and his name really suits him

    Thanks again
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •