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

Poor performance when accessing reports through URL

When viewing reports through the ReportServer application, I typically can
get response times that are between 5 and 10 seconds for my reports (in pdf
format). However, when I attempt to just view the report directly using a URL
(with the appropriate arguments supplied), the reponse time is usually 3 or 4
times worse, and it seems to eat up more resources. Is accessing reports
through a URL somehow doing something different? Anyone else experience this?
Any help would be appreciated.When you use Report Manager you are not getting the report as PDF. You are
getting them as HTML. Then through URL you can get them as PDF or HTML (your
choice). PDF and Excel formats are much much slower than HTML and are much
more resource intensive.
Try your URL as html (if you leave off the rendering format it defaults to
HTML).
You should not be seeing a difference if the format is the same with both. I
suspect that you are rendering in different formats.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"blabore" <blabore@.discussions.microsoft.com> wrote in message
news:EECE8772-5AEA-40F1-AE63-AA9A409A4646@.microsoft.com...
> When viewing reports through the ReportServer application, I typically can
> get response times that are between 5 and 10 seconds for my reports (in
> pdf
> format). However, when I attempt to just view the report directly using a
> URL
> (with the appropriate arguments supplied), the reponse time is usually 3
> or 4
> times worse, and it seems to eat up more resources. Is accessing reports
> through a URL somehow doing something different? Anyone else experience
> this?
> Any help would be appreciated.|||Bruce,
Thanks for the response. I probably wan't clear in my first post. To make
sure I was comparing apples to apples, I used the export functionality in the
report server to get a PDF. The time to complete the export was approximately
6 seconds, while when I attempted to do the same through a URL, it was over
20. To be sure that the time used to load Acrobat Reader doesn't affect
anything, I don't even have it installed, which just forces IE to download
the file. It seems odd, since the web serivce should be used no matter.
What I've found is that when I use slashes in the URL (instead of esacping
them), it significant slows things down. Is this a known issue?
"Bruce L-C [MVP]" wrote:
> When you use Report Manager you are not getting the report as PDF. You are
> getting them as HTML. Then through URL you can get them as PDF or HTML (your
> choice). PDF and Excel formats are much much slower than HTML and are much
> more resource intensive.
> Try your URL as html (if you leave off the rendering format it defaults to
> HTML).
> You should not be seeing a difference if the format is the same with both. I
> suspect that you are rendering in different formats.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "blabore" <blabore@.discussions.microsoft.com> wrote in message
> news:EECE8772-5AEA-40F1-AE63-AA9A409A4646@.microsoft.com...
> > When viewing reports through the ReportServer application, I typically can
> > get response times that are between 5 and 10 seconds for my reports (in
> > pdf
> > format). However, when I attempt to just view the report directly using a
> > URL
> > (with the appropriate arguments supplied), the reponse time is usually 3
> > or 4
> > times worse, and it seems to eat up more resources. Is accessing reports
> > through a URL somehow doing something different? Anyone else experience
> > this?
> >
> > Any help would be appreciated.
>
>|||Also, not sure if you are really comparing apples to apples. When you export
you get the report you are currently looking at. This means it uses the data
that it has already retrieved at the server. So the export would not have
the data retrieval time from whatever database you are hitting. There might
be some other differences as well.
The only other thing I can think of is authorization of the user. Could be
that this is taking some time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"blabore" <blabore@.discussions.microsoft.com> wrote in message
news:238C6980-AD52-4CFF-BD67-84408CEE5263@.microsoft.com...
> Bruce,
> Thanks for the response. I probably wan't clear in my first post. To make
> sure I was comparing apples to apples, I used the export functionality in
> the
> report server to get a PDF. The time to complete the export was
> approximately
> 6 seconds, while when I attempted to do the same through a URL, it was
> over
> 20. To be sure that the time used to load Acrobat Reader doesn't affect
> anything, I don't even have it installed, which just forces IE to download
> the file. It seems odd, since the web serivce should be used no matter.
> What I've found is that when I use slashes in the URL (instead of esacping
> them), it significant slows things down. Is this a known issue?
> "Bruce L-C [MVP]" wrote:
>> When you use Report Manager you are not getting the report as PDF. You
>> are
>> getting them as HTML. Then through URL you can get them as PDF or HTML
>> (your
>> choice). PDF and Excel formats are much much slower than HTML and are
>> much
>> more resource intensive.
>> Try your URL as html (if you leave off the rendering format it defaults
>> to
>> HTML).
>> You should not be seeing a difference if the format is the same with
>> both. I
>> suspect that you are rendering in different formats.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "blabore" <blabore@.discussions.microsoft.com> wrote in message
>> news:EECE8772-5AEA-40F1-AE63-AA9A409A4646@.microsoft.com...
>> > When viewing reports through the ReportServer application, I typically
>> > can
>> > get response times that are between 5 and 10 seconds for my reports (in
>> > pdf
>> > format). However, when I attempt to just view the report directly using
>> > a
>> > URL
>> > (with the appropriate arguments supplied), the reponse time is usually
>> > 3
>> > or 4
>> > times worse, and it seems to eat up more resources. Is accessing
>> > reports
>> > through a URL somehow doing something different? Anyone else experience
>> > this?
>> >
>> > Any help would be appreciated.
>>