Foreign Key

Hi,

Is sql server performance affected with 7 to 8 FK on a single master table.

Thanks
ShubhangiNot at all

forceseek performance and cost...

Hi,

I have just run the 2 queries from the BOL to test the forceseek option:

USE AdventureWorks;

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d WITH(FORCESEEK)

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

When I take a look at the execution plan, the first query has a cost of 20% and the forceseek take 80% of the total cost.

Also the number of logical read jump from 1238 to 66194!!!

So the forceseek option has a bad impact on the query.

The BOL says if there is a lot of IO, using the forceseek can provide better performance... but in this case its not so clear...

can you explain more in detail when its better to use the forceseek?

Thanks.

The cost increases,but if you look, the index change of clustered index scan to clustered index seek and if you compare the estimated I/O cust and estimated CPU Cost you can see the differences.The number reduce....

|||

Actually this is expected behavior. We have approx 30k rows of Order Header with approx 120k with order lines. What you tell SQL Server in the latter query, is to ignore the fact that we are retrieving all the rows, and still use a lookup instead of a scan. So what SQL Server does (on your demand) is to take each and every order one by one and lookup the corresponding order lines. If you looked carefully at the execution plan, you'ld see that the merge join was replaced with a inner loop join. The latter is more efficient if you lookup only a few values, but when you lookup all the values a merge join is way much faster.

As I understand the FORCESEEK its a way of forcing seeking in the index the few times that SQL Server believes it has to read more or less the whole table, while it actually is going to retrieve just a few rows.

Anybody is free to correct me if I'm wrong on this one.

Edit: That said, when I run a trace it actually seems that the last query is the faster, I would guess that is since all the data already are in memory.

|||

Hints are there to be used when the optimizer produces a less than optimal plan and, despite over two decades of effort, query optimizers still can't get the 100% best plan 100% of the time.

So what is FORCESEEK intended to deal with? The optimizer is always working with imperfect data about the values in the table. And when you start doing a join using multiple predicates that include operators such as "<" and ">" its ability to accurately estimate the number of rows that will be touched declines. Particularly true if you don't have up to date statistics on some of the columns, or if you encounter one of the rare instances where the histograms don't capture enough information to do accurate estimates. Historically (dating back to the System R research project) if you don't have statistics that indicate otherwise then an operator such as ">" is considered to be true for 1/3 of the data. Thus ORing two such conditions would lead to an assumption that 2/3 of the table was going to be retrieved which clearly would lead to a scan over a seek. This is a grand oversimplification of how things work in this day and age, but the basics help you understand why the optimizer might choose the wrong plan. It might think that 2/3 of the rows must be touched while you know that based on the actual data it is more like .01%. The bottom line being, there are cases where the optimizer will think that a scan offers the best performance but where human knowledge of the data makes it clear that a seek is the better alternative.

Using the optimizer's own statistics to see if FORCESEEK is better or not is a mistake. The reason the optimizer didn't choose the seek strategy on its own is that it estimated it would take a lot more I/O and rejected that strategy. So when you use FORCESEEK and look at the estimates what you see are the (incorrect) estimates that the optimizer saw when it considered this strategy on its own. You just can't use that data, you need to look at the actual performance of the query.

Hal

sql

forceseek performance and cost...

Hi,

I have just run the 2 queries from the BOL to test the forceseek option:

USE AdventureWorks;

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

SELECT*

FROM Sales.SalesOrderHeader AS h

INNERJOIN Sales.SalesOrderDetail AS d WITH(FORCESEEK)

ON h.SalesOrderID = d.SalesOrderID

WHERE h.TotalDue > 100

AND(d.OrderQty > 5 OR d.LineTotal < 1000.00);

GO

When I take a look at the execution plan, the first query has a cost of 20% and the forceseek take 80% of the total cost.

Also the number of logical read jump from 1238 to 66194!!!

So the forceseek option has a bad impact on the query.

The BOL says if there is a lot of IO, using the forceseek can provide better performance... but in this case its not so clear...

can you explain more in detail when its better to use the forceseek?

Thanks.

The cost increases,but if you look, the index change of clustered index scan to clustered index seek and if you compare the estimated I/O cust and estimated CPU Cost you can see the differences.The number reduce....

|||

Actually this is expected behavior. We have approx 30k rows of Order Header with approx 120k with order lines. What you tell SQL Server in the latter query, is to ignore the fact that we are retrieving all the rows, and still use a lookup instead of a scan. So what SQL Server does (on your demand) is to take each and every order one by one and lookup the corresponding order lines. If you looked carefully at the execution plan, you'ld see that the merge join was replaced with a inner loop join. The latter is more efficient if you lookup only a few values, but when you lookup all the values a merge join is way much faster.

As I understand the FORCESEEK its a way of forcing seeking in the index the few times that SQL Server believes it has to read more or less the whole table, while it actually is going to retrieve just a few rows.

Anybody is free to correct me if I'm wrong on this one.

Edit: That said, when I run a trace it actually seems that the last query is the faster, I would guess that is since all the data already are in memory.

|||

Hints are there to be used when the optimizer produces a less than optimal plan and, despite over two decades of effort, query optimizers still can't get the 100% best plan 100% of the time.

So what is FORCESEEK intended to deal with? The optimizer is always working with imperfect data about the values in the table. And when you start doing a join using multiple predicates that include operators such as "<" and ">" its ability to accurately estimate the number of rows that will be touched declines. Particularly true if you don't have up to date statistics on some of the columns, or if you encounter one of the rare instances where the histograms don't capture enough information to do accurate estimates. Historically (dating back to the System R research project) if you don't have statistics that indicate otherwise then an operator such as ">" is considered to be true for 1/3 of the data. Thus ORing two such conditions would lead to an assumption that 2/3 of the table was going to be retrieved which clearly would lead to a scan over a seek. This is a grand oversimplification of how things work in this day and age, but the basics help you understand why the optimizer might choose the wrong plan. It might think that 2/3 of the rows must be touched while you know that based on the actual data it is more like .01%. The bottom line being, there are cases where the optimizer will think that a scan offers the best performance but where human knowledge of the data makes it clear that a seek is the better alternative.

Using the optimizer's own statistics to see if FORCESEEK is better or not is a mistake. The reason the optimizer didn't choose the seek strategy on its own is that it estimated it would take a lot more I/O and rejected that strategy. So when you use FORCESEEK and look at the estimates what you see are the (incorrect) estimates that the optimizer saw when it considered this strategy on its own. You just can't use that data, you need to look at the actual performance of the query.

Hal

FOR XML performance question

I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question.
--
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
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:F314E284-6E34-403C-94C1-B307682B1418@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.
----
In addition to the above I have done some further investigation. On a query
which returns the top row from a table the FOR XML method performed 83.5%
faster than the recordset version. However, when I run a where query which
I
know returns a single row the FOR XML method performance plunges and is
actually 6% slower than the recordset version.
Is SQLXML just one of those things which seems like a great idea but has no
real practical use in an enterprise environment? I find it very frustrating
that its performance is superb in some situations but is so awful in others.
Is it a work in progress?
That said, are there any resources which discuss various ways to pull data
from SQL server 2000 as(and convert to) XML format? Surely there is a bette
r
way than using a ADO recordset as described above?
Thanks.|||Hi Lee
This is hard to answer without having more specifics.
How does your FOR XML query look like? How does it compare to the previous
query, what indices do yo have on it?
Etc.
Best regards
Michael
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.
> ----
> In addition to the above I have done some further investigation. On a
> query
> which returns the top row from a table the FOR XML method performed 83.5%
> faster than the recordset version. However, when I run a where query
> which I
> know returns a single row the FOR XML method performance plunges and is
> actually 6% slower than the recordset version.
> Is SQLXML just one of those things which seems like a great idea but has
> no
> real practical use in an enterprise environment? I find it very
> frustrating
> that its performance is superb in some situations but is so awful in
> others.
> Is it a work in progress?
> That said, are there any resources which discuss various ways to pull data
> from SQL server 2000 as(and convert to) XML format? Surely there is a
> better
> way than using a ADO recordset as described above?
> Thanks.|||The query is a simple :-
SELECT stuff
FROM table
WHERE condition (optional)
FOR XML RAW
There is a single index on the primary key of the table and that was the
field I did my where clause on as described in my above posts.
"Michael Rys [MSFT]" wrote:

> Hi Lee
> This is hard to answer without having more specifics.
> How does your FOR XML query look like? How does it compare to the previous
> query, what indices do yo have on it?
> Etc.
> Best regards
> Michael
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>
>

FOR XML performance question

I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question.
--
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
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:F314E284-6E34-403C-94C1-B307682B1418@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.|||I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.
----
In addition to the above I have done some further investigation. On a query
which returns the top row from a table the FOR XML method performed 83.5%
faster than the recordset version. However, when I run a where query which
I
know returns a single row the FOR XML method performance plunges and is
actually 6% slower than the recordset version.
Is SQLXML just one of those things which seems like a great idea but has no
real practical use in an enterprise environment? I find it very frustrating
that its performance is superb in some situations but is so awful in others.
Is it a work in progress?
That said, are there any resources which discuss various ways to pull data
from SQL server 2000 as(and convert to) XML format? Surely there is a bette
r
way than using a ADO recordset as described above?
Thanks.|||Hi Lee
This is hard to answer without having more specifics.
How does your FOR XML query look like? How does it compare to the previous
query, what indices do yo have on it?
Etc.
Best regards
Michael
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.
> ----
> In addition to the above I have done some further investigation. On a
> query
> which returns the top row from a table the FOR XML method performed 83.5%
> faster than the recordset version. However, when I run a where query
> which I
> know returns a single row the FOR XML method performance plunges and is
> actually 6% slower than the recordset version.
> Is SQLXML just one of those things which seems like a great idea but has
> no
> real practical use in an enterprise environment? I find it very
> frustrating
> that its performance is superb in some situations but is so awful in
> others.
> Is it a work in progress?
> That said, are there any resources which discuss various ways to pull data
> from SQL server 2000 as(and convert to) XML format? Surely there is a
> better
> way than using a ADO recordset as described above?
> Thanks.|||The query is a simple :-
SELECT stuff
FROM table
WHERE condition (optional)
FOR XML RAW
There is a single index on the primary key of the table and that was the
field I did my where clause on as described in my above posts.
"Michael Rys [MSFT]" wrote:

> Hi Lee
> This is hard to answer without having more specifics.
> How does your FOR XML query look like? How does it compare to the previous
> query, what indices do yo have on it?
> Etc.
> Best regards
> Michael
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>
>

for xml path reverse?

just started playing with xml datatype, and I'm running into a performance issue...

I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...

select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x

Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...

Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?

thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?

There are a couple of things you can try doing.

Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]

Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||

Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

Also, as a performance hint: You may want to use

where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')

which can give you better performance than doing the cast into SQL and then the comparison.

Best regards
Michael

|||hi..

rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...

changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...

adding a for path index made very little difference ( < 5%)

CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH

Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

we have generic data audit triggers that look like this:

insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.

the select described above is used to get the audit trail of changes to a row.

we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...

thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?

If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).

Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||

Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.

The optimization can apply to your case:

1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||PugV

Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.

Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!

for xml path reverse?

just started playing with xml datatype, and I'm running into a performance issue...

I have to query an xml column which was populated by a 'for xml path' statement, and get the values back into relational tables...

select
DeletedData.value('(/row/ListingID)[1]','int') as ListingID,
DeletedData.value('(/row/ListingTypeID)[1]','int') as ListingTypeID, DeletedData.value('(/row/EventID)[1]','int') as EventID,
DeletedData.value('(/row/UserID)[1]','uniqueidentifier') as
etc.......
............
............
where DeletedData.value('(/row/ListingID)[1]','int') = x

Performance slows down considerably as the number of values retreived in the select increases which is understandable since it looks like it traverses for every value...

Is there a way to do a 'for xml path' reverse into a table variable without explicitly retreiving every value?

thanks.Do you have an XML Index? If so, what secondary XML Indexes do you have?

There are a couple of things you can try doing.

Is your data untyped (meaning there is no associated XML Schema Collection)? If so, then you should rewrite your path expressions to look like this:
(/row/ListingID/text())[1]

Also, I would recommend changing your where clause to use the XML datatype exist() method, this will maximize the effectiveness of your XML Indexes.
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1
|||

Can you give a better repro? Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

Also, as a performance hint: You may want to use

where 1= col.exist('/row/ListingID/text()[. = sql:column("x")]')

which can give you better performance than doing the cast into SQL and then the comparison.

Best regards
Michael

|||hi..

rewriting the expression as (/row/ListingID/text())[1] improved performance by about 25%...

changing the where clause to
where DeletedData.exist('/row/ListingID/text()[.=sql:variable("@.x")]') = 1 didn't make any difference...

adding a for path index made very little difference ( < 5%)

CREATE PRIMARY XML INDEX idx_DeletedData on audit (DeletedData)
CREATE XML INDEX idx_DeletedDataPath on audit (DeletedData) USING XML INDEX idx_DeletedData FOR PATH

Do you expect to get more than one row or only ever get one row? Why do you use FOR XML PATH instead of the table variable in the first place?

we have generic data audit triggers that look like this:

insert audit select tablename, (select * for xml path from inserted), (select * from deleted for xml path).... etc.

the select described above is used to get the audit trail of changes to a row.

we have a large development effort going on, and using genric triggers seemed like a perfect way to audit data in an enviroment where number of tables and table schema changes on a daily basis without having to change triggers and audit tables... Once we stabilize the schema we might move to a more sophisticated strategy.. I'd prefer not to since I really like this solution, but if getting an audit tral of 100 rows takes 20-30 seconds, i might have to...

thanks!|||Thanks for testing it. Did you try the WHERE clause rewrite with the PATH index together?

If so, and you have a reasonable amount of data, can you please contact me in email (mrys at the usual microsoft com domain).

Thanks
Michael|||How selective is the variable @.X? If it is highly selective, then you may want to consider also creating a VALUE index on the XML Index. This will allow QO to select a plan in which we seek for the value and then match the path.|||

Take a look at the optimization described under "Merging multiple value() method executions for indexed XML" in the XML optimizations whitepaper at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqloptxml.asp.

The optimization can apply to your case:

1) When it is written as nodes()/value() combination
2) You use attributes instead of subelements (of <row>). If this is an option, please rerun the experiments and let us know the performance you observe.

Thank you,

Shankar
Program Manager
Microsoft SQL Server

|||PugV

Let me know if you still have the performance issue. There's a way to get close to what you want with a better performance. The best is to write to Eugene dot Kogan at Microsoft dot com and I'll reply to the forum.

Best regards,
Eugene Kogan
Technical Lead,
Microsoft SQL Server
|||sorry guys, was away for a while, I will do some more benchmarking next week and get back to you.
thanks a lot for everyone's help!

FOR XML AUTO - Performance under SQL 2000 SP3

I'm currently having a performance problem with a straight SQL select statement with the 'for xml auto' clause returning a result set involving approximately 5,000 records. This is taking about 16 seconds on a SQL 2000 installation SP3 (8.00.818), while it only takes a few milliseconds when removing the 'for xml auto' (when executed via Query Analyzer). Time under the 'for xml auto' is directly proportional to the number of records processed.

Testing the same query/table on another server with SP4 installed shows little difference in times.

I'll be trying to set up another test installation with SP3 on a different server to see if this behavior is something specific to SP3, but has anyone else encountered this problem?

Not anything specific to SP3...just tested it on a 8.00.818 box that I created and the query with/without the 'for xml auto' clause processed the results instantaneously. Will check to see what might be different about server configuration settings and collations.|||Seems to be associated with amount of data being output in the XML stream. The original output consists of 261 KB data and takes 16 seconds duration to execute. Renaming attributes in table to single characters to reduce tag name overhead reduces data size by 69% and duration to 70%. Execution was via Query Analyzer local to the database server. Execution on same table/data on another server was 115 ms. Query plans generated are the same. Output of data as a normal SQL result set (without 'for xml clause') on the problem installation takes time down to milliseconds also.|||

It is hard to give you any response to this without an exact repro (data and query at least). Did you compare the query plans?

The XML will be bigger than TDS rowset format but should not have such a big overhead. Are you sure you are not including the time it takes the QA to show the data?

Also, if the overhead does not show in SP4, could you just upgrade to SP4?

Best regards

Michael

FOR XML and performance

Does anyone have any suggestions or pointers for performance optimizations
for SELECT queries that take relational data and
return it as XML columns?
For example, I have a Person table. In our system, a person may have
multiple names, addresses, phone numbers, and email
addresses. So, I have a PersonNames table, PersonAddresses table,
PersonPhones table and a PersonEmails table. I'm
returning all of the person information at once, instead of having to make
multiple database calls.
One way to do this is to return multiple resultsets: the first is the person
base information, and each of the associated
collections of informataion would be in the next result sets.
There are some problems with this, though: if I ever want to bring back
multiple people with all of their supporting
information, I have to return them with multiple result sets in the same
order:
Person1
Person1Names
Person1Addresses
.
.
.
Person2
Person2Names
Person2Addresses
.
.
.
etc.
I've dealt with this by defining a view that selects the various information
as XML columns, like this:
SELECT personId,
dateOfBirth,
gender,
(SELECT DISTINCT nameId,
salutation,
firstName,
middleName,
surName,
suffix,
nameTypeId
FROM Names AS [Name]
WHERE personId = People.personId
FOR XML AUTO, TYPE, Root('Names')) AS Names,
(SELECT Address.addressId,
PeopleAddresses.addressTypeId AS addressTypeId,
cityId,
countyId,
stateId,
streetAddr1,
streetAddr2,
cityName,
stateAbbrev,
zipCode,
stateName,
countyName,
areaCode,
timeZone,
useDST
FROM PeopleAddresses INNER JOIN
VAddresses AS [Address] ON PeopleAddresses.addressId =
Address.addressId
WHERE PeopleAddresses.personId = People.personId AND
PeopleAddresses.addressId = Address.AddressId
FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
(SELECT emailAddressId,
emailAddress,
emailAddressTypeId
FROM EmailAddresses AS [EmailAddress]
WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
Root('EmailAddresses')) AS EmailAddresses,
(SELECT PhoneNumbers.phoneTypeId,
PhoneNumber.phoneNumberId,
PhoneNumber.areaCode,
PhoneNumber.phoneNumber,
PhoneNumber.extension,
PhoneNumber.prefix
FROM PeoplePhones PhoneNumbers INNER JOIN
PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
PhoneNumber.phoneNumberId
WHERE PhoneNumbers.personId = People.personId
FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
FROM dbo.People
This works really well - I can reach into the XML columns of the result set,
and pull back the data. I can also have a
SINGLE piece of code that knows how to read addresses, phone numbers, etc.
for other entities that have these. I just return
them in the queries for these entities as XML columns.
However, the performance is not optimal. I know that XML introduces a
performance penalty, and I'm willing to pay one for
this flexibility - but I was wondering if anyone has any performance
optimization tips for this scenario?
I could obviously change how I'm doing things: store the names, addresses,
etc. as XML, so that I only construct it once,
have special cases for returning this information as multiple result sets,
etc. I don't expect this to be the
best-performing method, but I'd like to eliminate as many performance
bottlenecks as possible.
Any comments are appreciated. Thanks!Hello PMarino,
If you need to read all that data then thats your option. However you are
readd all people in this example normally an app would only select 1 person.
Also you need to make sure the query is optimal so the subqueries are using
good plans
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Does anyone have any suggestions or pointers for performance
> optimizations for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to
> make multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring
> back multiple people with all of their supporting
> information, I have to return them with multiple result sets in the
> same order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
> I've dealt with this by defining a view that selects the various
> information as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO,
> TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS
> PhoneNumbers
> FROM dbo.People
> This works really well - I can reach into the XML columns of the
> result set, and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers,
> etc. for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names,
> addresses, etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result
> sets, etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>|||Did you try to analyze why your query is expensive? Can it be because of the
SELECT DISTINCT?
You can find some details of FOR XML implementation that could potentially
help optimize FOR XML query performance at
http://blogs.msdn.com/sqlprogrammab...ges/576095.aspx .
Best regards,
Eugene
"PMarino" <PMarino@.discussions.microsoft.com> wrote in message
news:B9127CC7-2413-4047-9E50-C779032E1586@.microsoft.com...
> Does anyone have any suggestions or pointers for performance optimizations
> for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to make
> multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person
> base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring back
> multiple people with all of their supporting
> information, I have to return them with multiple result sets in the same
> order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
>
> I've dealt with this by defining a view that selects the various
> information
> as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
> FROM dbo.People
>
> This works really well - I can reach into the XML columns of the result
> set,
> and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers, etc.
> for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names, addresses,
> etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result sets,
> etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>

FOR XML and performance

Does anyone have any suggestions or pointers for performance optimizations
for SELECT queries that take relational data and
return it as XML columns?
For example, I have a Person table. In our system, a person may have
multiple names, addresses, phone numbers, and email
addresses. So, I have a PersonNames table, PersonAddresses table,
PersonPhones table and a PersonEmails table. I'm
returning all of the person information at once, instead of having to make
multiple database calls.
One way to do this is to return multiple resultsets: the first is the person
base information, and each of the associated
collections of informataion would be in the next result sets.
There are some problems with this, though: if I ever want to bring back
multiple people with all of their supporting
information, I have to return them with multiple result sets in the same
order:
Person1
Person1Names
Person1Addresses
..
..
..
Person2
Person2Names
Person2Addresses
..
..
..
etc.
I've dealt with this by defining a view that selects the various information
as XML columns, like this:
SELECT personId,
dateOfBirth,
gender,
(SELECT DISTINCT nameId,
salutation,
firstName,
middleName,
surName,
suffix,
nameTypeId
FROM Names AS [Name]
WHERE personId = People.personId
FOR XML AUTO, TYPE, Root('Names')) AS Names,
(SELECT Address.addressId,
PeopleAddresses.addressTypeId AS addressTypeId,
cityId,
countyId,
stateId,
streetAddr1,
streetAddr2,
cityName,
stateAbbrev,
zipCode,
stateName,
countyName,
areaCode,
timeZone,
useDST
FROM PeopleAddresses INNER JOIN
VAddresses AS [Address] ON PeopleAddresses.addressId =
Address.addressId
WHERE PeopleAddresses.personId = People.personId AND
PeopleAddresses.addressId = Address.AddressId
FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
(SELECT emailAddressId,
emailAddress,
emailAddressTypeId
FROM EmailAddresses AS [EmailAddress]
WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
Root('EmailAddresses')) AS EmailAddresses,
(SELECT PhoneNumbers.phoneTypeId,
PhoneNumber.phoneNumberId,
PhoneNumber.areaCode,
PhoneNumber.phoneNumber,
PhoneNumber.extension,
PhoneNumber.prefix
FROM PeoplePhones PhoneNumbers INNER JOIN
PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
PhoneNumber.phoneNumberId
WHERE PhoneNumbers.personId = People.personId
FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
FROM dbo.People
This works really well - I can reach into the XML columns of the result set,
and pull back the data. I can also have a
SINGLE piece of code that knows how to read addresses, phone numbers, etc.
for other entities that have these. I just return
them in the queries for these entities as XML columns.
However, the performance is not optimal. I know that XML introduces a
performance penalty, and I'm willing to pay one for
this flexibility - but I was wondering if anyone has any performance
optimization tips for this scenario?
I could obviously change how I'm doing things: store the names, addresses,
etc. as XML, so that I only construct it once,
have special cases for returning this information as multiple result sets,
etc. I don't expect this to be the
best-performing method, but I'd like to eliminate as many performance
bottlenecks as possible.
Any comments are appreciated. Thanks!
Hello PMarino,
If you need to read all that data then thats your option. However you are
readd all people in this example normally an app would only select 1 person.
Also you need to make sure the query is optimal so the subqueries are using
good plans
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Does anyone have any suggestions or pointers for performance
> optimizations for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to
> make multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring
> back multiple people with all of their supporting
> information, I have to return them with multiple result sets in the
> same order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
> I've dealt with this by defining a view that selects the various
> information as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO,
> TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS
> PhoneNumbers
> FROM dbo.People
> This works really well - I can reach into the XML columns of the
> result set, and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers,
> etc. for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names,
> addresses, etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result
> sets, etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>
|||Did you try to analyze why your query is expensive? Can it be because of the
SELECT DISTINCT?
You can find some details of FOR XML implementation that could potentially
help optimize FOR XML query performance at
http://blogs.msdn.com/sqlprogrammability/pages/576095.aspx .
Best regards,
Eugene
"PMarino" <PMarino@.discussions.microsoft.com> wrote in message
news:B9127CC7-2413-4047-9E50-C779032E1586@.microsoft.com...
> Does anyone have any suggestions or pointers for performance optimizations
> for SELECT queries that take relational data and
> return it as XML columns?
> For example, I have a Person table. In our system, a person may have
> multiple names, addresses, phone numbers, and email
> addresses. So, I have a PersonNames table, PersonAddresses table,
> PersonPhones table and a PersonEmails table. I'm
> returning all of the person information at once, instead of having to make
> multiple database calls.
> One way to do this is to return multiple resultsets: the first is the
> person
> base information, and each of the associated
> collections of informataion would be in the next result sets.
> There are some problems with this, though: if I ever want to bring back
> multiple people with all of their supporting
> information, I have to return them with multiple result sets in the same
> order:
> Person1
> Person1Names
> Person1Addresses
> .
> .
> .
> Person2
> Person2Names
> Person2Addresses
> .
> .
> .
> etc.
>
> I've dealt with this by defining a view that selects the various
> information
> as XML columns, like this:
> SELECT personId,
> dateOfBirth,
> gender,
> (SELECT DISTINCT nameId,
> salutation,
> firstName,
> middleName,
> surName,
> suffix,
> nameTypeId
> FROM Names AS [Name]
> WHERE personId = People.personId
> FOR XML AUTO, TYPE, Root('Names')) AS Names,
> (SELECT Address.addressId,
> PeopleAddresses.addressTypeId AS addressTypeId,
> cityId,
> countyId,
> stateId,
> streetAddr1,
> streetAddr2,
> cityName,
> stateAbbrev,
> zipCode,
> stateName,
> countyName,
> areaCode,
> timeZone,
> useDST
> FROM PeopleAddresses INNER JOIN
> VAddresses AS [Address] ON PeopleAddresses.addressId =
> Address.addressId
> WHERE PeopleAddresses.personId = People.personId AND
> PeopleAddresses.addressId = Address.AddressId
> FOR XML RAW('Address'), TYPE, Root('Addresses')) AS Addresses,
> (SELECT emailAddressId,
> emailAddress,
> emailAddressTypeId
> FROM EmailAddresses AS [EmailAddress]
> WHERE EmailAddress.personId = People.personId FOR XML AUTO, TYPE,
> Root('EmailAddresses')) AS EmailAddresses,
> (SELECT PhoneNumbers.phoneTypeId,
> PhoneNumber.phoneNumberId,
> PhoneNumber.areaCode,
> PhoneNumber.phoneNumber,
> PhoneNumber.extension,
> PhoneNumber.prefix
> FROM PeoplePhones PhoneNumbers INNER JOIN
> PhoneNumbers PhoneNumber ON PhoneNumbers.phoneNumberId =
> PhoneNumber.phoneNumberId
> WHERE PhoneNumbers.personId = People.personId
> FOR XML RAW('PhoneNumber'), TYPE, Root('PhoneNumbers')) AS PhoneNumbers
> FROM dbo.People
>
> This works really well - I can reach into the XML columns of the result
> set,
> and pull back the data. I can also have a
> SINGLE piece of code that knows how to read addresses, phone numbers, etc.
> for other entities that have these. I just return
> them in the queries for these entities as XML columns.
> However, the performance is not optimal. I know that XML introduces a
> performance penalty, and I'm willing to pay one for
> this flexibility - but I was wondering if anyone has any performance
> optimization tips for this scenario?
> I could obviously change how I'm doing things: store the names, addresses,
> etc. as XML, so that I only construct it once,
> have special cases for returning this information as multiple result sets,
> etc. I don't expect this to be the
> best-performing method, but I'd like to eliminate as many performance
> bottlenecks as possible.
> Any comments are appreciated. Thanks!
>

For each container - performance problem?

I have a for each container configured to iterate over files in a directory (around 2600 files). The for each container contains only one data flow task. The data flow task is very simple. Only 3 steps. Read from file using flat file source (ragged right), format column values (eg. converting YYYYMMDD into yyyy/mm/dd and TRIM spaces - 13 such conversions) using derived column transform and inserting them into a table (23 columns - mostly nvarchar, few numerics and few dates) using Ole Db Destination. The table has one non-unique clustered index on nvarchar(18) column. Each of the 2600 file has at an average 750000 rows.

It seems to me that memory deallocation doesn't happen after each iteration of the loop (I might be wrong). So after few iterations, sqlservr.exe is hogging all available memory (2GB in this case) whereas my dual processors are running at 3-6 % of capacity. As a result, to process less than 5 million rows, it is taking 6 hours.

I am surprised that for such a trivial operation, performance should degrade this much.

One thing that is suspicious is that the files are residing on a USB 2 external drive.
Also, the database files are on the same drive. But, still... why first few iterations zap through like missiles while the later ones seem to be competing with snails?

Any insight?

TIA,
NiteshDo you use transactions? If yes, I suspect the cause of the problem that all files are submitted as part of single transaction - so the Sql Server has to keep all the data before committing the transactions.

I would rather avoid transactions unless really needed and use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.|||Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie|||

Michael Entin SSIS wrote:

Do you use transactions? ...

No, I am not using Transactions.

Michael Entin SSIS wrote:

...use SQL Destination for bulk insert operation which performs better than generic OleDb Destination.

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh|||

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh|||

Nitesh Ambastha wrote:

Jamie Thomson wrote:

Try loading to a raw file. You can append to the raw file each time around the loop. Then, simply insert the whole of the raw file contents at the same time.

-Jamie

Interesting idea Jamie.
However, this will create a huge file (say with 1.5 billion rows). I am not sure when I try to load that, how it will impact memory etc.

thanks,
Nitesh

There's only one way to find out :)

I'll be brave and say it should be able to handle it. Remember, all it does is swap buffers in and out of memory - once the contents of a buffer is inserted into the target the buffer "disappears".

You may have to play with buffer sizes etc... to get optimal performance but that sounds like an interesting piece of work if you ask me (perhaps that's just me! )

Let us know how it goes anyway. You should definately try it.

-Jamie|||

Nitesh Ambastha wrote:

If I remember correctly, in CTP15/16 SQL Destination was not an option for remote operations. I am not sure what holds true for nov RTM. I will give it a shot.

thanks,
Nitesh

Correct. SQL Server Destination can only be used when the target is local.

-Jamie

for deleting duplicate rows subquery or cueser is Better in performance?

For deleting duplicate rows, i can use cursor and subquery.

cursor code

Declare dup_cursor cursor for
select acctnumber from LinkUrnABSADMBAR
group by acctnumber
having count(*) > 1

Open dup_cursor

Fetch Next from dup_cursor INTO @.acctnumber

While (@.@.Fetch_Status = 0)
Begin

Delete from LinkUrnABSADMBAR
where acctnumber = @.acctnumber

Fetch Next from dup_cursor INTO @.acctnumber
End

Close dup_cursor
Deallocate dup_cursor

Subquery code

delete from galupload2000..test where id in (select id from galupload2000..test group by id having count(*) >1)

My question is which one is Better in performance?

Thanks

Sandipan

Subqueries run faster than cursors as a general rule.

BobP

|||Thanks Bob