Foreach NodeList Enumerator

Does anyone have any experience of using the NodeList enumerator in a Foreach loop? BOL is a bit light on this.

I want to enumerate over an XML Document that is passed into my package. The package is executed from a .net application.
Has anyone done anything like this?
Any demo material?
Should I pass the XML Document into an SSIS object variable or a String variable?
Can the NodeList enumerator enumerate an XML document that is stored in a String variable?
etc...
Thanks
JamieHey Jamie,
I've done this -- albeit for a rather simple example. I have an XML file that is just a persisted collection of structs with properties called "BusinessObjectName", so the data itself looks like this:
<DimensionInfos xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BusinessDimensionEntries>
<anyType xsi:type="BusinessDimensionInfo">
<BusinessObjectName>User</BusinessObjectName>
</anyType>
</BusinessDimensionEntries>
</DimensionInfos>

Essentially I want to iterate over all the nodes and pull out the "BusinessObjectName" from each. I created a foreach NodeList enumerator, document source is the file, enumeration type is NodeText, XPath source is DirectInput (meaning I specify it within the task) and the XPath string is "//BusinessObjectName". This query will recursively match all nodes of that type. Then I map index 0 to some variable and I will get the NodeText "User" (as specified) of the node(s) that match my XPath expression.
Not sure if you came across this article:
http://databasejournal.com/features/mssql/article.php/3528791
Also, a good XPath reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/html/_xpath_reference.asp

|||BTW - DocumentSourceType seems to be what you need, no? It allows you to draw the XML in from a variable or a string (which I suppose in theory you could bind to an expression =))
|||I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.|||

SimonSa wrote:

I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.

Simon,

I'm trying to do something almost identical to this but just can't get it working at the moment. I'll post up here if i get something useful working.

-Jamie|||I sent feedback about the help not being very helpful.

The help for the properties of the for each loop doesn't explain what each one is it just says 'set the value'. What else would I do with it. It needs to say what the value should be set to. Unlike other feedback. I didn't get a response on this one|||

SimonSa wrote:

I sent feedback about the help not being very helpful.

Me too. Doug Laudenshlager is good at taking feedback on board so expect something more useful in the future.

-Jamie|||

SimonSa wrote:

I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.

Hi all,
OK, i'm progressing with this but have now hit EXACTLY the same problem as Simon. Here's my XML document:
<LastLoadDateList>
<Pair>
<StreamDetailID>3</StreamDetailID>
<LastLoadDate>2005-09-19 13:40:00</LastLoadDate>
</Pair>
<Pair>
<StreamDetailID>4</StreamDetailID>
<LastLoadDate>2005-09-19 13:42:15</LastLoadDate>
</Pair>
</LastLoadDateList>

I've managed to enumerate the 2 <Pair> nodes which results in the strings "32005-09-19 13:40:00" & "42005-09-19 13:42:15" getting enumerated. I've got EnumerationType=Nodetext.
Can you see what's happened here? Its concatenated the StreamDetailID & LastLoadDate nodes.

So how can I get those 2 values out into seperate variables? I guess its something to do with InnerXPathString but I can't see how to do it.

The lack of documentation around this is infuriating. To say the least!!!

Any help much appreciated!

-Jamie
|||<root>
<mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

If your goal is to enumerate the attributes of the preceeding document, you could do so with an EnumerationType of NodeText and an OuterXPathString of /root/mynode/@.*|||<LastLoadDateList>
<Pair>
<StreamDetailID>3</StreamDetailID>
<LastLoadDate>2005-09-19 13:40:00</LastLoadDate>
</Pair>
<Pair>
<StreamDetailID>4</StreamDetailID>
<LastLoadDate>2005-09-19 13:42:15</LastLoadDate>
</Pair>
</LastLoadDateList>

If your goal is to enumerate the children of the Pair nodes in the preceeding document, you could do so with an EnumerationType of NodeText and an OuterXPathString of /LastLoadDateList/Pair/*|||Can the NodeList Enumerator be used to slice out a segment of an XML file and store it in a variable? Here's an example of what I would like to do. Given the following xml file:

<book>
<section>
<name>Chapter1</name>
<content>...</content>
</section>
<section>
<name>Chapter2</name>
<content>...</content>
</section>
</book>

I would like to use the NodeList Enumerator to loop twice over the XML and pull out the section into a variable, such that the variable would contain <section><name>Chapter1</name><content>...</content></section> in the first iteration and <section><name>Chapter2</name><content>...</content></section> in the second. Whatever I do, the enumerator either wants to map the different elements to different variables or store everything as text. Any help on this matter is appreciated.

Regards,
Lars R?nnb?ck|||If you set the index to -1 and the variable type to object and the outerXpath to \\section you should end up with the section in your object|||Should the enumerator type then be "Navigator" or "Node", and how would you transform the object variable back to string, so I could use it in an XSLT task?

Thanks for the tip about the -1 index, that seems to put everything into a single variable though.

Regards,
Lars|||Probably Node

and probably need to use a script component to convert the object to text.|||

Since others might be struggling with figuring out the InnerXPathString I thought I would post my experiences. I have the following recursive schema in the database for my typed XML datatype:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns="http://garbleddomain/schemas/meta/jobb"
targetNamespace="http://garbleddomain/schemas/meta/jobb"
elementFormDefault="qualified">
<xs:element name="job">
<xs:annotation>
<xs:documentation>
<xhtml:p>
Defines a job.
</xhtml:p>
</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element ref="job" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="name" type="xs:string" />
<xs:attribute name="script" type="xs:string" />
<xs:attribute name="type" default="Group">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Group" />
<xs:enumeration value="SP" />
<xs:enumeration value="SSIS" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>

The schema is used to define jobs I want to run that can be grouped and hierarchical. Here's an example XML document:

<?xml version="1.0"?>
<job xmlns="http://garbleddomain/schemas/meta/jobb">
<job name="Loads">
<job name="Load customer data" script="sp_loadCust" type="SP" />
<job name="Load articles" script="sp_loadArticles" type="SP" />
</job>
<job name="Updates">
<job name="Update transactions" script="sp_updateTrans" type="SP" />
<job name="Update categories" script="UpdateCategories" type="SSIS" />
</job>
</job>

To retrieve the XML using an Execute SQL Task over OLE DB I set the ResultSet type to XML and used the following Direct Input query:

SELECT CAST(definition AS VARCHAR(max)) AS JobDefinition
FROM META_Job_TB
WHERE (JobID = ?)

In the Parameter Mapping section I map one String variable, User::JobID as Input with type VARCHAR and Parameter Name 0. In the Result Set section I map another variable User::JobDefinition with Result Name 0. This will pull the XML document above wrapped in <ROOT> tags.

Since I want to remove the <ROOT> tags and since I couldn't get SSIS to work with typed XML I have a cleanup step using an XML Task (XSLT) where I remove the tags and namespace. The XSLT is a Direct Input which looks as follows:

<?xml version="1.0" ?>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:id="http://garbleddomain/schemas/meta/jobb"
exclude-result-prefixes="id">
<xsl:template match="/">
<xsl:apply-templates mode="copy-no-ns" select="/ROOT/id:job"/>
</xsl:template>
<xsl:template mode="copy-no-ns" match="*">
<xsl:element name="{name(.)}">
<xsl:copy-of select="@.*"/>
<xsl:apply-templates mode="copy-no-ns"/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>

I use the User::JobDefinition variable both as input source and output operation result destination. Now I am left with the job definition XML document without the namespace. In this case I want to iterate over the actual jobs and store the attributes in variables that can be accessible inside the loop, and do the following:

1. Create three String variables; User::JobName, User::JobScript, and User::JobType.
2. In the ForEach Loop I select the NodeList Enumerator.
3. I use the User::JobDefinition as document source.
4. Set EnumerationType to ElementCollection.
5. Set the OuterXPathString to //job[not(@.type = 'Group')]
6. Set the InnerElementType to NodeText.
7. Set the InnerXPathString to @.*
8. In Variable Mappings add the three String variables with Index 0, 1, and 2.

Voila, the variables will now be set to the values of the attributes for each job.

Hope this helps someone,
Regards,
Lars R?nnb?ck

Foreach NodeList Enumerator

Does anyone have any experience of using the NodeList enumerator in a Foreach loop? BOL is a bit light on this.

I want to enumerate over an XML Document that is passed into my package. The package is executed from a .net application.
Has anyone done anything like this?
Any demo material?
Should I pass the XML Document into an SSIS object variable or a String variable?
Can the NodeList enumerator enumerate an XML document that is stored in a String variable?
etc...
Thanks
JamieHey Jamie,
I've done this -- albeit for a rather simple example. I have an XML file that is just a persisted collection of structs with properties called "BusinessObjectName", so the data itself looks like this:
<DimensionInfos xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<BusinessDimensionEntries>
<anyType xsi:type="BusinessDimensionInfo">
<BusinessObjectName>User</BusinessObjectName>
</anyType>
</BusinessDimensionEntries>
</DimensionInfos>

Essentially I want to iterate over all the nodes and pull out the "BusinessObjectName" from each. I created a foreach NodeList enumerator, document source is the file, enumeration type is NodeText, XPath source is DirectInput (meaning I specify it within the task) and the XPath string is "//BusinessObjectName". This query will recursively match all nodes of that type. Then I map index 0 to some variable and I will get the NodeText "User" (as specified) of the node(s) that match my XPath expression.
Not sure if you came across this article:
http://databasejournal.com/features/mssql/article.php/3528791
Also, a good XPath reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/html/_xpath_reference.asp

|||BTW - DocumentSourceType seems to be what you need, no? It allows you to draw the XML in from a variable or a string (which I suppose in theory you could bind to an expression =))
|||I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.|||

SimonSa wrote:

I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.

Simon,

I'm trying to do something almost identical to this but just can't get it working at the moment. I'll post up here if i get something useful working.

-Jamie|||I sent feedback about the help not being very helpful.

The help for the properties of the for each loop doesn't explain what each one is it just says 'set the value'. What else would I do with it. It needs to say what the value should be set to. Unlike other feedback. I didn't get a response on this one|||

SimonSa wrote:

I sent feedback about the help not being very helpful.

Me too. Doug Laudenshlager is good at taking feedback on board so expect something more useful in the future.

-Jamie|||

SimonSa wrote:

I have to say the use of indexes is a bit of a black art.

What if I want to get the value of multiple elements of a node.

i.e. <root><mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

Can I iterate through mynode elements and store the value1 and value2 attributes.

Hi all,
OK, i'm progressing with this but have now hit EXACTLY the same problem as Simon. Here's my XML document:
<LastLoadDateList>
<Pair>
<StreamDetailID>3</StreamDetailID>
<LastLoadDate>2005-09-19 13:40:00</LastLoadDate>
</Pair>
<Pair>
<StreamDetailID>4</StreamDetailID>
<LastLoadDate>2005-09-19 13:42:15</LastLoadDate>
</Pair>
</LastLoadDateList>

I've managed to enumerate the 2 <Pair> nodes which results in the strings "32005-09-19 13:40:00" & "42005-09-19 13:42:15" getting enumerated. I've got EnumerationType=Nodetext.
Can you see what's happened here? Its concatenated the StreamDetailID & LastLoadDate nodes.

So how can I get those 2 values out into seperate variables? I guess its something to do with InnerXPathString but I can't see how to do it.

The lack of documentation around this is infuriating. To say the least!!!

Any help much appreciated!

-Jamie
|||<root>
<mynode value1="simon" value2="fred" />
<mynode value1="jamie" value2="smith" />
</root>

If your goal is to enumerate the attributes of the preceeding document, you could do so with an EnumerationType of NodeText and an OuterXPathString of /root/mynode/@.*|||<LastLoadDateList>
<Pair>
<StreamDetailID>3</StreamDetailID>
<LastLoadDate>2005-09-19 13:40:00</LastLoadDate>
</Pair>
<Pair>
<StreamDetailID>4</StreamDetailID>
<LastLoadDate>2005-09-19 13:42:15</LastLoadDate>
</Pair>
</LastLoadDateList>

If your goal is to enumerate the children of the Pair nodes in the preceeding document, you could do so with an EnumerationType of NodeText and an OuterXPathString of /LastLoadDateList/Pair/*|||Can the NodeList Enumerator be used to slice out a segment of an XML file and store it in a variable? Here's an example of what I would like to do. Given the following xml file:

<book>
<section>
<name>Chapter1</name>
<content>...</content>
</section>
<section>
<name>Chapter2</name>
<content>...</content>
</section>
</book>

I would like to use the NodeList Enumerator to loop twice over the XML and pull out the section into a variable, such that the variable would contain <section><name>Chapter1</name><content>...</content></section> in the first iteration and <section><name>Chapter2</name><content>...</content></section> in the second. Whatever I do, the enumerator either wants to map the different elements to different variables or store everything as text. Any help on this matter is appreciated.

Regards,
Lars R?nnb?ck|||If you set the index to -1 and the variable type to object and the outerXpath to \\section you should end up with the section in your object|||Should the enumerator type then be "Navigator" or "Node", and how would you transform the object variable back to string, so I could use it in an XSLT task?

Thanks for the tip about the -1 index, that seems to put everything into a single variable though.

Regards,
Lars|||Probably Node

and probably need to use a script component to convert the object to text.|||

Since others might be struggling with figuring out the InnerXPathString I thought I would post my experiences. I have the following recursive schema in the database for my typed XML datatype:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns="http://garbleddomain/schemas/meta/jobb"
targetNamespace="http://garbleddomain/schemas/meta/jobb"
elementFormDefault="qualified">
<xs:element name="job">
<xs:annotation>
<xs:documentation>
<xhtml:p>
Defines a job.
</xhtml:p>
</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element ref="job" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="name" type="xs:string" />
<xs:attribute name="script" type="xs:string" />
<xs:attribute name="type" default="Group">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="Group" />
<xs:enumeration value="SP" />
<xs:enumeration value="SSIS" />
</xs:restriction>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
</xs:schema>

The schema is used to define jobs I want to run that can be grouped and hierarchical. Here's an example XML document:

<?xml version="1.0"?>
<job xmlns="http://garbleddomain/schemas/meta/jobb">
<job name="Loads">
<job name="Load customer data" script="sp_loadCust" type="SP" />
<job name="Load articles" script="sp_loadArticles" type="SP" />
</job>
<job name="Updates">
<job name="Update transactions" script="sp_updateTrans" type="SP" />
<job name="Update categories" script="UpdateCategories" type="SSIS" />
</job>
</job>

To retrieve the XML using an Execute SQL Task over OLE DB I set the ResultSet type to XML and used the following Direct Input query:

SELECT CAST(definition AS VARCHAR(max)) AS JobDefinition
FROM META_Job_TB
WHERE (JobID = ?)

In the Parameter Mapping section I map one String variable, User::JobID as Input with type VARCHAR and Parameter Name 0. In the Result Set section I map another variable User::JobDefinition with Result Name 0. This will pull the XML document above wrapped in <ROOT> tags.

Since I want to remove the <ROOT> tags and since I couldn't get SSIS to work with typed XML I have a cleanup step using an XML Task (XSLT) where I remove the tags and namespace. The XSLT is a Direct Input which looks as follows:

<?xml version="1.0" ?>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:id="http://garbleddomain/schemas/meta/jobb"
exclude-result-prefixes="id">
<xsl:template match="/">
<xsl:apply-templates mode="copy-no-ns" select="/ROOT/id:job"/>
</xsl:template>
<xsl:template mode="copy-no-ns" match="*">
<xsl:element name="{name(.)}">
<xsl:copy-of select="@.*"/>
<xsl:apply-templates mode="copy-no-ns"/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>

I use the User::JobDefinition variable both as input source and output operation result destination. Now I am left with the job definition XML document without the namespace. In this case I want to iterate over the actual jobs and store the attributes in variables that can be accessible inside the loop, and do the following:

1. Create three String variables; User::JobName, User::JobScript, and User::JobType.
2. In the ForEach Loop I select the NodeList Enumerator.
3. I use the User::JobDefinition as document source.
4. Set EnumerationType to ElementCollection.
5. Set the OuterXPathString to //job[not(@.type = 'Group')]
6. Set the InnerElementType to NodeText.
7. Set the InnerXPathString to @.*
8. In Variable Mappings add the three String variables with Index 0, 1, and 2.

Voila, the variables will now be set to the values of the attributes for each job.

Hope this helps someone,
Regards,
Lars R?nnb?ck

Fore Color Expressions

Hi all, I can't seem to find an example anywhere on the net or BOL on this one. I assume you can set the color of a calculated members dynamically in the Fore Color Expression property but I can't find any code samples for that. Can someone provide me a simply one for changing the color to red if a condition is met?

I really appreciate it!

Brian

Hi Brian,

Here's a recent query for the AS 2000 Foodmart Sales cube - you should see some cells highlighted in red when this is run in the MDX Sample App:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/3a1eccc9d291c961

>>

With Member [Measures].[testmetric] AS
'[Measures].[Store Sales Net]-[Measures].[Store Sales]',

FORE_COLOR ='iif([Time].currentMember.Level is [Time].[Quarter] And
([Time].CurrentMember.PrevMember,[Measures].[testmetric])
- CalculationPassValue( [Measures].[testmetric], -1,Relative )

> Abs(([Time].CurrentMember.PrevMember,Measures.[testmetric])) * 0.025,

rgb(255,0,0), rgb(0,0,0))',
FORMAT_STRING = 'Currency'

SELECT
{[Measures].[Store Sales], [Measures].[Store Sales Net],
Measures.[testmetric]} ON COLUMNS ,
CrossJoin({[Product].[Product Family].[Drink],
[Product].[Product Family].[Food],
[Product].[Product Family].[Non-Consumable]},
{{[Time].[Year].[1997].CHILDREN}}) ON ROWS

FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

>>

|||

Thanks, so it sounds like I'd want to do something like this at runtime in the OLAP client application and not in the SQL Server 2005 Color Expressions of a Calculated Member?

Brian

|||

You can do this using Calculations tab of Sql Server Busyness Intelligence Studio too. When you press the button on the right side of a color expression textbox (there are 2 of those – for foreground and background color) there will be a color chooser dialog box to pick the desired color. When you have selected the color its code will be inserted in the text box. You can subsequently pick another color and it will add it to the text box again. Thus the contents of the text box will have 2 color codes with comments.

Now you can modify the contents of the textbox to have an expression using the codes you obtained. Something like:

iif([measures].currentmember > 0, 4227327 /*R=255, G=128, B=64*/, 8388608 /*Navy*/)

If you like editing MDX scripts in the plain text format then you might find convenient usage of the debugger as a helper to write the script. When you are on the calculations tab you can hit F5 and the tab will load the debugger. In the text box for the script you can type your MDX statements and click toolbar buttons to select colors and fonts. The codes will be inserted at the cursor of the script text box. You can also drag the templates of MDX statements from the tree views on the left side of the debugger.

Having constructed your statement, you can execute it (F10) and immediately test with the pivot table or MDX query you like (there are 4 tabs for custom queries). Pressing F10 subsequently will step over till the end and restart from the beginning of the script. Breakpoints also help.

Once you are done you can select Debug | Stop menu command and you will be offered to keep your script or discard. Thus you can use the debugger to try something and throw away (MDX prototyping).

|||Thanks Andrew, that worked like a champ.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

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

ForceExecutionResult doesn't do what its supposed to.

[Microsoft follow-up]

All,

According to BOL the ForceExecutionResult property can be used to imitate real-time failure (http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtsforcedexecresult.aspx).

However, I've just done a very simple test and this appears to not be the case. Here's the repro:

Start a new package Drag on a Sequence Container Set the sequence container property ForceExecutionResult='Failure'

When you run the package the sequence container will complete successfully. As far as I can see its not supposed to.

Any comments?

Thanks

Jamie

I've just dragged a script task onto the same package. Opened the script and closed it again to make sure it compiles. Set ForceExecutionResult='Failure'.

When I run the package the script task fails. This is the behaviour I would expect so why is the Sequence Container different?

We have seen problems in the past with unexpected Sequence Container behaviour: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1875222&SiteID=1&mode=1

This is a bug as far as I can tell.

-Jamie

|||

Hmm... Interesting...

Trying it with a data flow task or a script tasks, it shows a failure, but with the sequence container it succeeds.

Looks like a bug in the sequence container?

BobP

|||

BobP - BIM wrote:

Hmm... Interesting...

Trying it with a data flow task or a script tasks, it shows a failure, but with the sequence container it succeeds.

Looks like a bug in the sequence container?

BobP

Definitely. I'm waiting on a reply from Microsoft befroe I submit it to Connect.

-Jamie

|||

Setting ForcedExecutionResult=Failure doesn't produce container failures on a sequence container... or a for loop, or a foreach loop, or the package itself, or an event handler. Seems like a bug.

|||I just went through the codes and seems like we only apply the ForceExecutionResult value for Tasks, not containers. Package, Sequence, Foreach Loop, and For Loop are all in the container category so the property does not get apply to them. It seems like a bug to me also.|||

Cho Yeung - msft wrote:

I just went through the codes and seems like we only apply the ForceExecutionResult value for Tasks, not containers. Package, Sequence, Foreach Loop, and For Loop are all in the container category so the property does not get apply to them. It seems like a bug to me also.

Hi Cho,

TaskHost is in the container category as well so its definitely a bug.

Do you need me to submit it on Connect?

-Jamie

|||I am going to file a bug internally. Thanks for bringing this up|||It is better to have Jamie open the bug so he can stay in the loop.|||

Bob Bojanic - MSFT wrote:

It is better to have Jamie open the bug so he can stay in the loop.

Thanks Bob. Its done.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288294

-Jamie

sql

-ForceConvergenceLevel - Where dose it go?

It is (slightly) documented in BOL - see Replication
Merge Agent Utility. This is a commandline parameter that
can be added to the merge agent's job step. Is this
something that you have been told to do by PSS? I'm
curious about why you are implementing it.
Rgds,
Paul Ibison
[vbcol=seagreen]
Hi Paul.
I'm desperate, due to problems with rows not being replicated. Two
subscribers synchronizes fine without errors, but one gest all the rows from
a table and the other gets most but a few is missing. No filters is applied
on that article.
So I saw a discussion on the subject (can't remember where) and this article
http://support.microsoft.com/default...b;en-us;272210 so I thought
it was worth a try.
"Paul Ibison" wrote:

> It is (slightly) documented in BOL - see Replication
> Merge Agent Utility. This is a commandline parameter that
> can be added to the merge agent's job step. Is this
> something that you have been told to do by PSS? I'm
> curious about why you are implementing it.
> Rgds,
> Paul Ibison
>
>

FOR clause in mssql2k5.

Here is the BOL example for the PIVOT function:
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4,
[233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;
What Im having a hard time understanding though is the FOR clause. Why is it
used instead of the WHERE clause?
TIA, ChrisRIt is not a WHERE clause, and I think they used a different keyword in order
to make that absolutely clear. It only supports FOR <columnname> IN
(<values> ). If you need an actual WHERE clause, you can put one in the
derived table, or before the ORDER BY clause.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:usyswa3eGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Here is the BOL example for the PIVOT function:
> SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS
> Emp4,
> [233] AS Emp5
> FROM
> (SELECT PurchaseOrderID, EmployeeID, VendorID
> FROM Purchasing.PurchaseOrderHeader) p
> PIVOT
> (
> COUNT (PurchaseOrderID)
> FOR EmployeeID IN
> ( [164], [198], [223], [231], [233] )
> ) AS pvt
> ORDER BY VendorID;
> What Im having a hard time understanding though is the FOR clause. Why is
> it
> used instead of the WHERE clause?
> TIA, ChrisR
>|||FOR clause is a part of the PIVOT table
you can say its all part of the FROM clause.
the result of this is actually a table on which you can do a select and a
filter
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
And the above piece of code means this
Do a count of purchase order id grouping by each of the employeeID given in
the inclause and replace the EmployeeID column with these columns
[164], [198], [223], [231], [233] each having the split up count for its
employeeID.
I know I am confusing :)
"ChrisR" wrote:

> Here is the BOL example for the PIVOT function:
> SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp
4,
> [233] AS Emp5
> FROM
> (SELECT PurchaseOrderID, EmployeeID, VendorID
> FROM Purchasing.PurchaseOrderHeader) p
> PIVOT
> (
> COUNT (PurchaseOrderID)
> FOR EmployeeID IN
> ( [164], [198], [223], [231], [233] )
> ) AS pvt
> ORDER BY VendorID;
> What Im having a hard time understanding though is the FOR clause. Why is
it
> used instead of the WHERE clause?
> TIA, ChrisR
>
>|||You can display row data to column using PIVOT and FOR clause .
"ChrisR"?? ??? ??:

> Here is the BOL example for the PIVOT function:
> SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp
4,
> [233] AS Emp5
> FROM
> (SELECT PurchaseOrderID, EmployeeID, VendorID
> FROM Purchasing.PurchaseOrderHeader) p
> PIVOT
> (
> COUNT (PurchaseOrderID)
> FOR EmployeeID IN
> ( [164], [198], [223], [231], [233] )
> ) AS pvt
> ORDER BY VendorID;
> What Im having a hard time understanding though is the FOR clause. Why is
it
> used instead of the WHERE clause?
> TIA, ChrisR
>
>