Optimization gurus: Help with varchar vs. text fields decision

Hi, I'm trying to improve performance for a db that stores messages. The message is currently stored in an ntext field, but when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I don't want to actually limit the message size, but it seems like I might get much better performance using a varchar(3000) field to hold most of the messages, and a separate text field just used for those 3% that really are long. Is this a good idea? If so, is it better to put the Message and LongMessage fields in the same table; or, have a separate table to hold the long messages? If it is in a separate table, it would need to be left joined with the message table each time messages are retrieved.

Also -- I am getting about 700 new messages daily, and right now have over 150,000 messages stored. The vast majority of activity involves new messages. Is this a good situation to look at using horizontal partitioning?

Thanks for any help, I don't really have anyone to discuss this with and it is really helpful to get some other views!!

Are you able to upgrade to SQL2005? VARCHAR(max) would be a simple solution to your problem.

|||

Yay, I'm already on SQL server 2005, so I could use varchar(max) -- now that I've heard of it! Are there performance issues to be aware of with max? Any drawback to a design where the row size will vary wildly from row to row??

|||

Celestine:

when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I calculated wrong -- it is an ntext field, so each char is two bytes, not one byte. Meaning 97% of the messages are actually less than 1500 characters, not 3000. All the messages are in English, so I'm not going to continue using ntext or nvarchar.

|||

Varchar(max) allows rows to span physical blocks, hence no row length restriction. Why not look it up on Books-On_line BOL?

|||

I understand that there is no length restriction, I looked it up right away; thanks for making me aware of the max option. What I am asking about is whether there are performance implications to consider with using varchar(max), when you are hoping to get multiple records to fit on a data page.

|||

With varchar(max) most of the records will be fetched with a single read whereas for ntext, two reads will be required for every record irrespective of its size.

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

Optimistic Concurrency Control Error

Hi,

I have a table X:
ID (PK, int, not null)
cstID(FK, int, not null)
Name( nvarchar(100),not null)
Desc( ntext, null)

I am using the table view in Enterprise manager, if I manually type in a new row, then I edit that row, setting "Desc" = NULL, then I delete that row (from within the table view) I get the error:

Data has changed since the results pane was last retrieved. Do you want to save your changes now? (Optimistic Concurrency Control Error)

Things to note:
There was a FTI on this table, I deleted it, didn't help.
No other process or users are editing/viewing this table
The error doesn't occur if edit any other column, just setting the "Desc" to NULL creates this error.

Some other tables in my DB exhibit this same behavior, but not all......I can't figure out what the heck is going on...can you?

Can't anyone take a stab at this?

Some more information, I using SQL server 2005 so I am using MS not EM.

I created a duplicate table and that dup table doesn't have the same issue. So, I scripted both the bad table and the dup table, both scripts look identical sans the table names. I ran a trace and it doesn't look like anything different is happening between the original table and the dup table.

The table doesn't have any triggers.

Please help!

|||I get a similar message when I try to update any tables that contain fields of type bit, ntext, text, or image. But I only get it occasionally and cannot find a reason. I changed my ntext field to varchar and that eliminated the problem.|||

I can help you out here.

The fundamental cause of the problem is that Management Studio is rubbish.

It breaks down like this:

Management Studio can't handle edits on rows with char/varchar/text fields with more than 4000 characters of data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=266305&SiteID=1)

One side -effect of this is that in optimistic mode Management Studio thinks that the textual data has changed in between loading it and saving your changes to other fields so it warns you. Fortunately it doesn't go so far as to actually destroy your text data.

OPENXML INSERT/UPDATE and NTEXT

Hi,
I'm having problems inserting/updating a NTEXT field using OPENXML.
The field always gets a blank value with any parameters it receives.
Here's the (simplified) query:
---
declare @.doc int
-- Actually, it's a parameter
declare @.xml nvarchar(4000);
set @.xml = N'
<Article>
<Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
<Content>yada yada yada</Content>
</Article>'
EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3) WITH Article new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM OPENXML(@.doc, 'Article', 3) WITH Article
EXEC sp_xml_removedocument @.doc
---
Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
value.
Any ideas?
DiegoI think you need to revise your OPENXML syntax a bit. Try the following
instead of what you have:
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3)
WITH (
id uniqueidentifier 'Id',
content nvarchar(400) 'Content'
) new
WHERE Article.Id = new.Id
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Diego Mijelshon" <no@.thanks.com> wrote in message
news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
> Hi,
> I'm having problems inserting/updating a NTEXT field using OPENXML.
> The field always gets a blank value with any parameters it receives.
> Here's the (simplified) query:
> ---
> declare @.doc int
> -- Actually, it's a parameter
> declare @.xml nvarchar(4000);
> set @.xml = N'
> <Article>
> <Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
> <Content>yada yada yada</Content>
> </Article>'
> EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3) WITH Article new
> WHERE Article.Id = new.Id
> IF @.@.rowcount = 0
> INSERT INTO Article
> SELECT *
> FROM OPENXML(@.doc, 'Article', 3) WITH Article
> EXEC sp_xml_removedocument @.doc
> ---
> Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
> value.
> Any ideas?
> Diego
>|||Adam,
Thanks for your answer, but that didn't fix it.
Besides, the beauty in the "WITH tablename" clause, combined with FOR XML
AUTO, is that I get "free" O/R-M using XML Serializing.
I'll share my solution. It still looks like a bug to me...
---
SELECT *
INTO ##tmp
FROM OPENXML(@.doc, 'Article', 3) WITH Article
UPDATE Article
SET Content = new.Content
FROM ##tmp new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM ##tmp
DROP TABLE ##tmp
---
As you can see, inserting in a temporal table does the trick.
Diego
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OxCpmzEGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I think you need to revise your OPENXML syntax a bit. Try the following
> instead of what you have:
>
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3)
> WITH (
> id uniqueidentifier 'Id',
> content nvarchar(400) 'Content'
> ) new
> WHERE Article.Id = new.Id
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
the
>|||"Diego Mijelshon" <no@.thanks.com> wrote in message
news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE ##tmp
> ---
> As you can see, inserting in a temporal table does the trick.
I'm glad you found something that works. FYI, you should probably use a
local temporary table (single #) instead of a global one (##) unless you
need access to this same temp table from other processes...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWtvvjIGFHA.560@.TK2MSFTNGP15.phx.gbl...
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> I'm glad you found something that works. FYI, you should probably use a
> local temporary table (single #) instead of a global one (##) unless you
> need access to this same temp table from other processes...
Thanks for the correction, I had it backwards :-)
Diego

OPENXML for bulk inserts

Hi and thk for your help ;-)
I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
@.xmlOrders nText,
@.var_id int
I have this xml (@.xmlOrders ) :
<ORDER>
<ORDER>
<art_desc>blablablabla.</art_desc>
<art_code>1</art_code>
<art_units>50</art_units>
<xx>111</xx>
<yy>111</yy>
</ORDER>
<ORDER>
<art_desc>tetetetet.</art_desc>
<art_code>2</art_code>
<art_units>10</art_units>
<xx>222</xx>
<yy>222</yy>
</ORDER>
</ORDER>
I need to insert the parameter @.var_id and this fields from @.xmlOrders:
(art_desc,art_code and art_units) into a table "tbl_orders" with this
structure:
order_id int identity
art_desc varchar
art_code varchar
art_units int
var_id int
How can i modify this for work:
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
Insert Into TBL_ORDERS
SELECT art_desc,art_code,art_units
FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
WITH (art_desc varchar(100), art_code varchar(100),art_units int)
XMLOrders
EXEC sp_xml_removedocument @.hDoc
Thank you.
Hello, Oterox!
You wrote on Thu, 28 Oct 2004 15:04:20 +0200:
[Sorry, skipped]
O> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
The third parameter is the code for default mapping.
1 - attribute centerinc
2 - element centeric
Since you didn't point out the column pattern
O> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
the server use default, etc attribute centerinc mapping. This is not
correct, 'cause you don't have art_desc attribute as well as art_code and
art_units. To make this work you should change the default mapping to
element mapping:
FROM OPENXML (@.hdoc, '/ORDER/ORDER',2) --change the value to 2
or use explicit column mapping
FROM OPENXML (@.hdoc, '/ORDER/ORDER')
WITH(
art_desc varchar(100) 'art_desc',
art_code varchar(100) 'art_code',
art_units int 'art_units'
)
With best regards, Alex Shirshov.
|||If you could send me your procedure and your xml.file.And write me how you
import xml file to sql database.
my e-mail: ljag@.wp.pl
Uytkownik "Oterox" <oterox@.asp404.com> napisa w wiadomoci
news:u0EcT7OvEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi and thk for your help ;-)
> I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
> @.xmlOrders nText,
> @.var_id int
> I have this xml (@.xmlOrders ) :
> <ORDER>
> <ORDER>
> <art_desc>blablablabla.</art_desc>
> <art_code>1</art_code>
> <art_units>50</art_units>
> <xx>111</xx>
> <yy>111</yy>
> </ORDER>
> <ORDER>
> <art_desc>tetetetet.</art_desc>
> <art_code>2</art_code>
> <art_units>10</art_units>
> <xx>222</xx>
> <yy>222</yy>
> </ORDER>
> </ORDER>
> I need to insert the parameter @.var_id and this fields from @.xmlOrders:
> (art_desc,art_code and art_units) into a table "tbl_orders" with this
> structure:
> order_id int identity
> art_desc varchar
> art_code varchar
> art_units int
> var_id int
> How can i modify this for work:
> DECLARE @.hDoc int
> exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
> Insert Into TBL_ORDERS
> SELECT art_desc,art_code,art_units
> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
> XMLOrders
> EXEC sp_xml_removedocument @.hDoc
> Thank you.
>
>

OPENXML error

Hi,
I have an SP which deals with xml. The SP receives an ntext argument that
has the xml string.
The sp_xml_preparedocument sp is called to get a handle from the ntext.
Then an OPENXML statement clubbed with an INSERT statement is executed.
Example :
INSERT INTO dpcmain.ProcessedSecurity ( FileId, SecurityId, RecordDate,
UnprocessedData )
SELECT @.FileId, SecurityId, RecordDate, UnprocessedData
FROM OPENXML (@.PSDoc,'xml/rs:data/rs:insert/z:row',0)
WITH (SecurityID integer, RecordDate varchar(128), UnprocessedData
varchar(256))
where @.PSDoc is the handle to the xml document.
We occasionally get an error in the OPENXML statement.That is okay, however,
the problem is that SQL Server DOES NOT go to the next line in the SP. In
the next line I check for @.@.error and take some action if OPENXML fails.
Is it a known issue that when OPENXML fails, the control goes out of the SP
and DOES NOT go to the next line in the SP?
Thanks in advance,
Bhasker.
Hi,
Whether or not a Stored Procedure or Trigger continues onto the next line, following an error is dependant on the type of error that is raised. Back in the days of 6.5, this did NOT depend on the Severity of the error, but I'm not sure if that has changed
with 2000.
So, the actual error that you are getting would be useful.

OPENXML error

Hi,
I have an SP which deals with xml. The SP receives an ntext argument that
has the xml string.
The sp_xml_preparedocument sp is called to get a handle from the ntext.
Then an OPENXML statement clubbed with an INSERT statement is executed.
Example :
INSERT INTO dpcmain.ProcessedSecurity ( FileId, SecurityId, RecordDate,
UnprocessedData )
SELECT @.FileId, SecurityId, RecordDate, UnprocessedData
FROM OPENXML (@.PSDoc,'xml/rs:data/rs:insert/z:row',0)
WITH (SecurityID integer, RecordDate varchar(128), UnprocessedData
varchar(256))
where @.PSDoc is the handle to the xml document.
We occasionally get an error in the OPENXML statement.That is okay, however,
the problem is that SQL Server DOES NOT go to the next line in the SP. In
the next line I check for @.@.error and take some action if OPENXML fails.
Is it a known issue that when OPENXML fails, the control goes out of the SP
and DOES NOT go to the next line in the SP?
Thanks in advance,
Bhasker.Hi,
Whether or not a Stored Procedure or Trigger continues onto the next line, f
ollowing an error is dependant on the type of error that is raised. Back in
the days of 6.5, this did NOT depend on the Severity of the error, but I'm n
ot sure if that has changed
with 2000.
So, the actual error that you are getting would be useful.