IDS Forum

Re: varchar performance problems

Posted By:
Date:

I know I'm getting in "late" on this thread, but I have been on vacation...

Mr. Roberts hits very accurately on the varchar issue with respect to
forward pointers. Volatile varchars have the potential of causing an
unneccesary row-split...as Bill points out, if a varchar column content is
increased, and the row will no longer fit into it's slot on the page, we
will put a 4-byte forward pointer in the front of slot, modify the slot
table high-order bit of the 2-byte size indicator (if I recall right - it's
either the size or the offset) to flag the fact that the first 4-bytes
needs to be interpreted as a forward pointer, and not data. (We "steal"
this digit away as we would never use it...on data pages it's to indicate
fwd ptrs, on index pages, to indicate a logically deleted slot (via the
delete flag)). We move the rest of the row (which could be the entire data
portion of the row) to a remainder page (which could house many remainders
from other rows). This page will have a 4-byte slot table entry for the
remainder portion of the row - this is normal...each row has a 4-byte slot
table entry. Thus, we have just added 8 bytes to the row size, when in fact
it might have been able to fit on a single page. (Rows longer than a page
can't avoid this overhead). And, the significant impact is two I/O's versus
one. '

Keep in mind that a varchar also has a 1-byte length byte at the front of
the row. A NULL varchar takes up 2 bytes of space....1 for the length byte
and 1 for the 0 in the slot.

Jack Parker also pointed out something I believe true, but have never
proven - that you can't get light scans on a varchar table. Anyone know
different?

Thanks,
Mark.


Mark Scranton
Principal Consultant/Teacher
IBM Denver

IBM Software Group - Data Management
Office: 303-773-5067
Cell: 303-929-0914
email: mscranto@us.ibm.com





"bill.robert...."
<bill.roberts@ver To: ids@iiug.org
izon.com> cc:
Sent by: Subject: Re: varchar performance problems [871]
forum.subscriber@
iiug.org


04/08/2003 12:33
PM







Clifton,
I advise listening to your DBA until (s)he proves themselves incompetent.

I discourage the use of VARCHAR, NVARCHAR, and "CHARACTER VARYING" data
types because disk is relatively cheap, many data "architects" don't
understand the implications of these data types, and developers have a
tendency to misuse them (I've actually had to defend changing schema
requests from VARCHAR(1) to CHAR(1)!!!).

The answer to the question "Do VARCHARS have a negative impact on
performance?" is:

"It depends" (credit again to Mark Scranton...or is it Dan Michaelis?).

Updates to data types can force migration of data to remainder pages. Not
only are you again using more disk than you anticipated, but updating
VARCHAR columns may result in excessive disk I/O for future reads because
the engine may find a forward pointer to a remainder page when it returns
to retrieve the data. The engine then performs additional disk I/O to read
the remainder page(s).

Review the schema of the table and ask yourself:
01. What has my DBA advised and why?
02. Why do rows in my table exceed a page in size?
03. Can some columns be moved to another table? (after all, we are
relational...IMNSHO parallel processing is better than being pointed
elsewhere...)
04. How often/likely am I to update this column?
05. At a later date might someone request that the data I'm storing in a
varying data type be indexed?

Regards,
Bill Roberts

AAIS Core Production Support, Informix DBA
Office : (813) 978-2340
Pager: (888) 423-6604
e-mail:bill.roberts@verizon.com

#####################################################
From http://publibfi.boulder.ibm.com/epubs/pdf/4364.pdf
#####################################################
Variable-Length Execution Time

When you use any of the CHARACTER VARYING(m,r), VARCHAR(m,r), or
NVARCHAR(m,r) data types, the rows of a table have a varying number of
bytes instead of a fixed number of bytes. The speed of database operations
is
affected when the rows of a table have a varying number of bytes.
Because more rows fit in a disk page, the database server can search the
table
with fewer disk operations than if the rows were of a fixed number of
bytes.
As a result, queries can execute more quickly. Insert and delete operations
can be a little quicker for the same reason.

When you update a row, the amount of work the database server must
perform depends on the number of bytes in the new row as compared with
the number of bytes in the old row. If the new row uses the same number of
bytes or fewer, the execution time is not significantly different than it
is with
fixed-length rows. However, if the new row requires a greater number of
bytes than the old one, the database server might have to perform several
times as many disk operations. Thus, updates of a table that use CHARACTER
VARYING(m,r), VARCHAR(m,r), or NVARCHAR(m,r) data can sometimes be
slower than updates of a fixed-length field. (NOTE: as well as reading the
data after the updates, but they don't mention it...WAR)

To mitigate this effect, specify r as a number of bytes that encompasses a
high
proportion of the data items. Then most rows use the reserve number of
bytes, and padding wastes only a little space. Updates are slow only when a
value that uses the reserve number of bytes is replaced with a value that
uses
more than the reserve number of bytes.





"Clifton M. Bean"

<cmbean@sbcglobal To: ids@iiug.org

.net> cc:

Sent by: Subject: Re: varchar
performance problems [868]
forum.subscriber@

iiug.org


04/08/03 11:50 AM







Varchars fields that will be or are part of an index is a BAD idea; other
than that, I don't recall any special problems with varchars. Review the
release notes of your engine to see if there are any bugs with varchars
that
may impact your system. You may even want to open a case with Informix
just
to have the engineer let you know the status of any bugs mentioned and
where
they are fixed so you can upgrade (if needed) before performing your data
conversion.

Take care.
Clifton

----- Original Message -----
From: "Bob Amy " <bamy@ssww.com>
To: <ids@iiug.org>
Sent: Tuesday, April 08, 2003 8:16 AM
Subject: varchar performance problems [863]


>
> My dba is telling me that theree are some performance problems with
> using varchars.
> We have a table that has 2133 bytes per row. This is over the 2K pages
> size, so I am suggesting that we convert several rarely used fields or
> rarely filled field to varchars.
>
> Can some of you please explain the varchar performance problem(s), and
> I'd also like to know if the over one page performance problem is worse
> or better.
>
> We are running Informix Dynamic Server Version 7.31.FD2
> Thanks
>

Messages In This Thread

• varchar performance problems
  Bob Amy -- Tuesday, 8 April 2003, at 9:16 a.m.
  • Re: varchar performance problems
    Clifton M. Bean -- Tuesday, 8 April 2003, at 11:50 a.m.
    • Re: varchar performance problems
      Jack Parker -- Tuesday, 8 April 2003, at 1:31 p.m.
    • Re: varchar performance problems
      bill.roberts@verizon.com -- Tuesday, 8 April 2003, at 2:33 p.m.
      • Re: varchar performance problems
        bill.roberts@verizon.com -- Tuesday, 8 April 2003, at 4:07 p.m.
      • Re: varchar performance problems
        Mark Scranton -- Sunday, 27 April 2003, at 9:42 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.