Solved

Need to understand how Sybase 600 characters limitation works?

Posted on 2006-11-05
12
2,523 Views
Last Modified: 2007-11-27
Hi All,

Can someone please tell me in layman term how the Sybase 600 characters limitation works?  

We are using Sybase ASE 12.5 on one of our project and due to the type of data that we track our data varies, we have records with very little info to it and also records that have lots of info.  We have run into some problems where sometimes our reports will run and sometimes we will get an error like: "Sybase 600 characters limitation reached ....".  I did some quick research and found that base on settings on the server Sybase has certain limitations, i.e. 600 for 2K page, 31 columns in group by/order by clause, etc....  What I need to understand is how the Sybase engine works in the background after I ran the query?  For example: if I have two records in the table:

Record Id     Desc
1                 50 characters
2                 700 characters.

If I run a query to select all from this table does the 600 characters limitation apply because of the second record or it doesn't because the first record only has 50 characters?  Also, is there a work around to this problem beside changing the page setting on the server.  TIA
0
Comment
Question by:Taz2
  • 5
  • 3
  • 2
12 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 17877362
I will answer your last question first:  Nope.  Your stuck.  The only direct and easiest way around this is to change the page size to 4 or 8KB.  I would highly recommend you just bite the bullet and change the page size.

The rest of your question can be answered simply by saying that Sybase will give you an error when the total length of the fixed length fields, overhead, and variable length fields in a record approaches the page size, in your case, 2KB (the exact number escapes me right now but it is documented).

You could play some games like adding a second field to take overflow from the first field but that promises to be a pain in the butt unless your application is very, very simple.

The other way around this might be worse; that is to use CLOBs (Charater Large OBjects).  If you really have just a few hundred bytes in the field, you can define a CLOB field and do the simplest of operations against it in TSQL.  Depending upon what you are using for report generation, the tool may choke on the CLOB field altogether regardless of length.

Increasing the page size is definitely the right answer and is not hard to do.  You will have to rebuild the server and databases.  You will also have to BCP out any data you wish/need to salvage since you cannot backup/restore across page sizes.  Another option is to build the larger page size database on another machine and use CIS to connect to it and do INSERT/SELECTS to get the data moved.

Finally, the best argument for larger page sizes is the increase in performance.  If your row sizes are bumping against the 2KB limit, you really should be running an 8KB page size.  It reduces the number of physical I/Os in most cases which, of course, improves performance.

Best of luck,
Bill
0
 
LVL 10

Expert Comment

by:bret
ID: 17882834
I'm going to disagree with Bill on this.  We could use more information on
the error and the TSQL causing it, but I think I know what it is and
how to get around it

I believe the limit you are hitting is for the maximum width of an index, which
is also the maximum length of a group-by (because ASE internally builds a worktable
with an index based on the group by columns to do that work).

A simple "select *" shouldn't hit the error at all

A way around this error is to have some shorter surrogate value for the long
column.  If that column contains only unique values, then you can just
add an identity column to the table and group by the identity column rather
than the long column.  If that colume contains duplicates, then an approach
is to create a lookup table that correlates a surrogate value with each unique
value (again, could be an identity column) and add a column to the base table
that would contain the shorter surrogate value.  Again, do your group-bys
using the surrogate value rather than the long one.

An insert/update trigger can be used to maintain the lookup table and the
value of the surrogate in the base table.

Going even further, you could drop the long column from the base table and
rewrite your code to get the long value through a join of the two tables.
If you have many long duplicate values, this could save quite a bit of space.
0
 
LVL 10

Expert Comment

by:bret
ID: 17882869
Oh, yes - from DBCC SERVERLIMITS output:

Index-specific row-size limits

  Max index row-size, including row-overheads                       :
650     1300    2700    5400    
  Max user-visible index row-size                                   :
600     1250    2600    5300    

the values are for 2k, 4k, 8k and 16k page size servers
0
 
LVL 19

Expert Comment

by:grant300
ID: 17883319
Good catch, Bret.

I have to admit it never occured to me that anybody would try to index a 600 character field.

To paraphrase an old vaudville joke, "How do you get to Carnegie Hall?  Normalize, normalize, normalize" :-)

Bill
0
 

Author Comment

by:Taz2
ID: 17883326
Hi Bret,

This is the error that we get:  

"Databae error: [Server (NameofServer)][Procedure DynamicSQL].  The current query would generate a key size of 750 for a work table.  This exceeds the maximum allowable limt of 600.  Contact your database supplier for more information"

It's random when and how we get this error, the only pattern that I can see is when we have lots of columns in our select clause or there are lots of summing that requires grouping.  My questions are two folds:
 
1. Does it make sense for me to go through each field/attribute in the table and decide (base on Data type/length) which combination of these field/attribute will generate this error and which will not?  For example:  if i have 3 fields in one table: Id (Int), Name Varchar(100), and Desc Varchar (255).  Would it make sense for me to say that if I select Id and Name then I won't run into the limitation but if I select Id and Desc then I will?  

2. How does the worktable in Sybase works?  is this the temporary workspace used when processing queries?  If the query is a long running query which takes up lots of space in the database and cause this type of error?

Thanks.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 10

Expert Comment

by:bret
ID: 17883413
1) well, you shouldn't hit the error in either of these cases because it just isn't something
you would hit on a simple select.  Even if there was a group by, no combination of those
three columns exceeds 600 bytes.   But if you had another varchar(255) column and grouped
by name, desc, and the other wide column, then you would.

There is a similar error for exceeding the maximum length of a row, which you can hit
if the sum of the sizes of the columns you are selecting exceeds the max length of a row
(which is a little under the server's page size, generally around 1964 bytes for an APL table).  
This is most commonly hit when selecting wide columns from multiple tables through a join.

2) Yes, worktables are system-generated temporary table.  The length of the query/transaction
won't have anything to do with this error - the important factor is the width of the columns
included in GROUP BY  (or CREATE INDEX).

0
 

Author Comment

by:Taz2
ID: 17883660
Sorry, my first question wasn't clear.  Let me try again, let's say this is my table with two records as below:

Id (Int)        Project Name  Varchar(50)                          Project Desc  Varchar (255)                  Project Notes  Varchar (255)
1                Even though the data length is set at 50,       Actual length of data entered is 255      actual length of data entered is 255
                  actual length of data entered is 255

2               Actual length of data entered is 50                Actual length of data entered is 100       actual length of data entered is 100

So, let's say I ran a query SELECT * FROM table.  In theory I should get the two records above back, hence my questions:

1. Will I get anything back from my query or will I run into the 600 limitations?  If no, why not? the first record definitely is over 600 characters?  If yes, why?  the second record only has 252 characters?  

This is what I need to understand, how does the sybase works in this case?  does Sybase evaluate each record one by one and decides which one to return and which one to generate the error "This exceeds the maximum allowable limt of 600" ?  Thank you in advance for your help/patience in this, I have never work in Sybase before so this is a huge learning curve for me.
Taz2
0
 
LVL 10

Accepted Solution

by:
bret earned 100 total points
ID: 17884782
Your statement for the NAME column - even though the data length is set at 50, actual lenghth of data entered is 255
doesn't really make sense in this context.  If you try to insert 255 characters into a varchar(50), the value will be truncated down to the first 50 characters (silently, unless you have SET STRING_RTRUNCATION ON).  This will have happened at INSERT time; by the time any SELECT is done the data is certainly no more than 50 characters, so the maximum length of the data in this row is 560 characters.

You will get both rows back with a simple select *.  That is because the limit on the total size of a *row* is ~1964 bytes.

However, to answer your general question - in the case of this error, ASE will only raise it if there
is data involved in the query that hits the limit.  The error will be raised when the row that violates
the limit is processed.

0
 

Author Comment

by:Taz2
ID: 17890743
Hi Bret,

Just more question regarding your statement here: "If you try to insert 255 characters into a varchar(50), the value will be truncated down to the first 50 characters....".  I thought that since Varchar is a variable length character data, storage size is the actual length of the data entered and not in n bytes?
0
 
LVL 10

Expert Comment

by:bret
ID: 17891033
Yes, but only up the specified maximum length.  a varchar(20) will hold 1-20 bytes/characters.  a varchar(50) will hold 1-50, etc.
Anything over the declared maximum will be truncated.  And, oddly, empty strings "" are converted to a single space " ".

Cheers,
-bret
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Find out what the Office 365 disclaimer function is, why you would use it and its limited ability to create Office 365 signatures.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now