Taz2
asked on
Need to understand how Sybase 600 characters limitation works?
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
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
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.
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.
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
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
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
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
ASKER
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.
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.
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).
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).
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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
Anything over the declared maximum will be truncated. And, oddly, empty strings "" are converted to a single space " ".
Cheers,
-bret
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