2005 xml equality

There is not equality comparison for xml data type. You cannot cast xml to
text or ntext, but you can cast to varbinary(max), varchar(max), and
nvarchar(max).
A couple of routes you can take (I'm sure there are other ways as well):
1. Assuming the incoming xml is identical in structure (whitespace doesn't
count) to the existing xml value , you could cast the incoming value and
existing value to varbinary(max) and compare.
2. You could iterate through the xml and compare, although this is more
complicated to implement and slower as the size of the xml value increases.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:e$kf%23YvwHHA.5008@.TK2MSFTNGP05.phx.gbl...
> Is there any (perhaps roundabout) mechanism for testing xml columns
> for equality?
> Specifically, as part of a bulk update (i.e. into a staging table that
> is then migrated) I want to short-circuit any records whose xml hasn't
> actually changed, since I have audit requirements and I don't want to
> fill in the audit every time a record is present in an extract - only
> when it has actually changed.
> Any ideas? I have tried a few approaches like extracting as ntext or
> some of the checksum functions, but not with any success. CLR perhaps?
> Marc
>
>
Thanks Peter - I'll give the varbinary(max)/varchar(max)/nvarchar(max)
route a go in the morning. I'm confident that this will make
everything work - much appreciated.
Marc

2005 Query help

Please help.

On sql 2000 i have a query like this where the columns are primary keys.

Select count(*) from db.dbo.table1

where convert(varchar(3), col1) + convert(varchar(10), col2) not in

(select convert(varchar(3), col1) + convert(varchar(10), col2) from db.dbo.table2)

It completes in 1 second with sql 2000. I have restored the db to sql 2005 and run the same query. The processors peg and it goes to la la land. I have updated statistics and installed SP1. Anyone have ideas? The sql 2005 is even way better than the sql 2000 box. If you have a better way to perform the same task, please let me know.

Thanks!

can you compare execution plans on both servers? That should give a head start.|||

Hey,

I did compare the execution plans. For some reason, the 2005 execution plan has more to it and mentions parallelism. It just does not run the query. The box just maxes out and stays maxed out. Very strange.

I've rebooted the box for the heck of it and it doesn't matter. It doesn't want to run. Thanks for the response.

|||Moving to T-SQL forum. Maybe there's a way to rewrite the query.|||

Try this using EXISTS:

select *
from db.dbo.table1 as table1
where not exists (select *
from db.dbo.table2 as table2
where table1.col1 = table2.col1
and table1.col2 = table2.co2)

This should perform better and give the same results (actually more correct, because the varchar conversions could in some rare cases given invalid values).

As for the long run times, How much data is involved? One of the problems I have run into is a lot of waits during parallel operations when some larger operations go parallel. I had to tune some of my data warehouse queries by setting MAXDOP to 1.

I found this by executing this query:

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,
execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

And checking the wait type. Lots of huge CXPACKET waits. Once you get into the wait, watch the results here and post them. You can see where the execution is at by watching the currentExecutingCommand column (it is really cool to watch when you aren't stuck :)

|||

Hey Louis. Thanks for the response!

The exists does work very well. I use that most of the time. I also will put a hyphen between the converts to help avoid getting errors when I do use the "in" style.

I tried running the query you posted on the 2005 box and it complains about '.' near the end of the query saying incorrect syntax. I don't see why though.

It's very strange though how they will be handled so differently between 2000 and 2005.

Thanks again!

|||

The other concern with the IN style is indexing. If you put values in functions or expressions it invalidates use of indexes. But putting seperators that cannot exist in the data will make it "technically" safe.

I took that query verbatim and ran it on my express instance and it worked fine. It is version:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I will try it on my 2005 SP1 box and make sure it works there, but that is interesting.

|||

Hey Louis. Thanks for the info.

Thanks too for trying it on your boxes!

One of my tables has 1.4 million records and the other has about 40K.

Thanks

2005 perf much worse than 2000... suggestions please..

I have this SP that takes several varchar columns and concatinates them all together then inserts them into a text field. I do this with a cursor which was the quickest way to get it done when it was setup...

However when I moved the process to a 2005 server (on the same physical server) the process drastically slowed down. On 2000 the process took about 7 min to handle all 350k+ rows with the processors hanging around 20-40%... On 2005 it took over 30 min (not sure how long it would take cause I killed the process) and the processors stay above 98%...

I have rewritten the process to use a while loop instead of the cursor (I wanted to do this anyways) and it had no effect. At this rate (about 1 row a second) it will take forever and this process runs everyday.

Any ideas?

Here is the procedure...

declare @.srch_field varchar(8000)

declare @.row int, @.productid varchar(25)

DECLARE @.title varchar(150), @.actors_keyname varchar(1200), @.directors_name varchar(400)

Declare @.genres varchar(700), @.theme varchar(1500), @.type varchar(1500), @.studio_desc varchar(100)

DECLARE @.media_format varchar(50), @.artist_name varchar(100), @.dev_name varchar(100)

DECLARE @.flags varchar(256), @.starring varchar(256), @.esrb varchar(100), @.esrb_desc varchar(500)

DECLARE @.ptrval varbinary(16), @.text varchar(max)

declare @.productlist table(product_id varchar(25), IDNUM int identity)

insert into @.productlist (product_id)

select product_id

from music_load..globalsearch

select @.row = @.@.rowcount

while @.row > 0

begin

select @.productid = product_id

from @.productlist

where idnum = @.row

SELECT @.title = rtrim(title) ,

@.actors_keyname = actors_keyname ,

@.directors_name = directors_name,

@.genres = genres ,

@.theme = theme ,

@.type = type ,

@.studio_desc = studio_desc,

@.media_format = media_format ,

@.artist_name = artist_name,

@.dev_name = dev_name,

@.flags = flags ,

@.starring =starring ,

@.esrb = esrb ,

@.esrb_desc = esrb_desc

FROM globalsearch

where product_id = @.productid

Set @.srch_field = isnull(@.title,'')

if @.actors_keyname is not null and @.actors_keyname <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.actors_keyname)

if @.directors_name is not null and @.directors_name <> 'unknown'

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.directors_name)

if @.genres is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.genres, 0,''))))

if @.theme is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.theme, 0,''))))

if @.type is not null

Set @.srch_field = @.srch_field + ' ~ ' + (ltrim(rtrim(replace(@.type, 0,''))))

if @.studio_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.studio_desc)

if @.media_format is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.media_format)

if @.artist_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.artist_name)

if @.dev_name is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.dev_name)

if @.flags is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.flags)

if @.starring is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.starring)

if @.esrb is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb)

if @.esrb_desc is not null

Set @.srch_field = @.srch_field + ' ~ ' + rtrim(@.esrb_desc)

update globalsearch

set srch_field = @.srch_field

where product_id = @.productid

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = credits

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

SELECT @.ptrval = TEXTPTR(srch_field),

@.text = track

FROM globalsearch

where product_id = @.productid

UPDATETEXT globalsearch.srch_field @.ptrval NULL NULL @.text

set @.row = @.row - 1

end

Text fields are going away in 2005. The first thing to do is change srch_field to a varchar(max) type.

The second thing is to make the update one UPDATE command instead of steping thru them one at a time. You don't need a cursor or a temp table or a loop at all. Do something like this:

UPDATE globalsearch
SET srch_field = isnull(rtrim(title),'') +
'~' + isnull(CASE WHEN actors_keyname <> 'unknown' THEN actors_keyname ELSE NULL END,'') +
'~' + isnull(CASE WHEN directors_name <> 'unknown' THEN directors_name ELSE NULL END,'') +
'~' + isnull((ltrim(rtrim(replace(@.genres, 0,'')))),'') +
......|||

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

|||

William Lowers wrote:

I will change to a varchar(max)...

I was thinking about doing just one singe update rather than all the if statements but what about the 2 text columns cause even a varchar(max) will not allow an update using addition.

any suggestions on that?

I don't quite understand your question.

You should change all the TEXT datatypes to VARCHAR(MAX). Then they are just strings and you can use string concatination on them directly just like any other string.|||

You are correct... for some reason I remember trying the string concatination and it didn't work. no idea when or where that was but I changed to all varchar(max) and made a single update statement. now the procedure runs in under 2 minutes...

Thanks a lot.

2005 Inconsistent Results with VarBinary fields

Hello,
I seem to be encountering some inconsistent results with full text searching
against a combination of VarChar(max) and VarBinary(max) fields.
I have a table which has both a VarChar(max) field and a VarBinary(max)
field. The table is meant to either have text or an attachement (or both).
I have indexed both fields. When I do a particular search against just the
Varbinary field I get 16 results. When I do the same query, but with both
columns specified, I only get 8 results. Maybe there is a good reason for
this, but it seems like adding more fields to the search should not result
in fewer hits. And I have verified that the records it is missing truly
contain the word I am looking for. Below are the relevant queries.
Any help would be appreciated. Thanks!
Randall
-- ***** Table ****
create table dbo.z_FullTextChild (
FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
FullTextMainID uniqueidentifier not null,
LongText VarChar(max) null,
AttachmentFileName VarChar(256) null,
AttachmentFileType VarChar(15) null,
Attachment VarBinary(max),
constraint PK_FullTextChild primary key (FullTextChildID),
constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
cascade
)
go
-- ******** Full Text Index *********
create fulltext index on dbo.z_FullTextChild
(LongText, Attachment TYPE COLUMN AttachmentFileType )
key index PK_FullTextChild
on ftTracker
with CHANGE_TRACKING AUTO
go
-- ******* Query that yields 16 results ***************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******** Query that yields 8 results (only difference is in selected
columns) ******************
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (Attachment, LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
-- ******* Select @.@.Version ***********
Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005 18:22:46
Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
I have tried to repro this problem and am unable to do so. Can you perhaps
do this query?
select ftc.LongText, ftc.AttachmentFileName, ft.Rank
from z_FullTextChild ftc
join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
100) ft on ft.[KEY]=ftc.FullTextChildID
where
ftc.AttachmentFileName is not null
order by ft.Rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Randall" <randall@.randall.com> wrote in message
news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I seem to be encountering some inconsistent results with full text
searching
> against a combination of VarChar(max) and VarBinary(max) fields.
> I have a table which has both a VarChar(max) field and a VarBinary(max)
> field. The table is meant to either have text or an attachement (or
both).
> I have indexed both fields. When I do a particular search against just
the
> Varbinary field I get 16 results. When I do the same query, but with both
> columns specified, I only get 8 results. Maybe there is a good reason for
> this, but it seems like adding more fields to the search should not result
> in fewer hits. And I have verified that the records it is missing truly
> contain the word I am looking for. Below are the relevant queries.
> Any help would be appreciated. Thanks!
> Randall
> -- ***** Table ****
> create table dbo.z_FullTextChild (
> FullTextChildID uniqueidentifier ROWGUIDCOL NOT NULL default newid(),
> FullTextMainID uniqueidentifier not null,
> LongText VarChar(max) null,
> AttachmentFileName VarChar(256) null,
> AttachmentFileType VarChar(15) null,
> Attachment VarBinary(max),
> constraint PK_FullTextChild primary key (FullTextChildID),
> constraint PK_FullTextChild_FullTextMain foreign key (FullTextMainID)
> references dbo.z_FullTextMain(FullTextMainID) on update cascade on delete
> cascade
> )
> go
> -- ******** Full Text Index *********
> create fulltext index on dbo.z_FullTextChild
> (LongText, Attachment TYPE COLUMN AttachmentFileType )
> key index PK_FullTextChild
> on ftTracker
> with CHANGE_TRACKING AUTO
> go
> -- ******* Query that yields 16 results ***************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment), 'INFORMATION', 100) ft
> on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******** Query that yields 8 results (only difference is in selected
> columns) ******************
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (Attachment, LongText),
'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
> -- ******* Select @.@.Version ***********
> Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86) May 24 2005
18:22:46
> Copyright (c) 1988-2005 Microsoft Corporation Beta Edition on Windows NT
> 5.1 (Build 2600: Service Pack 2)
>
>
|||Thanks for the reply.
That particular query yields no hits because currently none of the records
that have attachments have any LongText associated with them (although that
could change in the future).
Randall
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O4eEKfuhFHA.1048@.tk2msftngp13.phx.gbl...
>I have tried to repro this problem and am unable to do so. Can you perhaps
> do this query?
> select ftc.LongText, ftc.AttachmentFileName, ft.Rank
> from z_FullTextChild ftc
> join freetexttable (z_FullTextChild, (LongText), 'INFORMATION',
> 100) ft on ft.[KEY]=ftc.FullTextChildID
> where
> ftc.AttachmentFileName is not null
> order by ft.Rank desc
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Randall" <randall@.randall.com> wrote in message
> news:uYYBzNuhFHA.2472@.TK2MSFTNGP15.phx.gbl...
> searching
> both).
> the
> 'INFORMATION',
> 18:22:46
>