SLOW performance on table with image fields (SQL 2000)

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • news.tele.dk
    #1

    SLOW performance on table with image fields (SQL 2000)

    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].[BIOMETRICPROFIL E] (
    [BIOMETRICPROFIL EID] [bigint] IDENTITY (1, 1) NOT NULL ,
    [FINGERPRINTTEMP LATE1] [image] NOT NULL ,
    [FINGERPRINTTEMP LATE2] [image] NOT NULL ,
    [FINGERPRINTTEMP LATE3] [image] NOT NULL ,
    [FINGERPRINTTEMP LATE4] [image] NOT NULL ,
    [FINGERPRINTTEMP LATE5] [image] NOT NULL ,
    [FINGERPRINTTEMP LATE6] [image] NOT NULL ,
    [TYPE] [nvarchar] (50) COLLATE Danish_Norwegia n_CI_AS NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    select * from BIOMETRICPROFIL E 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




    Tags: None
    • Ed Murphy
      #2
      Re: SLOW performance on table with image fields (SQL 2000)

      news.tele.dk wrote:
      CREATE TABLE [dbo].[BIOMETRICPROFIL E] (
      [BIOMETRICPROFIL EID] [bigint] IDENTITY (1, 1) NOT NULL ,
      [FINGERPRINTTEMP LATE1] [image] NOT NULL ,
      [FINGERPRINTTEMP LATE2] [image] NOT NULL ,
      [FINGERPRINTTEMP LATE3] [image] NOT NULL ,
      [FINGERPRINTTEMP LATE4] [image] NOT NULL ,
      [FINGERPRINTTEMP LATE5] [image] NOT NULL ,
      [FINGERPRINTTEMP LATE6] [image] NOT NULL ,
      [TYPE] [nvarchar] (50) COLLATE Danish_Norwegia n_CI_AS NOT NULL
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
      GO
      Zero-One-Infinity Rule
      http://www.catb.org/jargon/html/Z/Zero-One-Infinity-Rule.html

        Comment

        • news.tele.dk
          #3
          Re: SLOW performance on table with image fields (SQL 2000)

          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" <emurphy42@soca l.rr.comskrev i en meddelelse
          news:MuH6h.706$ Fg.524@tornado. socal.rr.com...
          news.tele.dk wrote:
          >
          >CREATE TABLE [dbo].[BIOMETRICPROFIL E] (
          > [BIOMETRICPROFIL EID] [bigint] IDENTITY (1, 1) NOT NULL ,
          > [FINGERPRINTTEMP LATE1] [image] NOT NULL ,
          > [FINGERPRINTTEMP LATE2] [image] NOT NULL ,
          > [FINGERPRINTTEMP LATE3] [image] NOT NULL ,
          > [FINGERPRINTTEMP LATE4] [image] NOT NULL ,
          > [FINGERPRINTTEMP LATE5] [image] NOT NULL ,
          > [FINGERPRINTTEMP LATE6] [image] NOT NULL ,
          > [TYPE] [nvarchar] (50) COLLATE Danish_Norwegia n_CI_AS NOT NULL
          >) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
          >GO
          >
          http://www.catb.org/jargon/html/Z/Ze...nity-Rule.html

            Comment

            Previous template Next
            Working...