ExtendedProperties Performance

Has anyone found a way to speed up the retrival of the "ExtendedProperties" on a column via SMO yet?

I had a search around but couldn't spot anything. I have an applicaiton that currently cyles through 180+ tables (with the number growing all the time) and the "ExtendedProperties" access is abosultly killing it ... taking it from seconds to minutes.

If anyone could provide any information (even if it's a "no it's not possible to speed up") or an alternative retrival method I'd greatly appreciate it.

Have you tried using the SetDefaultInitFields method to include the ExtendedProperties object? Insert this code after you connect to the server:

srv.SetDefaultInitFields(GetType(Table), "ExtendedProperties")

|||

I'm retriving the ExtendedProperties from Columns, not Tables :)

I've tried including the SetDefualtInitFields(typeof(Column), "ExtendedProperties") but it doesn't have much effect.

The people in this thread are having the same problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=571167&SiteID=1

|||

ExtendedProperties is a collection not a property therefore your solution will not work.

Try this:

Database db = ... // get your database root

ScriptingOptions so = new ScriptingOptions();

db.PrefetchObject(typeof(Table), so);

so.ExtendedProperties = true;

db.PrefetchObjects(typeof(Column), so);

Let me know if it works.

Ciprian Gerea, SqlServer SDE

|||

This,

"this.o_Database.PrefetchObjects(typeof(Column), scriptingOptions);"

doesn't work, I think this is because it is a child collection on Table rather than on Database.

But,

ScriptingOptions scriptingOptions = new ScriptingOptions();

scriptingOptions.ExtendedProperties = true;

this.o_Database.PrefetchObjects(typeof(Table), scriptingOptions);

works wonderfully :D It seems it prefetchs the ExtendedProperties on the Columns as well as the Tables!

Thanks for helping me optimize my 5 minute loop into a 10 second one :D