Poor performance when looping through table columns

Hi

Is there a performant way to loop through all columns on a table accessing lots of properties against the table and each column?

If I do this on a single table with 100 columns it takes forever! If I look at Profiler this seems to generate 1000s of queries. I have tried using SetDefaultInitFields (as indicated in Michiel Worries FAQs) but while this reduces the number of queries it still generates 100s of queries.

Is there an equivalent to SetDefaultInitFields for collections? What am I missing? I can do this via my own query in 1 round trip but if I keep having to do that it would make SMO pretty useless!

Thanks

Russell Mason

See these articles:

http://blogs.msdn.com/mwories/archive/2005/04/22/smoperf1.aspx

http://blogs.msdn.com/mwories/archive/2005/05/02/smoperf2.aspx

What you are seeing is likely caused by delayed instantiation.

|||I have the same question as Russell and I don't believe it's answered by the links posted, as SetDefaultInitFields only seems to work for 'scalar' properties (like IsSystemObject) but not properties that are collections (like StoredProcedure.Parameters).
So what about collection caching? How does one prevent SMO round-tripping to the database for each object in an SMO collection? An example of this would be the Parameters collection of a StoredProcedure - is there a way we can get all the parameters for a stored procedure in one go rather than roundtripping to the database for each parameter? Ditto for extended properties!

Thanks in advance for any help!!
sql