Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    1

    Post Unanswered: Collations Problem

    I have recently migrated a SQL Server 6.5 DB to SQL 2000.

    On a particular table i added a new varchar ( [field29] - see below)

    now when changing a record in this table, the performance is greatly reduced.
    In SQL Enterprise manager, doing a return all rows, and then amending a record here, i get the following message :

    "the entire resultset must be returned before this row can be updated. This operation is in progress and may take a long time due to the size of the result set".

    The table has 300,000 records. The update takes about 20secs.

    After this has completed, the performance is ok, as long as the window remains open. SQL Server memory also grows significantly. It appears that the entire recordset is cached.

    Is this related to Collations?
    ([Field1] is the Primary Key)

    any ideas?




    CREATE TABLE [dbo].[Tabletest]
    (
    [field1] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
    [field2] [smallint] NOT NULL ,
    [field3] [datetime] NOT NULL ,
    [field4] [datetime] NULL ,
    [field5] [datetime] NULL ,
    [field6] [datetime] NULL ,
    [field7] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
    [field8] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
    [field9] [varchar] (30) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
    [field10] [varchar] (250) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
    [field11] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
    [field12] [smallint] NOT NULL ,
    [field13] [varchar] (6) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
    [field15] [smallint] NULL ,
    [field16] [smallint] NULL ,
    [field17] [smallint] NULL ,
    [field18] [smallint] NULL ,
    [field19] [smallint] NULL ,
    [field20] [smallint] NULL ,
    [field21] [smallint] NULL ,
    [field22] [varchar] (60) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
    [field23] [smallint] NOT NULL ,
    [field24] [bit] NOT NULL ,
    [field25] [datetime] NULL ,
    [field26] [varchar] (9) COLLATE SQL_Latin1_General_CP850_CI_AS NULL ,
    [field27] [int] NOT NULL ,
    [field28] [smallint] NULL ,
    [field29] [varchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL
    )

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Use WHERE clause, you do not need to see 300,000 records when you are changing one

Posting Permissions

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