Re: Performance differences using varchar, char and text
От | Andrew Ayers |
---|---|
Тема | Re: Performance differences using varchar, char and text |
Дата | |
Msg-id | 3EF20604.7060908@eldocomp.com обсуждение исходный текст |
Ответ на | Performance differences using varchar, char and text (Yusuf <yusuf0478@netscape.net>) |
Список | pgsql-general |
Tom Lane wrote: > I believe what you're reporting here is problems on the Access side, not > problems in the underlying database. (That doesn't make them any less > of a real problem if you're using Access, of course.) Access doesn't > work very well with datatypes that aren't found in MS SQL Server... No - I had converted an Access 97 DB over to Postgres, and my VB app was updating the PG database via the ODBC driver. My application is written in VB (version 6, Enterprise Edition) - it isn't a case of an Access/VB for Applications front-end to PG backend. > I think if you look in the pgsql-odbc list archives you will find some > discussion of workarounds for Access with TEXT fields. I believe my problems extended from using DAO, instead of ADO - once I switched to using ADO, my problems went away (of course, other problems cropped up, but that was due to code differences between DAO and ADO usage). I could use TEXT datatypes on the PG database/tables without problems. Honestly, most of my issues was my bad decision to "bastardize" relational DBs - I have a lot of background in PICK/MultiValue databases, where you can store and retrieve from data-cubes, and I wanted this functionality for my application. I have since learned that this approach is far from correct (and I probably have Codd or Cobb or whatever his name is spinning in his new grave - RIP). In the process of converting to PG, a few of the tables I *had* to normalize and split into proper relational tables in order for it to work properly and quickly when I was using VARCHAR fields - so some good has come out of this. > As far as the original question goes: there is no reason within Postgres > to choose one of these three types on performance grounds; you should > make the choice based on the semantics you want. Do you really want > every value blank-padded to exactly N characters? Use char(N). If you > don't want padding, but do want a specific upper limit on the field > width, use varchar(N). If you haven't got any specific upper limit in > mind (and if you're putting in numbers like 100000 then you don't ;-)) > then use text. The performance differences that exist come directly > from the cycles expended to add padding blanks, check that the width > limit is not exceeded, etc. I agree in practice, when you are creating and maintaining a proper relational database, and accessing it properly. However, because of DAO I was hitting a big problem on updates of TEXT fields, which I didn't have in regards to VARCHAR, or any other datatype field in PG - only the TEXT datatype. Furthermore as I have noted, many of these large fields were being used in a manner inconsistant with the rules of relational database structure. I shouldn't have done that. I first thought it was the ODBC driver, but after much discussion and such, I finally tried using ADO instead of DAO, and the update issue with TEXT fields went away. I don't know if the performance issue is still there, though. I kinda suspect it is. However, I have since coded around it (when I was using VARCHAR fields), so the problem isn't a problem for me anymore (I had fields that were very large holding descriptions, and I wanted to do a "keyword" LIKE search on them - I ended up with a different strategy, which keeps me away from TEXT and large VARCHAR fields, and have a keyword table for allowances of lexical stats and such now). Andrew -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
В списке pgsql-general по дате отправления: