Performance Confusion

Hi,
Can any of you folks take a minute to tell me why the results of the attached test are weird. I'm trying to come up with a method of allowing my business layer to interact with the data layer - specifically a nice, easy to maintain, but safe method of passing parameters without adversely impacting performance.

This is only a few of my sketchy ideas so far but I'm stuck because I cannot prove that using OR is slower than a straightforward = because each time I run the test the OR seems to perform better. Even changing the sequence in which the piece work still does alter the OR begin better than =.

And, of course, as that makes no sense whatsoever I cannto really progress.

Any thought would be appreciated. I hope I have altered the script enough that you can just run it straight in and see what I'm talking about. I only introduced the transactions to see if any of that stuff my be interfering.

Thanks.Presumably FLOWBOOL and FLOWUSER are UDTs? Anything special about them? Just bit and varchar?

EDIT - oops - FLOWBOOL is char(1) or similar|||Here are the results from my poor pathetic test box (maybe a PIII)

XML Join XML Variables OR Coalesce = Dynamic
---- ---- ---- ---- ---- ----
270 200 10 10 10 30

It pretty much shows what I knew before (openXML is an expensive operation). The main reason queries that use OR tend to be slower is that they have a reduced chance of hitting an index on the table. In this case, the optimizer may be smart enough to check the @.pName variable only once per query, instead of once per row in the resultset. This would strip out all of the extra comparisons. Whether that one comparison operation really matters that much, is a separate question. About all you can really say is to try to avoid using openXML in queries, and only use it to populate variables which are used in the queries.

[EDIT: Nice post, by the way]|||damn, sorry about those. Yes char(1) (don't ask!!!) and varchar(20)|||Wow, MCrowley, the performance differences you got are completely different than mine - i.e. that the xml route was soooo much slower. My real interest is in why the OR and the = methods don't differ significantly.|||Note - If you are using a small table and looping a query multiple times it might not give you accurate performance indications...

If the real table will be larger, indexes will play a more important role than a table small enough to fit in memory. You also need to consider caching of data rows in memory and execution plans...|||They do not differ significantly, because the OR gets its bad reputation when it misses an index. On your table (5 rows), that is not nearly a consideration. Try a test on a table of 10,000 rows or so, but make sure the equals hits the index. Otherwise, you just have two slow queries vying for the worst performance.|||Sorry, been away. Thanks for those responses.

I tried this on a much bigger table and the results were more like what I would have expected (well, some of them were). I'm disappointed, however, because I wanted to create a more clever mechanism of building the search parameters without having such a tight contract between say, C#, code and the database.

For a 36823 record table the results are a significantly different:

XML Join: 10953
XML Variables: 3640
Or: 3470
Coalesce: 5483
=: 16
Dynamic: 33

Surprises here, for me (non database person if you haven't guessed :)) are that dynamic sql takes second position, 'or' outperforms coalesce and that XML variables are quite performant!?

Perform upgrade method

hi

I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .

Thanks

Mani

Explanation about PerformUpgrade and a sample code can be found in book online or at:

http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.performupgrade.aspx

Hope this helps,
Ovidiu Burlacu

|||

hi

thanks i got the solution the perform upgrade method only works if the current version property, that is stored in the package is less than the currentversion property of the component on the local computer.

Regards

Mani

|||

hi

If i removed the old DLL from the GAC i am getting errors while opening the package saved with the old version DLL

these are the errors ...

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package1.dtsx 0 0
Error 2 Error loading Package1.dtsx: The "component "Oracle Destination" (67)" failed to cache the component metadata object and returned error code 0x80131600. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 3 Error loading Package1.dtsx: Component "component "Oracle Destination" (67)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 4 Error loading Package1.dtsx: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. d:\testproject\integration services project5\Package1.dtsx 1 1

how can i correct these errors

thanks

Mani

|||

You should not remo0ve the old DLL from the GAC becuase the component can no longer be instantiated. The new DLL should be able to live in the GAC with the old DLL because they have different versions (file or assembly or even the PublicTokenKey)

Thanks,
Ovidiu

|||

Ovidiu, I don't see how you can do side by side with SSIS components in the GAC. File versions do not differentiate between assemblies, so you cannot use that to installside by side. If you change the assembly version or the key (and therefore the public key token) you can side by side them, but then your are stuffed since SSIS stores the full strong name in the package. Any change in the string name means you get the nice error about not being able to load the assembly. Hacking the XML would seem to be the only solution, shame it is not a supported one :)

|||Indeed you are right. I think is time for me to go in vacation |||

In fact you can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

Here's how:

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen

Perform upgrade method

hi

I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .

Thanks

Mani

Explanation about PerformUpgrade and a sample code can be found in book online or at:

http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.performupgrade.aspx

Hope this helps,
Ovidiu Burlacu

|||

hi

thanks i got the solution the perform upgrade method only works if the current version property, that is stored in the package is less than the currentversion property of the component on the local computer.

Regards

Mani

|||

hi

If i removed the old DLL from the GAC i am getting errors while opening the package saved with the old version DLL

these are the errors ...

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package1.dtsx 0 0
Error 2 Error loading Package1.dtsx: The "component "Oracle Destination" (67)" failed to cache the component metadata object and returned error code 0x80131600. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 3 Error loading Package1.dtsx: Component "component "Oracle Destination" (67)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 4 Error loading Package1.dtsx: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. d:\testproject\integration services project5\Package1.dtsx 1 1

how can i correct these errors

thanks

Mani

|||

You should not remo0ve the old DLL from the GAC becuase the component can no longer be instantiated. The new DLL should be able to live in the GAC with the old DLL because they have different versions (file or assembly or even the PublicTokenKey)

Thanks,
Ovidiu

|||

Ovidiu, I don't see how you can do side by side with SSIS components in the GAC. File versions do not differentiate between assemblies, so you cannot use that to installside by side. If you change the assembly version or the key (and therefore the public key token) you can side by side them, but then your are stuffed since SSIS stores the full strong name in the package. Any change in the string name means you get the nice error about not being able to load the assembly. Hacking the XML would seem to be the only solution, shame it is not a supported one :)

|||Indeed you are right. I think is time for me to go in vacation |||

In fact you can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

Here's how:

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen