SLOW performance on table with image fields (SQL 2000)

[news.tele.dk]

Hi

We have a SQL server 2000 SP4 on a windows 2003 2x3Ghz XEON 4 GB ram.

We have a table looking like this with currently 6 rows. Total data is aprox
10 kb i all row all together.

CREATE TABLE [dbo].[BIOMETRICPROFILE] (
[BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,
[FINGERPRINTTEMPLATE1] [image] NOT NULL ,
[FINGERPRINTTEMPLATE2] [image] NOT NULL ,
[FINGERPRINTTEMPLATE3] [image] NOT NULL ,
[FINGERPRINTTEMPLATE4] [image] NOT NULL ,
[FINGERPRINTTEMPLATE5] [image] NOT NULL ,
[FINGERPRINTTEMPLATE6] [image] NOT NULL ,
[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

select * from BIOMETRICPROFILE takes ~4 seconds (!) to execute thourgh Query
analyzer. Alle other tables has no performance problems.

We have a SQL 2005 express instalation on the same server. If we restore a
backup from the sql 2000 database the query takes aprox ~ 15 ms.What is
going on here?

Has SQL 2000 problems with image fields? or how can we find the problem?

Regards
Anders

[Ed Murphy]

news.tele.dk wrote:

CREATE TABLE [dbo].[BIOMETRICPROFILE] (
[BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,
[FINGERPRINTTEMPLATE1] [image] NOT NULL ,
[FINGERPRINTTEMPLATE2] [image] NOT NULL ,
[FINGERPRINTTEMPLATE3] [image] NOT NULL ,
[FINGERPRINTTEMPLATE4] [image] NOT NULL ,
[FINGERPRINTTEMPLATE5] [image] NOT NULL ,
[FINGERPRINTTEMPLATE6] [image] NOT NULL ,
[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

http://www.catb.org/jargon/html/Z/Ze...nity-Rule.html

[news.tele.dk]

Your answere indicates that the image type is "randomly large" so to much
data is retrieved. Is image the wrong field type then? My binary data is
beteween 1-200kb large per field but the current performance is not an
option.

And why is sql 2005 working whitout problems?

Can you please give me some more details. Thanks in regards

Anders

"Ed Murphy" <em*******@socal.rr.comskrev i en meddelelse
news:Mu**************@tornado.socal.rr.com...

news.tele.dk wrote:

>CREATE TABLE [dbo].[BIOMETRICPROFILE] (
[BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,
[FINGERPRINTTEMPLATE1] [image] NOT NULL ,
[FINGERPRINTTEMPLATE2] [image] NOT NULL ,
[FINGERPRINTTEMPLATE3] [image] NOT NULL ,
[FINGERPRINTTEMPLATE4] [image] NOT NULL ,
[FINGERPRINTTEMPLATE5] [image] NOT NULL ,
[FINGERPRINTTEMPLATE6] [image] NOT NULL ,
[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

http://www.catb.org/jargon/html/Z/Ze...nity-Rule.html