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!!

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

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!!

PL-SQL LOOP equivilant in TRANSACT-SQL

Hi,

I need to convert this anonymous pl-sql block into transact-sql to run
in Microsoft SQL Server Query Analyser.

Does anyone know what the conversion syntax would be?

I know that to declare variables to use the @..
PRINT for printing the output.

No idea for the LOOP.

Any suggestions greatly appreciated.

Many thanks.

Thiko!
____________________________________________

SET SERVEROUTPUT ON

DECLARE
recordcount NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(5000000); -- Sets buffer size.
recordcount := 0;

LOOP
INSERT INTO RecoverTest VALUES (SYSDATE - (recordcount / 4));
COMMIT; -- frees up rollback segment;
recordcount := recordcount + 1;
DBMS_OUTPUT.PUT_LINE('INSERT: ' || recordcount || ' - 45000 ');
EXIT WHEN recordcount > 45000;
END LOOP;
END;
/Not certain about the Oracle date arithmetic but take a look at this post
which was a very similar requirement:
http://tinyurl.com/tlpi

--
David Portas
----
Please reply only to the newsgroup
--