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.

asked 18 Nov '15, 15:29

alexryder's gravatar image

alexryder
1163310
accept rate: 0%

edited 18 Nov '15, 15:50

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?

(19 Nov '15, 03:56) Vlad
Replies hidden

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

(19 Nov '15, 05:21) Vlad

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 :)

(19 Nov '15, 07:52) Breck Carter

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.

permanent link

answered 19 Nov '15, 05:09

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 19 Nov '15, 09:51

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

(26 Nov '15, 14:36) alexryder

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

permanent link

answered 19 Nov '15, 06:54

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406

question asked: 18 Nov '15, 15:29

question was seen: 389 times

last updated: 26 Nov '15, 14:36