Optimising text type fields

[Former Member]

When our database was set up (before my time), I am guessing for future proofing reasons, it was decided to use text type fields for any textual type columns

This includes fields such as customertype which has a max of 3 chars for example.

I read somewhere that text columns allocate a 2k of space regardless of what is stored, but I have been unable to locate that source again.

1. Am I right in thinking that optimizing to the correct column sizes will improve read/write performance (I am not too fussed if it is only going to save a bit of disk space)

2. If I want to go ahead and convert these columns to more realistic lengths, is there a boundary I should use, between choosing a varchar and a text type

Eg, if varchar(2000) performs the same as text, then I will just leave it alone.

[Vlad]

An interesting thing I've found (http://dcx.sap.com/index.html#sqla170/en/html/81f490066ce210148562c14973ac8507.html) is that:

SQL Anywhere: ... In SQL Anywhere, VARCHAR is a variable-length type. In other relational database management systems, VARCHAR is a fixed-length type... SQL Anywhere does not blank-pad stored character data.

Maybe you do not need to worry much? Have you tried to make a copy of the DB file, play with one of VARCHAR columns, truncate/defrag/rebuild it and compare size/performance? Is your DB slow now, if yes, have you analyzed why?

[Vlad]

After I read Volker's reply, I realized that the question was understood badly. My fault. Probably, this comment is wrong as well.

[Breck_Carter]

FWIW I recently created a new SQL Server consolidated database to use in a new synchronization setup with an existing SQL Anywhere database, and that existing database used LONG VARCHAR for most string columns, including almost all of the 5,000+ columns in one single table. Here are two observations...

First, SQL Anywhere had absolutely no problem with a table containing thousands of LONG VARCHAR columns storing NULL or short string values.

Second, SQL Server has HUGE difficulties with a table like that; e.g., column count and row length limitations.

You don't mention any compelling reason to change the data types, so unless you don't have any other work to do perhaps you should leave them alone ( as in, if it ain't broke don't fix it 🙂

[VolkerBarth · Accepted solution]

We're talking about a SA 12 database, correct? (*)

As for SQL Anywhere, TEXT is just a system-defined domain implemented as a LONG VARCHAR, so I think your question is fully addressed here:

varchar vs. long varchar

From that I would think that your information about a minimum 2K storage is basically wrong.

To 1: Performance on the server-side may not increase when using shorter VARCHAR but client-side performance should increase because no SQLGetData()-like calls (or the according special fetch operations of other APIs) are required.

To 2: A normal VARCHAR (or NVARCHAR) allows up to 32767 character so I think that's a limit that could be used to distinguish between "normal" and "wide" strings.

---

(*) In contrast, say with MS SQL Server, a "text" datatype is a (since SQL Server 2005 deprecated but still available with SQL Server 2014) datatype with a lot of disadvangages compared to "normal" varchar datatypes, e.g. you cannot use a within a trigger, the accesses are different, no search facilities - I do know because we use a third-party system using those types a lot, and it's really annoying.

[Former Member · Accepted solution]

Thanks for the feedback, I will go and find more important things to do, cheers

[MCMartin]

Also check the INLINE and PREFIX clauses to understand how SQLA is storing the data in the table rows.