Updategram produces Unicode DML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updategram produces Unicode DML

Author

Topic

dkm1hub
Starting Member

2 Posts

Posted - 2002-08-01 : 19:18:10

We are using MQ/Series and ASP to update data from DB2/400 and Unix/Oracle tables into our SQL/2000 Data Warehouse ODS tables. I am the DBA and have been struggling with our apps team (no offense...) on a performance issue.

SQL profiler shows the transactions (updates, inserts and deletes) executed by the updategram to contain the Unicode N'text' format for all character columns. In the WHERE clause, this causes SQL to do a Clustered Index Scan (of 300K rows) even when an entire Primary Key is provided in the Update/Delete. When I physically remove the 'N' the query will run in milliseconds instead of 33 seconds for the Unicode version.

Also, Profiler shows an Update to a non-existing key requiring 155 Reads to return an error with Unicode formatted data and non-formatted WHERE clause, and 24 Reads to return the error without the Unicode formatted data.

The difference is HUGE (especially the Update - literally 5000 times faster) between the 2 versions. Is this something in the Optimizer than can be tweaked (to ignore/dealwith Unicode WHERE clauses)? Or is it a matter of XML/Updategram schema/column mapping? Or some setting in the XML stream that we're totally missing?

I'm totally new to XML so this is like a foreign language...

Thanks!

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-01 : 19:37:20

Are you storing international characters in these fields? If not, then you don't need NText fields in the database. You can change them to Text fields, or possible VarChar fields, depending on the amount of data going into each field.

We'd need to see the queries that are being executed against the SQL database and the DDL (CREATE TABLE Statements) to be able to help you further.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

dkm1hub
Starting Member

2 Posts

Posted - 2002-08-02 : 13:37:50

Hmmm, s'pose I should have expounded on that a bit... We have NO unicode columns/tables. We don't code with or for unicode anywhere, our programmers don't even know what unicode is. I don't think our problem is coding or not coding something per se.

The UPDATEGRAM process is producing these DML statements with the N'text' designation in them on it's own volition. We just want it to stop doing that. I'm assuming here that we have a renegade default or setting in the XML document that causes Updategram to ignore(?) our standard plain English character types and use unicode types instead. I thought it might be in the schema, but it's not very clear about unicode (datetypes are very general).

Since you asked, here's a simplified (yet still sufficiently dramatic) example...

Table DDL
CREATE TABLE SMPWAYB
(WBSHP# char(12) NOT NULL,
WBTYP char(2) NOT NULL,
WBSEQN numeric(5,2) NOT NULL,
WBDIV char(4) NOT NULL,
WBWHS char(4) NOT NULL
[150 more columns]
) ON PRIMARY
ALTER TABLE SMPWAYB
ADD CONSTRAINT PK_SMPWAYB PRIMARY KEY CLUSTERED
(WBSHP#, WBTYP, WBSEQN, WBDIV, WBWHS) ON PRIMARY

Table has 300,000 rows averaging 1825 bytes.

XML Schema
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
- <xsd:element name="SMPWAYB" sql:relation="SMPWAYB" sql:key-fields="WBSEQN,WBTYP,WBWHS,WBDIV,WBSHP#">
- <xsd:complexType>
<xsd:attribute name="WBSEQN" sql:field="WBSEQN" type="xsd:decimal" />
<xsd:attribute name="WBTYP" sql:field="WBTYP" type="xsd:string" />
<xsd:attribute name="WBWHS" sql:field="WBWHS" type="xsd:string" />
<xsd:attribute name="WBDIV" sql:field="WBDIV" type="xsd:string" />
<xsd:attribute name="WBSHP_x0023_" sql:field="WBSHP#" type="xsd:string" />
[150 more columns]
</xsd:complexType>
</xsd:element>
</xsd:schema>

XML example
<ROOT xmlns:updg="urn:schemas-microsoft.com:xml-updategram"><updg:sync xmlns:updg="urn:schemas-micro
soft-com:xml-updategram" mapping-schema="./template/SMPWAYB.xml"><updg:before><SMPWAYB WBSHP_x0023_=
"06896260.001" WBTYP="30" WBSEQN="001.00" WBDIV="HUBG" WBWHS="HCIL"/></updg:before><updg:after/></up
dg:sync></ROOT>

UPDATEGRAM processing above XML Produces this (according to SQL Profiler)
DELETE SMPWAYB
WHERE WBSEQN=001.00 AND WBDIV=N'HUBG' AND WBSHP#=N'06896260.001' AND WBWHS=N'HCIL' AND WBTYP=N'30'

SQL Query Analyzer Estim Exec Plan shows a "clustered index scan" with I/O Cost of 49.4

When we manually remove Unicode designator
DELETE SMPWAYB
WHERE WBSEQN=001.00 AND WBDIV='HUBG' AND WBSHP#='06896260.001' AND WBWHS='HCIL' AND WBTYP='30'

SQL QA EEP shows a "clustered index delete" with I/O Cost of .0118

This of course happens with Inserts and Updates as well, and to every table that we're updating this way. All character columns (in both the SELECT and the WHERE clauses) go into UPDATEGRAM unmolested (by us) CHAR, yet they all come out Unicode. SQL Server Optimizer can't do conversion between Uni and regular text, so it searches the clustered index using a scan instead of a seek (essentially making it a table scan).

Sorry about the length, but this is the entire breadth of our problem and our programmers have been struggling for a long time. I'm spanking new to XML and just don't know where to look for that magic answer that *I* think is probably obvious...

Dave

monkeybite
Posting Yak Master

152 Posts

Posted - 2002-08-02 : 14:10:03

I've also noticed this behavior in the past. I tinkered with the XSD schemas for a week and wasn't able to prevent this or change it. We were using SQLXML 2.0 at the time. Allegedly, there were supposed to be better support for sqlxml datatypes in version 3.0, but I haven't worked with SQLXML in a while, so i'm not sure it's in there.

-- monkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources