Re: sp_tableoption 'MyTable2','text in row','5000'

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Leila (leilas_at_hotpop.com)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 15:32:56 +0430

Thank you very much Peter!
But why the IO for the second table is considerably more?

"Peter The Spate" <anonymous@discussions.microsoft.com> wrote in message
news:42d101c4a484$21e635b0$a601280a@phx.gbl...
> Firstly if you have not read it, check
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/createdb/cm_8_des_04_0x5t.asp, and pay
> special attention to the section 'Effects of the text in
> row Option'
>
> Now here is why the first option is overall slower than
> the first.
>
> As the first option does not have the Text in Row option
> set, your pics are not stored in the data page, but
> instead a pointer is used to where its stored. So the
> action is.
> 1. Find the Row
> 2. Find the pointer
> 3. Go to the location on disk where the pic is
> 4. Retrieve the pic
> 5. Send the pic back.
>
> With the second option, the pic is actually saved in the
> row, so the steps are
> 1. Find the row
> 2. Find the column
> 3. Return it.
>
> As you can see its two less steps, which is why the second
> option takes less time.
>
> As for recommendations, personally I would go for one with
> less overall time.
>
> BTW, have a look at the overall table sizes.
>
> Peter
>
> >-----Original Message-----
> >Hi,
> >I have a table with some varchar fields and a picture
> field of type image. I
> >created the second table exactly similar to first table
> but enabled 'text in
> >row' option:
> >
> >sp_tableoption 'MyTable2','text in row','5000'
> >
> >Then inserted all of records from MyTable1 into MyTable2.
> All of pictures
> >inserted in these two tables are exactly the same with
> the size of 4.5KB.
> >When I select all of the records from MyTable1 and
> MyTable2, the statistics
> >io/time is like this (I use DBCC DROPCLEANBUFFERS and
> DBCC FREEPROCCACHE
> >each time before executing the query):
> >----------------------
> >(select * from mytable1):
> >
> >(3337 row(s) affected)
> >
> >Table 'MyTable1'. Scan count 1, logical reads 84,
> physical reads 1,
> >read-ahead reads 83.
> >
> >SQL Server Execution Times:
> > CPU time = 50 ms, elapsed time = 17320 ms.
> >------------------------
> >(select * from mytable2):
> >
> >(3337 row(s) affected)
> >
> >Table 'MyTable2'. Scan count 1, logical reads 3339,
> physical reads 2,
> >read-ahead reads 3342.
> >
> >SQL Server Execution Times:
> > CPU time = 240 ms, elapsed time = 1239 ms.
> >------------------------
> >I'm really confused. The query on first table has less IO
> but it takes about
> >17 seconds to complete. The second table although it has
> much more IO, but
> >it is very fast.
> >Assuming that always my pictures are not more than 5KB,
> is the 'text in row'
> >option recommended for my case? (As you know, I'm
> responsible for fast
> >queris to my boss!!)
> >Any help would be greatly appreciated,
> >Leila
> >
> >
> >.
> >

Relevant Pages

  • Re: char** function parameters
    ... A picture might also help: ... but this pointer is not stored in memory anywhere, ... You need to make sure the arrow points in turn to some other thing ...
    (comp.lang.c)
  • Re: Using the Marquee Tool
    ... When I use the Marquee Tool to frame a picture segment for copying, ... the mouse pointer is a little 'cross'. ... hit the picture at approximately the right starting point, ... mouse movements will move the whole selection. ...
    (comp.graphics.apps.photoshop)
  • Re: Using the Marquee Tool
    ... When I use the Marquee Tool to frame a picture segment for copying, ... the mouse pointer is a little 'cross'. ... hit the picture at approximately the right starting point, ... mouse movements will move the whole selection. ...
    (comp.graphics.apps.photoshop)
  • Re: Unknown pointer problem between Visual Basic and C
    ... Draw into a picture area. ... except drawing from Visual Basic (HDC seems to be wrong). ... I send to the "Visual Basic wrapper" built in C, dimensions and DC pointer ...
    (microsoft.public.win32.programmer.gdi)
  • Re: MS Paint Zoom
    ... picture in MS Paint rather than just a corner of it? ... MS Paint is a simple graphic editor. ... the mouse pointer into the picture. ... and horizontal double head pointer. ...
    (microsoft.public.windowsxp.help_and_support)