Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    19

    Question Unanswered: UPDATE QUERY TAKES MORE THAN 5 Hrs.

    Hi All,
    The following update query has taken more time to execute. It affects other queries execution time.

    Table Name : BILL
    Row Count : 6055555
    Data Space : 3,492.297 MB

    UPDATE BILL
    set COUNTING = case when BILL_AMT =0 AND bill_amt_aug=0 AND bill_amt_july =0 then 0
    when BILL_AMT <>0 AND bill_amt_aug=0 AND bill_amt_july =0 then 1
    when BILL_AMT =0 AND bill_amt_aug=0 AND bill_amt_july <>0 then 1
    when BILL_AMT =0 AND bill_amt_aug<>0 AND bill_amt_july =0 then 1
    when BILL_AMT <>0 AND bill_amt_aug<>0 AND bill_amt_july =0 then 2
    when BILL_AMT <>0 AND bill_amt_aug=0 AND bill_amt_july <>0 then 2
    when BILL_AMT =0 AND bill_amt_aug<>0 AND bill_amt_july <>0 then 2
    when BILL_AMT <>0 AND bill_amt_aug<>0 AND bill_amt_july <>0 then 3
    END



    TABLE DESIGN:
    CREATE TABLE [dbo].[BILL](
    [MKTCODE] [nvarchar](255) NULL,
    [BILLED_EXTERNAL_ID] [nvarchar](255) NULL,
    [Value_Type] [nvarchar](255) NULL,
    [Vip_Flag] [nvarchar](255) NULL,
    [Payment_Mode] [nvarchar](255) NULL,
    [Buisness_Unit] [nvarchar](255) NULL,
    [Customer_Type] [nvarchar](255) NULL,
    [Customer_Classification] [nvarchar](255) NULL,
    [Product_Type] [nvarchar](255) NULL,
    [Vertical_Segment] [nvarchar](255) NULL,
    [Risk_Profile] [nvarchar](255) NULL,
    [BILL_PLAN] [nvarchar](255) NULL,
    [BILL_PERIOD] [nvarchar](255) NULL,
    [AIRTIME] [float] NULL,
    [VAS] [float] NULL,
    [PSTN] [float] NULL,
    [RC] [float] NULL,
    [NRC] [float] NULL,
    [ROAMING] [float] NULL,
    [DISCOUNT] [float] NULL,
    [ADJ] [float] NULL,
    [TAX] [float] NULL,
    [BILL_AMT] [float] NULL,
    [PREP_DATE] [datetime] NULL,
    [STATEMENT_DATE] [datetime] NULL,
    [BILL_REF_NO] [nvarchar](255) NULL,
    [CONCATENATEFIELD] [varchar](255) NULL,
    [ACCOUNT_CATEG] [varchar](255) NULL,
    [counting] [numeric](25, 0) NULL,
    [active_date] [date] NULL,
    [bill_amt_aug] [float] NULL,
    [bill_amt_july] [float] NULL,
    [bill_amt_june] [float] NULL,
    [bill_amt_may] [float] NULL,
    [bill_amt_apr] [float] NULL,
    [total] [float] NULL,
    [average] [float] NULL,
    [AON] [numeric](18, 0) NULL,
    [TOTAL_3] [float] NULL,
    [AVERAGE_3] [float] NULL
    ) ON [PRIMARY]


    Thanks
    GA

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    From the preponderance of nvarchar(255) fields in this table, it looks like this was originally created in Microsoft Access and exported to SQL Server will little consideration to reasonable table design.

    I see a field labeled as a "flag" field, which could presumably by a bit field, but which is configured as a nvarchar(255) field.

    I have no idea how a cleanup of this table design would impact performance, but when I see something obviously wrong, like the design of this table, that would be my first area of investigation.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I agree with PracticalProgram; no Primary Key, all optional columns, lots of FLOATs, NVARCHR(255), ... a blind copy of an Access table to MSSQL is very likely.

    If you can clean up the structure of this table, it will become a lot smaller. Smaller meaning less pages that have to be swapped in and out, meaning faster access to the table.

    Also, the UPDATE will each time it runs update 6 000 000 records. Is that necessary?

    Perhaps you could consider partitioning.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    A computed column would be preferable in any event.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If this was generated by access, then there is a fair likelyhood that there is some Access front-end application trying to get this data, and holding a shared lock. Personally, I would expect the transaction log to blow out before 5 hours, but disk is getting cheap, these days.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pity we can't add bit values, or this would be even simpler...

    Code:
    
    update	BILL
    set	COUNTING = 
    	convert(smallint, convert(bit, BILL_AMT))
    	+ convert(smallint, convert(bit, bill_amt_aug))
    	+ convert(smallint, convert(bit, bill_amt_july))
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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