Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Help with hungry update

    I''ve got the following SP on a DB server, dual Xeon 2.8 with 4 gigs of ram, and a SAN of 20 15K disks, 2 sets of 10 striped. Yet, whenever I run it, it literally takes the lid off the server.. (totally locks it down, hogging all ressources)

    Code:
    UPDATE MATCH_basistabel 
    SET MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
    	MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnavn),
    	MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnavn2),
    	MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
    	MATCH_basistabel.nationalitet =  convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
    	MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisationsnummer),
    	MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
    	MATCH_basistabel.ipdn =  ALL_tbl_medlemsinfo.ipdn,
    	MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
    	MATCH_basistabel.franavision = 1
    FROM MATCH_basistabel, ALL_tbl_medlemsinfo 
    WHERE MATCH_basistabel.matchfelt = ALL_tbl_medlemsinfo.[søgenavn] COLLATE Latin1_General_CI_AI OR
    MATCH_basistabel.matchfelt = ALL_tbl_medlemsinfo.[medlemsnavn] COLLATE Latin1_General_CI_AI OR
    MATCH_basistabel.matchfelt = ALL_tbl_medlemsinfo.[medlemsnavn2] COLLATE Latin1_General_CI_AI
    Sure, there where clauses are heavy, but taking the lid off my server, doing a compare of 3000 rows, shouldn't be?

    The following indexes exist on match_basistabel
    Code:
     CREATE  INDEX [IDX_match_basistabel_matchfelt] ON [dbo].[MATCH_basistabel]([matchfelt]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_søgenavn] ON [dbo].[MATCH_basistabel]([søgenavn]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_medlemsnavn] ON [dbo].[MATCH_basistabel]([medlemsnavn]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_medlemsnavn2] ON [dbo].[MATCH_basistabel]([medlemsnavn2]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_ipdn] ON [dbo].[MATCH_basistabel]([ipdn]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_franavision] ON [dbo].[MATCH_basistabel]([franavision]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_match_basistabel_importkey] ON [dbo].[MATCH_basistabel]([importkey]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    Whilst these exist on the ALL_tbl_medlemsinfo:

    Code:
     CREATE  CLUSTERED  INDEX [IDX_all_tbl_medlemsinfo_mednr] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnummer]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_soege] ON [ket].[ALL_tbl_medlemsinfo]([søgenavn]) ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_nat] ON [ket].[ALL_tbl_medlemsinfo]([nationalitet]) ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_orgnr] ON [ket].[ALL_tbl_medlemsinfo]([organisationsnummer]) ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_ipdn] ON [ket].[ALL_tbl_medlemsinfo]([ipdn]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_mnavn] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnavn]) WITH  STATISTICS_NORECOMPUTE  ON [PRIMARY]
    GO
    
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_mnavn2] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnavn2]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    Given the stored procedure and the indexes, shoudln't the update be fairly light on the machine? or do the collates really kill the ressources?

    Thanks for any help / advice, Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    table MATCH_basistabel have too many indexes.That will affect the perfomance when u try to update or insert record.My suggestion based on ur update query is,create appropraite indexes.
    Code:
    CREATE  INDEX [IDX_match_basistabel_matchfelt] ON [dbo].[MATCH_basistabel]([matchfelt]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
     
     
    CREATE  INDEX [IDX_all_tbl_medlemsinfo_soege] ON [ket].[ALL_tbl_medlemsinfo]([søgenavn]) ON [PRIMARY]
    GO
     
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_mnavn] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnavn]) WITH  STATISTICS_NORECOMPUTE  ON [PRIMARY]
    GO
     
     CREATE  INDEX [IDX_all_tbl_medlemsinfo_mnavn2] ON [ket].[ALL_tbl_medlemsinfo]([medlemsnavn2]) WITH  FILLFACTOR = 80 ON [PRIMARY]
    GO
    drop other indexes from those tables
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    That would kill off performance on other queries run against the table though, and how can 3 indexes be the cause for brinding down an intere sever in ressources?

    Hmmm.. I was thinking about a lower fill factor on the secondary indexes, which would improve the page write performance during update, so that the indexes don't have to recompute..

    or entirely drop the index for the non-vital index during the update, and then recompute the lot after its done?

    /TRIN

    EDIT:

    Just recreated the query with parameters ensuring zero match... as indexes should only slow down / be a performance issue during updates, its certainly the where clauses being the killer.

    According to the execution plan they're worth 94% of the performance bomb in the query, so I'd love any suggestions on how to lighten the where clauses.

    Would changing the MATCH_basistabel `/ ALL_tbl_medlensinfo collation default help?
    Last edited by Trinsan; 12-08-05 at 09:30.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What about splitting the update into three?

    Code:
     UPDATE MATCH_basistabel 
    SET MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),  
    	MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav  n),
    	MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav  n2),
    	MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
    	MATCH_basistabel.nationalitet =  convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
    	MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisati  onsnummer),
    	MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
    	MATCH_basistabel.ipdn =  ALL_tbl_medlemsinfo.ipdn,
    	MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
    	MATCH_basistabel.franavision = 1
    FROM MATCH_basistabel INNER JOIN ALL_tbl_medlemsinfo 
    ON MATCH_basistabel.matchfelt = ALL_tbl_medlemsinfo.[søgenavn] COLLATE Latin1_General_CI_AI
    and repeated for the other two conditions.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    That might be a way.

    correction: It IS the way I'm happy now... it simply races through with the update... takes on a fraction of the time doing 3 independant updates....

    Thanks mate... thats about the 24th time you haul me out of a snag... you're gold!

    /Trin
    Last edited by Trinsan; 12-08-05 at 10:29.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

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