Re: varchar performance problems

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 >