varchar performance problems

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

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 >

The other big issue is with re-write performance. You pop the row out there and it has a size 'x'. Along comes another varchar row and sits right after your first row. Now somebody comes along and updates the first row - oops, it doesn't fit in the original space anymore and has to be moved - where it used to be is now dead space until a compression is run on the page - which is unlikely to happen when you have very few rows on the page. In your situation, if you can get the varchar row to fit snugly on one page - that is over 1010 in length, but under 2020, then you won't have this problem. Of course you won't get light scans against the table either...... cheers j. ----- Original Message ----- From: "Clifton M. Bean" <cmbean@sbcglobal.net> To: <ids@iiug.org> Sent: Tuesday, April 08, 2003 11:50 AM Subject: Re: varchar performance problems [868] > 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 > > > > >

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 >

And an apology to Clifton Bean who IS a DBA. My e-mail was meant as a reply to Bob Amy. Regards, Bill Roberts AAIS Core Production Support, Informix DBA Office : (813) 978-2340 Pager: (813) 304-6503 e-mail:bill.roberts@verizon.com http://dbaman.tmtrfl.tel.gte.com/browser.cgi Bill A. Roberts/EMPL/FL/Veri To: ids@iiug.org zon@VZNotes cc: Sent by: Subject: Re: varchar performance problems [871] forum.subscriber@iiu g.org 04/08/03 02: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 >