Have you compared the query plan from both servers? Would you post them?

-Mike

>Can anyone help me?
>
>I have a table by the name of MATERIALMST in SQL-Server 7.0 as well as 2000. This table contains approx. just 16000 records. Primary Key is set to the first three columns under both SQL 7.0 and SQL 2000.
>
>When I fire the query
>SELECT CODE,NAME,STOCK_UNIT FROM MATERIALMST
>WHERE
>CODE
>IN('AB0001','AB0002',.... about 1000 codes) AND CO+DIV =' '
>
>on SQL 7.0, this query gets executed in flat 9 secs. But under SQL-Server 2000 this same query takes around 6-7 minutes. The primary key index in the same in both SQL 7.0 and SQL 2000. The structure of the table is as follows:
>
>CREATE TABLE [dbo].[MATERIALMST] (
> [co] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [div] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ven_prt_no] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [val_method] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stock_unit] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ndecimal] [smallint] NULL ,
> [purch_rate] [float] NULL ,
> [sales_rate] [float] NULL ,
> [mrp_rate] [float] NULL ,
> [mrp_percent] [float] NULL ,
> [exc_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pack_size] [int] NULL ,
> [sum_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [gl_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [cost_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [anyl_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pursum_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [purgl_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bssum_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [bsgl_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [plsum_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [plgl_code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [updated] [smallint] NULL ,
> [zero_date] [datetime] NULL ,
> [tolerence] [float] NULL ,
> [lastuserid] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lastdt] [datetime] NULL ,
> [terminator] [smallint] NULL
>) ON [PRIMARY]
>GO