Re: VARCHAR performance issue - Mailing list pgsql-general

Hello all,

I am experiencing an issue that I am wanting to find a good solution for
- so far, everything has felt like a bandaid.

Let me first lay out my problem, my solution, and what I have tried:

Problem:

I have a legacy application, written in VB6, that I have converted to
use PostgreSQL (7.3.2) via the ODBC driver. I have set up a DSN-less
connection to the database. My tables are converted (and if need be, can
be easily reconverted). Originally they were Access 97 tables.

On these tables, I had several which used the Memo datatype in Access.
After doing research, I found that PostgreSQL offerred the datatype
TEXT, which seemed to be the ideal type to use for a Memo field.
However, in testing, I had code in the VB application which essentially
did the following (DAO based code):

With recordset
   .AddNew
   ![memo] = "This"
   ![memo] = "That"
   .Update
End With

In other words, a dual update during the add of a record was performed.
Using Access, this resulted in the field on the new record containing
"That". Under PostgreSQL, the second setting causes an error. I don't
know if this is due to the ODBC driver, or PostgreSQL - I think it may
be the driver - other PostgreSQL datatypes allow this same update to
pass - only a TEXT datatype fails.

So, I sought a solution - I had posted to this list about my problem,
and the solution that both the list and I came up with was...

Solution:

Use a VARCHAR field - so, I set up my tables to use VARCHAR fields, and
I explicitly defined my fields to be a "maximum" size, after researching
my data on the Access 97 database to determine what I needed. I set this
size to 175,000 characters.

This seemed ok, but when I would perform a select that used a VARCHAR
field that was large like that, the select would slow waaaaaay down - it
would take a very long time to select the data needed. Decreasing the
VARCHAR size helps, but not as much as I would like. Other tricks in
both my VB app code and SQL statements (such as avoiding whereever
possible selecting those fields unless absolutely needed) has helped,
along with setting up some indexes as needed to speed things up.

A colleague asked why I was explicitly defining the VARCHAR field size -
that I could just leave off the explicit size, and VARCHAR would only
use what was needed, and allow me to store however much I needed in the
field. I didn't remember anything in the documentation about this, so I
decided to re-read about the type, and see what I could do. I thought
maybe this would allow me to use the VARCHAR fields, but keep the speed
up as well.

I looked at the docs, and sure enough, it says that you can leave off
the explicit size, and it will store whatever length string you want.
However, when I went to try it out, I saw something very different
happenning.

When I created a new record, and set the string to 5000 characters to
store in the VARCHAR field I created - it only stored 254 bytes! The
rest were truncated.

My questions basically are am I doing something wrong, is there
something I am missing to speed all of this up (my select speed)? Is
there another data type I should use? Why is only 254 bytes being stored
when the documentation says differently? Or am I stuck with using
VARCHAR as it is, and I have to continue to work around my problem as
best as I can?

Thank you for any help or insights you can provide.

Andrew L. Ayers
Phoenix, Arizona



Eldorado Test Disclaimer...please ignore.