Optimizing SQL - Union

Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanksIf you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@.gmail.com> wrote in message
news:1143651738.146958.160520@.t31g2000cwb.googlegr oups.com...
Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanks|||Ok, I will try this. I am a liitle bit confused about the cross join..

I will test and let you know. Thanks!|||On 29 Mar 2006 09:02:18 -0800, das wrote:

(snip)
>In order to do this I am doing a union:
>select * into #tempUpdate
>(
> select 'col1' as field, col1 as val from table1
> union
> select 'col2' as field, col2 as val from table1
> union
> select 'col3' as field, col3 as val from table1
>)
>the above example query is smaller - I have a much bigger table with
>about 80 columns (Imagine the size of my union query :) and this takes
>a lot of time to execute. Can someone please suggest a better way to do
>this?

Hi das,

Somewhat simpler than the suggestions Tom posted (and probably less
efficient, but still a major win over your present version) is the
following simple change:

select 'col1' as field, col1 as val from table1
union ALL
select 'col2' as field, col2 as val from table1
union ALL
select 'col3' as field, col3 as val from table1

UNION without ALL will attempt to remove duplicates; with large result
sets, checking for duplicates can be a major performance killer. With
UNION ALL, you say "don't attempt to remove duplicates" - either because
you want them or (in this case) because you're sure there aren't any.

--
Hugo Kornelis, SQL Server MVP|||that's a really good advice, didn't know what 'union all' meant all
these days.
I tried Thomas approach and it is much faster than before.
thanks a lot guys.|||Been away for a while. Here's a correction:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col2' union all
select 'col3' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:8izWf.1879$m35.157124@.news20.bellglobal.com.. .
If you have SQL 2005, you can use UNPIVOT. If you are using earlier
releases, try:

select
m.id
, x.col
, case x.col
when 1 then m.col1
when 2 then m.col2
when 3 then m.col3
when 4 then m.col4
end as val
from
MyTable m
cross join
(
select 'col1' union all
select 'col1' union all
select 'col1' union all
select 'col4'
) as x (col)

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"das" <Adityanad@.gmail.com> wrote in message
news:1143651738.146958.160520@.t31g2000cwb.googlegr oups.com...
Hello all,

I have a table with thousands of rows and is in this format:

id col1 col2 col3 col4
-- -- -- -- --
1 nm 78 xyz pir
2 bn 45 abc dir

I now want to get the data from this table in this format:

field val
--------
col1 nm
col1 bn
col2 78
col2 45
col3 xyz
col3 abc
col4 pir
col4 dir

In order to do this I am doing a union:

select * into #tempUpdate
(
select 'col1' as field, col1 as val from table1
union
select 'col2' as field, col2 as val from table1
union
select 'col3' as field, col3 as val from table1
)

the above example query is smaller - I have a much bigger table with
about 80 columns (Imagine the size of my union query :) and this takes
a lot of time to execute. Can someone please suggest a better way to do
this?

The results of this union query are selected into a temp table, which I
then use to update another table. I am using SQL Server 2000.

my main concern is performance. any ideas please?

thanks|||THANKS THOMAS!sql

Optimizing a JOIN

I have two tables.

One has approx 90,000 rows with a field .. let's call in BigInt (and it
is defined as a bigint data type).

I have a reference table, with approx 10,000,000 rows. In this
reference table, I have starting_bigint and ending_bigint fields. I
want to pull out all of the reference data from the reference table for
all 90,000 rows in the transaction table where the BigInt from the
transaction table is between the starting_bigint and ending_bigint in
the reference table.

I have the join working now, but it is not as optimized as I would
like. It appears no matter what I do, the query does a full table scan
on the 10,000,000 rows in the reference table.

Sample code

SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref on tran.bigint between
ref.starting_bigint and ending_bigint

Yes, all 3 of the fields are indexed. I even have a composite index on
the reference table with the starting_bigint and ending_bigint fields
selected as the composite.

Any help would be appreciated.

Robert H. Kershberg
IT Director
Tax Credit Company
RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.comIf the starting and ending bigints ranges are not overlapping, then I
would classify this as the "zipcode problem".

If this is the case, you could try the following approach:

SELECT R1.*,T1.bigint
FROM transactiontable T1
CROSS JOIN referencetable R1
WHERE R1.starting_bigint = (
SELECT MAX(starting_bigint)
FROM referencetable R2
WHERE R2.starting_bigint <= T1.bigint
)
AND R1.ending_bigint >= T1.bigint

Hope this helps,
Gert-Jan

"rkershberg@.gmail.com" wrote:
> I have two tables.
> One has approx 90,000 rows with a field .. let's call in BigInt (and it
> is defined as a bigint data type).
> I have a reference table, with approx 10,000,000 rows. In this
> reference table, I have starting_bigint and ending_bigint fields. I
> want to pull out all of the reference data from the reference table for
> all 90,000 rows in the transaction table where the BigInt from the
> transaction table is between the starting_bigint and ending_bigint in
> the reference table.
> I have the join working now, but it is not as optimized as I would
> like. It appears no matter what I do, the query does a full table scan
> on the 10,000,000 rows in the reference table.
> Sample code
> SELECT ref.*, tran.bigint
> from transactiontable tran
> INNER JOIN referencetable ref on tran.bigint between
> ref.starting_bigint and ending_bigint
> Yes, all 3 of the fields are indexed. I even have a composite index on
> the reference table with the starting_bigint and ending_bigint fields
> selected as the composite.
> Any help would be appreciated.
> Robert H. Kershberg
> IT Director
> Tax Credit Company
> RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.com|||try skipping the "between." something like
SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref
on tran.bigint >= ref.starting_bigint
and tran.bigint <=ref.ending_bigint

try creating a composite index on ref, containing starting_bigint plus
ending_bigint
if that doesn't work, try zapping your existing indexes on ref.

thinking outside the box, try creating staging tables, or ghost tables.
The ghost tables are for a selected period taht you are currently
working on. You copy pieces of your trans table off to a temp table,
and do your lookups against it. you eat the one copy, you eat the
creation of the indexon the temp table, but you get the benefits of not
having to do complex searches against a 100,000,000 row table. this is
RARELY needed, but I've done it on rare occasion.|||Thank you for your time. I had tried a cross join, but not quite the
same way. It'll be interesting to see if there is a significant
difference. I appreciate your intelligent input.

Rob|||Thanks again .. this logic improved performance 3 to 4 fold (I had
already cut a 2 min 30 sec query to 15 seconds .. your logic
implemented on top of mine cut that down to 3 seconds .. this on 88,000
row transacation table running against a 9.6 million row reference
table .. not zip codes, but the right idea <g>).

Thank YOU very much!!!

Rob|||3 seconds is a long time.

how do we make it faster??

i am not a huge fan of clustered indexes. However for a reference
table where you will ALWAYS be looking up data utilizing a specific
column, and rarely inserting into the middle, I'd sure consider it.

For grins, try creating a clustered, composite index on starting_bigint
+ ending_bigint.

For grins after that, consider a clustered index on trans.bigint.
warning: clustered indexes on trans tables are usually not a great idea
IMO, unless the clustered index is based on a timestamp or an identity
column.

-doug

Optimizing a big query

To start with, I'll give a simplified overview of my data.

BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc

OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data

ProblemTable (4mil rows)
ID | ConcatenatedHistory

The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'

Using left outer joins (all from base, the rest optional) I've got a
view something like:

View (4mil rows)
ID | Name | Region | etc | Data | Data2 | ConcatenatedHistory

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58;

But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58
and ConcatenatedHistory like '%test%' ;

What puzzles me most is that it's taking longer to return nothing.

I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.

The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code

Quote:

Originally Posted by

>From View inner join History on History.ID = View.ID

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.

~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.

If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.On Feb 8, 4:05 pm, "Merennulli" <mar...@.sdf.lonestar.orgwrote:

Quote:

Originally Posted by

To start with, I'll give a simplified overview of my data.
>
BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc
>
OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data
>
ProblemTable (4mil rows)
ID | ConcatenatedHistory
>
The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'
>
Using left outer joins (all from base, the rest optional) I've got a
view something like:
>
View (4mil rows)
ID | Name | Region | etc | Data | Data2 | ConcatenatedHistory
>
Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View
>
Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;
>
Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View
>
Where Region = 58;
>
But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View
>
Where Region = 58
and ConcatenatedHistory like '%test%' ;
>
What puzzles me most is that it's taking longer to return nothing.
>
I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.
>
The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.
>
Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code>From View inner join History on History.ID = View.ID
>
Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';
>
I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.
>
~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.
>
If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.

do you query the concatenated data, "normalized", in a way that you
could use FULL TEXT ?|||I expect that the normalized approach can perform much better than parsing
ConcatenatedHistory using LIKE as long as you have the proper indexes in
place and tune your queries. However it's difficult to make recommendations
without the actual DDL of your existing objects.

Quote:

Originally Posted by

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code

Quote:

Originally Posted by

>>From View inner join History on History.ID = View.ID

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

You might be able to reformulate this query as something like the example
below:

SELECT View.ID, View.Name, View.Region, View.etc, View.Data, View.Data2,
History.Date, History.Code
FROM View
INNER JOIN History ON
History.ID = View.ID
WHERE
View.Region = 58 AND
History.Date = '11/28/2006'
UNION
SELECT View.ID, View.Name, View.Region, View.etc, View.Data, View.Data2,
History.Date, History.Code
FROM View
INNER JOIN History ON
History.ID = View.ID
WHERE
View.Region = 58 AND
History.Code = '2007X';

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Merennulli" <maross@.sdf.lonestar.orgwrote in message
news:1170979522.693854.323490@.s48g2000cws.googlegr oups.com...

Quote:

Originally Posted by

To start with, I'll give a simplified overview of my data.
>
BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc
>
OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data
>
ProblemTable (4mil rows)
ID | ConcatenatedHistory
>
The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'
>
Using left outer joins (all from base, the rest optional) I've got a
view something like:
>
View (4mil rows)
ID | Name | Region | etc | Data | Data2 | ConcatenatedHistory
>
Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;
>
Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58;
>
But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58
and ConcatenatedHistory like '%test%' ;
>
What puzzles me most is that it's taking longer to return nothing.
>
I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.
>
The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.
>
Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code

Quote:

Originally Posted by

>>From View inner join History on History.ID = View.ID

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';
>
I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.
>
~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.
>
If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.
>

|||On Feb 8, 7:27 pm, "Steve" <morrisz...@.hotmail.comwrote:

Quote:

Originally Posted by

On Feb 8, 4:05 pm, "Merennulli" <mar...@.sdf.lonestar.orgwrote:
>
>
>
>
>

Quote:

Originally Posted by

To start with, I'll give a simplified overview of my data.

>

Quote:

Originally Posted by

BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc

>

Quote:

Originally Posted by

OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data

>

Quote:

Originally Posted by

ProblemTable (4mil rows)
ID | ConcatenatedHistory

>

Quote:

Originally Posted by

The concatenated history field is a nvarchar with up to 20 different
pipe delimited date/code combinations, eg. '01/01/2007X|11/28/2006Q|
11/12/2004Q|'

>

Quote:

Originally Posted by

Using left outer joins (all from base, the rest optional) I've got a
view something like:

>

Quote:

Originally Posted by

View (4mil rows)
ID | Name | Region | etc | Data | Data2 | ConcatenatedHistory

>

Quote:

Originally Posted by

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View

>

Quote:

Originally Posted by

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

>

Quote:

Originally Posted by

Or to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View

>

Quote:

Originally Posted by

Where Region = 58;

>

Quote:

Originally Posted by

But this takes over a minute:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory>From View

>

Quote:

Originally Posted by

Where Region = 58
and ConcatenatedHistory like '%test%' ;

>

Quote:

Originally Posted by

What puzzles me most is that it's taking longer to return nothing.

>

Quote:

Originally Posted by

I've tried normalizing this concatenated field into it's own table, or
into 20 and 40 denormalized fields. The denormalized fields were
nightmarishly long queries for a web interface at 5-6 minutes.

>

Quote:

Originally Posted by

The normalized table should have roughly 25mil records, but cutting it
down to just the most relevant years let me play with it at 9.5mil
records. This shifted the results to where it took 35-40 seconds to do
ANY query against that table.

>

Quote:

Originally Posted by

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code>From View inner join History on History.ID = View.ID

>

Quote:

Originally Posted by

Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

>

Quote:

Originally Posted by

I also tried reducing this table down to a linking table between ID
and Code, and pushing the date off to another table, but that only
made things worse.

>

Quote:

Originally Posted by

~~~
Going back to what worked best (the intial View), the Execution Plan
shows 93% on a Clustered Index Scan on the ID field's index in the
ConcatenatedHistory table for the problem query, but spreads out the
load fairly evenly among indexes on the successful query. I'm trying
to figure out a way to improve performance, and more importantly, make
"0 records found" responses be a bit more forthcoming.

>

Quote:

Originally Posted by

If it's relevant, I'm on SQL Server 2005 Standard, and I've already
taken care of the memory, CPU and drive optimization.

>
do you query the concatenated data, "normalized", in a way that you
could use FULL TEXT ?- Hide quoted text -
>
- Show quoted text -

Steve,

The "normalized" data broke the code and date into a datetime field
and a 5 character char (eg. '2006X'). With that change, I was doing
exact matching instead of "like" comparisons. As far as my knowledge
extends, that should have more than compensated for the table size
difference (4mil vs 25mil and later vs 9mil). My first assumption was
that the indexes were wrong, but a non-clustered index on the ID and
Code fields should have been correct for this. I can try a clustered
on the pair, but I don't see where that would improve performance.|||Merennulli (maross@.sdf.lonestar.org) writes:

Quote:

Originally Posted by

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

I completely agree with Dan that normalising ConcatenatedHistory into
its own table, would give you better performance. But not know the
tables or indexes its difficult to say why your attempt failed.

The one thing I can suggest to improve the speed of the current
query is that you add a COLLATE clause to force a binary collation:

ConcatenatedHistory LIKE '%2007%' COLLATE Latin1_General_BIN

this is particular important if you use a Windows collation or your
column is varchar.

Quote:

Originally Posted by

Select View.ID, View.Name, View.Region, View.etc, View.Data,
View.Data2, History.Date, History.Code
From View inner join History on History.ID = View.ID
Where View.Region = 58
and History.Date = '11/28/2006' or History.Code = '2007X';

I don't really know what this code is. Isn't that just a date or
rather a period? And is that really the WHERE clause? Or should it
be:

Where View.Region = 58
(and History.Date = '11/28/2006' or History.Code = '2007X')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Feb 10, 10:55 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Merennulli (mar...@.sdf.lonestar.org) writes:

Quote:

Originally Posted by

Querying it, it takes about 15-20 seconds to do this:
Select ID, Name, Region, etc, Data, Data2, ConcatenatedHistory

Quote:

Originally Posted by

>From View

Where Region = 58
and ConcatenatedHistory like '%11/28/2006%' or ConcatenatedHistory
like '%2007X%' ;

>
I completely agree with Dan that normalising ConcatenatedHistory into
its own table, would give you better performance. But not know the
tables or indexes its difficult to say why your attempt failed.

Sorry, it looks like my last message in response to Dan didn't go
through.
The problem was indeed my index. It failed because I had the ID first
and the date second.
Flipping the field order in the index brought my time down to about
5-8 seconds.

My thought had been that the query would use start with the other side
of the join - the view, narrow it down first and tie to the index
values, then find the date out of the remaining small section of the
index. Instead it seems it started with the opposite side of the join
from what I expected. Because of that it was within an average of 10
places of being enforced as completely random from the date field's
perspective.

Thanks for pointing me in the right direction.

optimizer problem

Hi,
We have a table having 3.2 million rows having primary key
clustered index on id column ...update statistics is done
with fullscan(100%)...
when we are running:
select count(*) from table1 ...it is taking about 4
minutes to return the result...when i see the statistics
io it shows that it is doing scan count:728...
How can this be doing scan count 728 on 2 cpu machine and
takes 4 min just to return count?
Thanks
--HarvinderIf it actually is a scan count of 728, that is not the same as Logical
reads. It means that SQL Server is accessing the table 728 times, and this
usually implies some sort of join.
Can you SET STATISTICS PROFILE ON and show us the output so we can see the
query plan in addition to the statistics?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> Hi,
> We have a table having 3.2 million rows having primary key
> clustered index on id column ...update statistics is done
> with fullscan(100%)...
> when we are running:
> select count(*) from table1 ...it is taking about 4
> minutes to return the result...when i see the statistics
> io it shows that it is doing scan count:728...
> How can this be doing scan count 728 on 2 cpu machine and
> takes 4 min just to return count?
> Thanks
> --Harvinder
>|||That was my other question...howcome it is doing 728 scan
count instead of 1 clustered index scan...i am pasting the
output of showplan :
select count(*) from tab1
|--Compute Scalar(DEFINE:([Expr1002]=Convert
([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM
([partialagg1003])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:
([partialagg1003]=Count(*)))
|--Clustered Index Scan(OBJECT:([dm].
[dbo].[tab1].[pk_tab1]))
Thanks
--Harvinder
>--Original Message--
>If it actually is a scan count of 728, that is not the
same as Logical
>reads. It means that SQL Server is accessing the table
728 times, and this
>usually implies some sort of join.
>Can you SET STATISTICS PROFILE ON and show us the output
so we can see the
>query plan in addition to the statistics?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"harvinder" <hs@.metratech.com> wrote in message
>news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
>> Hi,
>> We have a table having 3.2 million rows having primary
key
>> clustered index on id column ...update statistics is
done
>> with fullscan(100%)...
>> when we are running:
>> select count(*) from table1 ...it is taking about 4
>> minutes to return the result...when i see the
statistics
>> io it shows that it is doing scan count:728...
>> How can this be doing scan count 728 on 2 cpu machine
and
>> takes 4 min just to return count?
>> Thanks
>> --Harvinder
>
>.
>|||I was actually hoping for the STATISTICS PROFILE output in addition to the
exact STATISTICS IO that I assumed you were already collecting.
My guess at this point (without seeing the STATISTICS IO output) is that
the high scan count is related to the fact that the query is being processed
in parallel.
The large amount of time is probably because of the clustered index scan. A
clustered index scan is exactly the same as a table scan, so to get the
results of count(*) SQL Server has to look at every row on every page. How
many rows and how many pages are in this table? Does the query include a
WHERE clause? What is the result of your count(*) query?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:051501c35230$b8aed800$a301280a@.phx.gbl...
> That was my other question...howcome it is doing 728 scan
> count instead of 1 clustered index scan...i am pasting the
> output of showplan :
> select count(*) from tab1
> |--Compute Scalar(DEFINE:([Expr1002]=Convert
> ([globalagg1004])))
> |--Stream Aggregate(DEFINE:([globalagg1004]=SUM
> ([partialagg1003])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:
> ([partialagg1003]=Count(*)))
> |--Clustered Index Scan(OBJECT:([dm].
> [dbo].[tab1].[pk_tab1]))
> Thanks
> --Harvinder
> >--Original Message--
> >If it actually is a scan count of 728, that is not the
> same as Logical
> >reads. It means that SQL Server is accessing the table
> 728 times, and this
> >usually implies some sort of join.
> >Can you SET STATISTICS PROFILE ON and show us the output
> so we can see the
> >query plan in addition to the statistics?
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> We have a table having 3.2 million rows having primary
> key
> >> clustered index on id column ...update statistics is
> done
> >> with fullscan(100%)...
> >> when we are running:
> >> select count(*) from table1 ...it is taking about 4
> >> minutes to return the result...when i see the
> statistics
> >> io it shows that it is doing scan count:728...
> >> How can this be doing scan count 728 on 2 cpu machine
> and
> >> takes 4 min just to return count?
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >|||if your system is a Xeon or Xeon MP, and HT is enabled,
and you have a parallel execution plan
try OPTION (MAXDOP 1)
better yet, disabled HT
>--Original Message--
>Hi,
>We have a table having 3.2 million rows having primary
key
>clustered index on id column ...update statistics is done
>with fullscan(100%)...
>when we are running:
>select count(*) from table1 ...it is taking about 4
>minutes to return the result...when i see the statistics
>io it shows that it is doing scan count:728...
>How can this be doing scan count 728 on 2 cpu machine and
>takes 4 min just to return count?
>Thanks
>--Harvinder
>.
>

Optimize Linked Server to Oracle

I have a linked server to Oracle (I have one using MS and one Using Oracle
Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
there any way to speed this up, and why does oracle always ruin my day ?
- DavidIt depends on the query being executed, capabilities of the
driver or provider you are using, connection between the
servers, etc. Have you tried using an OLE DB provider
instead of an ODBC driver? Check the network connection
between the two servers. You may want to go through the
following topic in books online:
Optimizing Distributed Queries
In addition, the following link has some suggestions:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Mon, 19 Sep 2005 14:56:15 -0400, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:

>I have a linked server to Oracle (I have one using MS and one Using Oracle
>Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
>there any way to speed this up, and why does oracle always ruin my day ?
>- David
>

Optimize Linked Server to Oracle

I have a linked server to Oracle (I have one using MS and one Using Oracle
Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
there any way to speed this up, and why does oracle always ruin my day ?
- David
It depends on the query being executed, capabilities of the
driver or provider you are using, connection between the
servers, etc. Have you tried using an OLE DB provider
instead of an ODBC driver? Check the network connection
between the two servers. You may want to go through the
following topic in books online:
Optimizing Distributed Queries
In addition, the following link has some suggestions:
http://www.sql-server-performance.com/linked_server.asp
-Sue
On Mon, 19 Sep 2005 14:56:15 -0400, "David J. Cartwright"
<davidcartwright@.hotmail.com> wrote:

>I have a linked server to Oracle (I have one using MS and one Using Oracle
>Driver), both take about 20+mins to return 114k rows using OPENQUERY, is
>there any way to speed this up, and why does oracle always ruin my day ?
>- David
>

optimization question

I have a table with a large amount of rows > 100 000 with
ID|NAME|HITScolumns
ID is the primary key
NAME is nvarchar
HITS is int
I run this query, it takes less than a second
select top 40 * from table 1 order by id desc
but when i run this it take more than 10 seconds
select top 40 * from table 1 order by hits desc
hits column keeps track of time the page has been loaded
what can i do to make the second query run just as fast as the first one?Do you have an index on HIts? If not then you might want to add one.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I have a table with a large amount of rows > 100 000 with
> ID|NAME|HITScolumns
> ID is the primary key
> NAME is nvarchar
> HITS is int
> I run this query, it takes less than a second
> select top 40 * from table 1 order by id desc
> but when i run this it take more than 10 seconds
> select top 40 * from table 1 order by hits desc
>
> hits column keeps track of time the page has been loaded
> what can i do to make the second query run just as fast as the first one?
>
>|||what type of index would you recommend? I ran the Database Engine Tuning
Advisor it didn't give me anything
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
> Do you have an index on HIts? If not then you might want to add one.
> --
> Andrew J. Kelly SQL MVP
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>|||You really only have two choices here. A clustered or a Nonclustered index.
I assume you already have a clustered index on ID. So try adding a
non-clustered index on Hits and see if it helps.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:e$EfRUSUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> what type of index would you recommend? I ran the Database Engine Tuning
> Advisor it didn't give me anything
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>

Optimization

I just wanna know what is the optimized way for fast retrieval if there are
billions of rows in a single table.
Can any one let me know any idea except table partitioning for the fast
retrieval of data
Thanks in advance.
Hi
Optimization needs to take into account the whole environment of your
application and be part of your design. This will include hardware as well
as the software. Partitioned views (see Books online) may be an option if
you have the hardware to run them on, if you are not wanting to change the
design look at what indexes are available. Other things to look at would be
using table variables or temporary to reduce the size of data you are trying
to manipulate.
John
"John" <naissani@.hotmail.com> wrote in message
news:%23Hhk6pJMFHA.1472@.TK2MSFTNGP14.phx.gbl...
>I just wanna know what is the optimized way for fast retrieval if there are
> billions of rows in a single table.
> Can any one let me know any idea except table partitioning for the fast
> retrieval of data
> Thanks in advance.
>
|||Thanks John but can you explain me more about Other things to look at would
be using table variables or temporary to reduce the size of data you are
trying
to manipulate.
Thanks.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:#vpfVfSMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> Optimization needs to take into account the whole environment of your
> application and be part of your design. This will include hardware as well
> as the software. Partitioned views (see Books online) may be an option if
> you have the hardware to run them on, if you are not wanting to change the
> design look at what indexes are available. Other things to look at would
be
> using table variables or temporary to reduce the size of data you are
trying[vbcol=seagreen]
> to manipulate.
> John
> "John" <naissani@.hotmail.com> wrote in message
> news:%23Hhk6pJMFHA.1472@.TK2MSFTNGP14.phx.gbl...
are
>
|||Hi
By using a temporary table to restrict the data you are looking at, it
may be possible to create a more proformant than it would be otherwise.
e.g
http://www.windowsitpro.com/SQLServe...hreadid=102472
With SQL 2000 table variables were introduced as an alternative to
temporary tables.
http://www.windowsitpro.com/Articles...layTab=Article
Using temporary tables may introduce it's own problems
http://www.sql-server-performance.co...ottlenecks.asp
and using them unnecessarily may also degrade performance
http://www.sql-server-performance.co...ved_tables.asp
Therefore you would have to try out different methods to see what is
the best in your circumstances.
John
John wrote:
> Thanks John but can you explain me more about Other things to look at
would
> be using table variables or temporary to reduce the size of data you
are[vbcol=seagreen]
> trying
> to manipulate.
> Thanks.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:#vpfVfSMFHA.1176@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
as well[vbcol=seagreen]
option if[vbcol=seagreen]
change the[vbcol=seagreen]
would[vbcol=seagreen]
> be
are[vbcol=seagreen]
> trying
there[vbcol=seagreen]
> are
the fast[vbcol=seagreen]

Optimise multitable update

Hi
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
Sean
On 25 May 2004 08:14:42 -0700, Sean wrote:

>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
>TableA.Code = TableB.Code
>AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Optimise multitable update

Hi
I've got the following scenario:
TableA (4 million rows)
TableB (20 000 rows)
I have two fields on TableA that are the unique fields on TableB,
which I use to set the foreign key from A to B:
UPDATE TableA
SET TableA.B_FK = TableB.B_PK
FROM TableA, TableB
WHERE
TableA.Code = TableB.Code
AND TableA.Name = TableB.Name
Code = varchar(5)
Name = varchar(50)
What would a suitable indexes be to optimise this query as it takes 4
hours to run?
I already have an index on TableA on "Code, Name" and TableB on "B_PK"
- takes 4 hours with these!
Any help? Should I have a covering index on TableB, i.e. "Code, Name,
B_PK" ?
Thanks
SeanOn 25 May 2004 08:14:42 -0700, Sean wrote:
>Hi
>I've got the following scenario:
>TableA (4 million rows)
>TableB (20 000 rows)
>
>I have two fields on TableA that are the unique fields on TableB,
>which I use to set the foreign key from A to B:
>UPDATE TableA
>SET TableA.B_FK = TableB.B_PK
>FROM TableA, TableB
>WHERE
> TableA.Code = TableB.Code
> AND TableA.Name = TableB.Name
>
>Code = varchar(5)
>Name = varchar(50)
>What would a suitable indexes be to optimise this query as it takes 4
>hours to run?
>I already have an index on TableA on "Code, Name" and TableB on "B_PK"
>- takes 4 hours with these!
>Any help? Should I have a covering index on TableB, i.e. "Code, Name,
>B_PK" ?
>Thanks
>Sean
Hi Sean,
Is the current index on TableA(Code, Name) a clustered index? Is it
defined as a unique index?
Do all 20000 rows in TableB match a row in TableA? If so, adding an index
on TableB won't do you any good. If all rows in a table have to be
processed anyway, a table scan is always the best way. If only a few of
the 20000 rows will match, an index on TableB(Code, Name) *might* help,
but I'm not sure. Test it. The covering index you suggest *might* help as
well, but you'll have to test that as well. But, as I said - only if the
majority of rows in TableB will not match against TableA.
Is there an index on TableA(B_FK)? If it is, see if you can remove it;
that saves the time to update this index as the update is carried out.
Check that there are no triggers on TableA. (If you have them, can't
disable them and they're the cause of the long execution, forget about the
query and start optimising the triggers first!)
And the most important thing: Check the execution plan!! From your
description, I would expect a table scan of TableB and an index seek on
the index on TableA(CodaA, Name).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Opinons on using Delete then BULK INSERT

Hi. Just looking for your opinions on a technique I have seen for using BULK
INSERT. It reads through the flat file first deleting rows in the target
table that if finds. It then issues the BULK INSERT command on the same flat
file. This has the effect of doing an insert or update.
The DBA reports that the tables tend to become more fragmented because of
the deletes rather than using update.
Are there any pitfalls to this approach? Does it sound OK?
Are there other approaches other than Transact SQL for inserting or updating
large amounts of flat file data?
Thanks!!
McGy
[url]http://mcgy.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in message
news:eudcIdGPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi. Just looking for your opinions on a technique I have seen for using
> BULK INSERT. It reads through the flat file first deleting rows in the
> target table that if finds. It then issues the BULK INSERT command on the
> same flat file. This has the effect of doing an insert or update.
> The DBA reports that the tables tend to become more fragmented because of
> the deletes rather than using update.
> Are there any pitfalls to this approach? Does it sound OK?
> Are there other approaches other than Transact SQL for inserting or
> updating large amounts of flat file data?
> Thanks!!
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
>
It depends on how big the tables are and how big the flat file is.
Personally, I would probably BULK INSERT the flat file into a working table
first. Then using a chunking method, I would update/insert about 10,000
rows at a time until the task completed. This has the advantage of letting
SQL Server do what it does best using SET theory for determining updates and
inserts, but also doing it in small enough chunks that other processing can
continue with only slight interruption.
Just my .02
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the pointer Rick.
McGy
[url]http://mcgy.blogspot.com[/url]
"Rick Sawtell" <Quickening@.msn.com> wrote in message
news:%23pxvSgGPGHA.1124@.TK2MSFTNGP10.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:eudcIdGPGHA.3984@.TK2MSFTNGP14.phx.gbl...
> It depends on how big the tables are and how big the flat file is.
> Personally, I would probably BULK INSERT the flat file into a working
> table first. Then using a chunking method, I would update/insert about
> 10,000 rows at a time until the task completed. This has the advantage of
> letting SQL Server do what it does best using SET theory for determining
> updates and inserts, but also doing it in small enough chunks that other
> processing can continue with only slight interruption.
> Just my .02
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>

Operation canceled

Hi,
I have a stored procedure which can take quite a long time to process. this
SP is run from a sql server Job.
The SP loops through rows in a table and stores the primary key values of
the rows that where processed in a table varaible such that when the loop
finishes it executes an update statment on the main table to flag each
processed row as processed!
My concerns is that if the user stops the Job while the loop is doing its
work, then the updated rows will not be flagged.
is there a way to trap the cancel request and perform the update statement
before quitting the SP?
regards,Emmanuel
Deny the user symin privileges.
"Emmanuel" <emmanuel@.email.com> wrote in message
news:%23yLvVSgKFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a stored procedure which can take quite a long time to process.
this
> SP is run from a sql server Job.
> The SP loops through rows in a table and stores the primary key values of
> the rows that where processed in a table varaible such that when the loop
> finishes it executes an update statment on the main table to flag each
> processed row as processed!
> My concerns is that if the user stops the Job while the loop is doing its
> work, then the updated rows will not be flagged.
> is there a way to trap the cancel request and perform the update statement
> before quitting the SP?
> regards,
>

OPENXML won't return rows

I am trying to insert a row from an XML document into a table. Here is
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
Here is the code to get the data from the document and insert it:
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.
You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegr oups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> Here is the code to get the data from the document and insert it:
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>
|||That was it! Thanks very much, Graeme.

OPENXML won't return rows

I am trying to insert a row from an XML document into a table. Here is
the table structure:
CREATE TABLE dbo.Customer(
CustomerGUID uniqueidentifier NOT NULL,
CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
Version int NULL,
ActivatedDate smalldatetime NULL,
ActivatedByWS uniqueidentifier NULL,
DeactivatedDate smalldatetime NULL,
DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
UpdatedByWS uniqueidentifier NULL,
CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
PRIMARY KEY CLUSTERED
(
CustomerGUID ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
UNIQUE NONCLUSTERED
(
CustomerName ASC
)WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
) ON PRIMARY
GO
---
Here is the code to get the data from the document and insert it:
---
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer')
WITH Customer
EXEC sp_xml_removedocument @.docHandle
GO
I get this result:
CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
-- -- -- -- --
NULL NULL NULL NULL NULL
I think that I am not describing the XML correctly to the XML variable,
but I don't know what to do next. Any ideas? Thanks.You need to specify the flags parameter to tell OPENXML to look for elements
instead of attributes. The default Flags value is 1, which is attributes -
use 2 for elements, or 3 for both (or better yet use colpatterns in a table
def in the WITH clause).
DECLARE @.docHandle int
declare @.xmlDocument xml
set @.xmlDocument = N'<ROOT>
<Customer>
<CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
<CustomerType>CU</CustomerType>
<SalesOfficeName>Roadway Main</SalesOfficeName>
<UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
<ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
-- comment out the actual INSERT
--INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS, ActivatedDate)
SELECT CustomerGUID, CustomerType, SalesOfficeName,
UpdatedByWS,ActivatedDate
FROM OPENXML(@.docHandle, N'/ROOT/Customer', 2)
WITH Customer
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
<googleThis@.nadolna.net> wrote in message
news:1132684803.666917.108890@.g47g2000cwa.googlegroups.com...
>I am trying to insert a row from an XML document into a table. Here is
> the table structure:
> CREATE TABLE dbo.Customer(
> CustomerGUID uniqueidentifier NOT NULL,
> CustomerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> Version int NULL,
> ActivatedDate smalldatetime NULL,
> ActivatedByWS uniqueidentifier NULL,
> DeactivatedDate smalldatetime NULL,
> DeactivatedByWS varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> UpdatedByWS uniqueidentifier NULL,
> CustomerType char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
> SalesOfficeName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL,
> PRIMARY KEY CLUSTERED
> (
> CustomerGUID ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY,
> UNIQUE NONCLUSTERED
> (
> CustomerName ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON PRIMARY
> ) ON PRIMARY
> GO
>
> ---
> Here is the code to get the data from the document and insert it:
> ---
> DECLARE @.docHandle int
> declare @.xmlDocument xml
> set @.xmlDocument = N'<ROOT>
> <Customer>
> <CustomerGUID>05062E6D-453B-4CAE-9EA4-BAF7D00E4235</CustomerGUID>
> <CustomerType>CU</CustomerType>
> <SalesOfficeName>Roadway Main</SalesOfficeName>
> <UpdatedByWS>D7DA4883-656D-4923-9C5C-FD83EBE6F1BE</UpdatedByWS>
> <ActivatedDate>2005-11-20T00:00:00</ActivatedDate>
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.docHandle OUTPUT, @.xmlDocument
> -- comment out the actual INSERT
> --INSERT INTO Customer (CustomerGUID, CustomerType, SalesOfficeName,
> UpdatedByWS, ActivatedDate)
> SELECT CustomerGUID, CustomerType, SalesOfficeName, UpdatedByWS,
> ActivatedDate
> FROM OPENXML(@.docHandle, N'/ROOT/Customer')
> WITH Customer
> EXEC sp_xml_removedocument @.docHandle
> GO
> I get this result:
> CustomerGUID CustomerType SalesOfficeName UpdatedByWS ActivatedDate
> -- -- -- -- --
> NULL NULL NULL NULL NULL
> I think that I am not describing the XML correctly to the XML variable,
> but I don't know what to do next. Any ideas? Thanks.
>|||That was it! Thanks very much, Graeme.

OPENXML Question

How do I return the number of rows inserted/updated using OPENXML?
I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
Generic update example trying to return the # of rows updated:
declare @.i int
exec sp_xml_preparedocument @.i output,
'<mydata>
<test xmlID="3" xmlData="blah blah blah"/>
<test xmlID="1" xmlData="blah"/>
</mydata>'
update test
set test.xmlData = ox.xmlData
from OpenXml(@.i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID
RETURN @.@.ROWCOUNT --Returns a 0
exec sp_xml_removedocument @.i
Thanks,It will return the rowcount.
Can you check if the data was really updated.
I think its the data problem.
Or try using print @.@.rowcount as see.
"Robert" wrote:

> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>|||Check my procedure here:
It works for me (rowcount stuff that is)
if exists (select * from sysobjects
where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4)
drop procedure uspTitleUpdate
GO
CREATE PROCEDURE dbo.uspTitleUpdate (
@.xml_doc TEXT ,
@.numberRowsAffected int output --return
)
AS
SET NOCOUNT ON
DECLARE @.hdoc INT -- handle to XML doc
DECLARE @.errorTracker int -- used to "remember" the @.@.ERROR
DECLARE @.updateRowCount int
DECLARE @.insertRowCount int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.XML_Doc
-- build a table (variable table) to store the xml-based result set
DECLARE @.titleupdate TABLE (
identityid int IDENTITY (1,1) ,
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate datetime ,
--used to differeniate between existing (update) and new ones (insert)
alreadyExists bit DEFAULT 0
)
--the next call will take the info IN the @.hdoc(with is the holder for
@.xml_doc), and put it IN a variableTable
INSERT @.titleupdate
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate ,
alreadyExists
)
SELECT
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
dbo.udf_convert_xml_date_to_datetime (pubdate) ,
0
FROM
-- use the correct XPath .. the second arg ("2" here) distinquishes
-- between textnode or an attribute, most times with
--.NET typed datasets, its a "2"
--This xpath MUST match the syntax of the DataSet
OPENXML (@.hdoc, '/TitlesDS/Titles', 2) WITH (
title_id varchar(6) ,
title varchar(80) ,
type varchar(32) ,
pub_id varchar(32) ,
price money ,
advance money ,
royalty varchar(32) ,
ytd_sales varchar(32) ,
notes TEXT ,
pubdate varchar(32) ,
alreadyExists bit
)
--select * from @.titleupdate
--lets differeniate between existing (update) and new ones (insert)
Update @.titleupdate
SET
alreadyExists = 1
FROM
@.titleupdate tu , titles
WHERE
--this where clause is a little weird, usually you'll must match
--primary key (int or global identifiers)
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
SET NOCOUNT OFF
Update
titles
set
title = tu.title ,
type = tu.type ,
pub_id = tu.pub_id ,
price = tu.price ,
advance = tu.advance ,
royalty = tu.royalty ,
ytd_sales = tu.ytd_sales ,
notes = tu.notes ,
pubdate = tu.pubdate
FROM
@.titleupdate tu , titles
WHERE
ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))
AND
tu.alreadyExists <> 0
Select @.updateRowCount = @.@.ROWCOUNT
INSERT INTO titles
(
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
)
Select
title_id ,
title ,
type ,
pub_id ,
price ,
advance ,
royalty ,
ytd_sales ,
notes ,
pubdate
FROM
@.titleupdate
WHERE
alreadyExists = 0
Select @.insertRowCount = @.@.ROWCOUNT
select @.numberRowsAffected = @.insertRowCount + @.updateRowCount
--select * from titles
SET NOCOUNT OFF
GO
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:3C2C4124-DEE9-4A0E-82CE-FE91CCFDC5AF@.microsoft.com...
> How do I return the number of rows inserted/updated using OPENXML?
> I tried to use the @.@.ROWCOUNT function, but it always returns a 0.
> Generic update example trying to return the # of rows updated:
> declare @.i int
> exec sp_xml_preparedocument @.i output,
> '<mydata>
> <test xmlID="3" xmlData="blah blah blah"/>
> <test xmlID="1" xmlData="blah"/>
> </mydata>'
> update test
> set test.xmlData = ox.xmlData
> from OpenXml(@.i, 'mydata/test')
> with (xmlID int, xmlData nvarchar(30)) ox
> where test.xmlID = ox.xmlID
> RETURN @.@.ROWCOUNT --Returns a 0
> exec sp_xml_removedocument @.i
>
> Thanks,
>

OPENXML question

Hi!

I am trying to import an xml file into a SQL 2005 table using sp_xml_prepareDocument ...OPENXML.

I always get 0 rows affected even though there is data in the file. The table structure is identical to the XML output. The OpenXML qry uses the following syntax:

FROM OPENXML(@.xmlHndAdd, '/NewDataSet/Table1', 1)
WITH MyTbl

The XML file format is:

<NewDataSet xmlns="">
<Table1 diffgr:id="Table11" msdata:rowOrder="0">
<program_id>1-2-3-4-5</program_id>
<object_name />
</Table1>
<Table1 diffgr:id="Table12" msdata:rowOrder="1">
<object_id>6-7-8-9-0</object_id>
<object_name>ABC</object_name>
<objectproperty_id>1-3-5-7-9</objectproperty_id>
</Table1>

Any suggestions are greatly appreciated!!!

Thank you!

Found the right syntax if anyone has similar questions:

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE mytbl

INSERT mytbl

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)

WITH mytbl

OpenXML not returning desired results

Hi all,

I have a SQL job where I do the following -

I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() -

DECLARE @.intDocINT
DECLARE @.xmlDocVARCHAR(8000)
IF(SELECTCOUNT(*)FROM DumpResults WHERE DumpResults.C1NOT IN (SELECT CleanTable.C1FROM CleanTable)) > 0
BEGIN
SET @.xmlDoc = (SELECT *FROM DumpResultsWHERE DumpResults.C1NOT IN (SELECT CleanTable.C1FROM CleanTable)FOR XML RAW)
SET @.xmlDoc ='<TABLE>' + @.xmlDoc +'</TABLE>'PRINT @.xmlDoc

EXECsp_xml_preparedocument @.intDocOUTPUT, @.xmlDoc
--INSERT INTOCleanTable(C1, C2, C3, C4, C5)SELECTC1, C2, C3, C4, C5, C6FROM OPENXML(@.intDoc,'/row',1)
WITH (C1 INT,C2CHAR(3),C3CHAR(3) ,C4FLOAT,C5INT)

EXECsp_xml_removedocument @.intDocENDELSE

Output that I get is -

<TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE
(0 row(s) affected)

SO "PRINT @.xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply

If anyone has any better method to do it, inputs are welcome.

Thanks

The issue is with your xpath.

Try it like this FROM OPENXML(@.intDoc, '/Table/row', 1)

Everything else looks to be right. Your XML has attributes and your setting your OPENXML to be with attribute centric. Just your XPATH is telling it to look at the root level for the row element, but its not at the root element.

|||

Hey guess what I had tried '/Table/row' earlier too.. But didn't work. Now once you said I looked at my query, and the place where I am appending -

SET @.xmlDoc = '<Table>' + @.xmlDoc + '</Table>'

I changed the case of "Table" and tried.. and it worked.. didn't know it was case sensitive..

Thanks

|||

Thats great,

Yes XML is very case sensitive :P