[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

How to optimize SMO and looping thru SPs

moondaddy

3/20/2007 8:28:00 AM

I have a method (in c#3.0) which loops through all the SPs in a sql05 db and
adds them to a tree control. my problem is that I only have about 100 SP,
but it takes for ever because its looping through all the system SPs as
well. is there a flag I can use so the database object only returns use
defined SPs? Here's my code:

foreach (StoredProcedure procedure in database.StoredProcedures)
{
if (!(procedure.IsSystemObject))
{
newObject = new TreeNode(procedure.Name);
newGroup.Nodes.Add(newObject);
}
}

Thanks.

--
moondaddy@noemail.noemail


7 Answers

Chris.Cheney

3/20/2007 8:49:00 AM

0

"moondaddy" <moondaddy@noemail.noemail> wrote in
news:#POPNmsaHHA.1508@TK2MSFTNGP06.phx.gbl:

> I have a method (in c#3.0) which loops through all the SPs in a sql05
> db and adds them to a tree control. my problem is that I only have
> about 100 SP, but it takes for ever because its looping through all
> the system SPs as well. is there a flag I can use so the database
> object only returns use defined SPs? Here's my code:
>
> foreach (StoredProcedure procedure in database.StoredProcedures)
> {
> if (!(procedure.IsSystemObject))
> {
> newObject = new TreeNode(procedure.Name);
> newGroup.Nodes.Add(newObject);
> }
> }

The IsSystemObject member of the StoredProcedure object.

moondaddy

3/20/2007 9:47:00 AM

0

That's what I was using, the problem is that the db object returns all the
SPs and I have to loop through each one using the 'IsSystemObject' to filter
them out. this takes a long time. I was hoping there was a flag I could
pass when fetching the db object that it would only contain user defined
objects which would shorten the loop.

"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@tesco.net> wrote in message
news:Xns98F959F09AB95ChrisCheneytesconet@62.253.170.163...
> "moondaddy" <moondaddy@noemail.noemail> wrote in
> news:#POPNmsaHHA.1508@TK2MSFTNGP06.phx.gbl:
>
>> I have a method (in c#3.0) which loops through all the SPs in a sql05
>> db and adds them to a tree control. my problem is that I only have
>> about 100 SP, but it takes for ever because its looping through all
>> the system SPs as well. is there a flag I can use so the database
>> object only returns use defined SPs? Here's my code:
>>
>> foreach (StoredProcedure procedure in database.StoredProcedures)
>> {
>> if (!(procedure.IsSystemObject))
>> {
>> newObject = new TreeNode(procedure.Name);
>> newGroup.Nodes.Add(newObject);
>> }
>> }
>
> The IsSystemObject member of the StoredProcedure object.


changliw

3/20/2007 10:16:00 AM

0

Hi,
I understand that your code took a long time to finish since it loopped
through all the system SPs.
If I have misunderstood, please let me know.

It is indeed not an efficient way to query user defined stored procedures
through Database.StoredProcedures. I recommend that you change your code
like the following:
============================================================================
=
SMO.Server srv = new
Microsoft.SqlServer.Management.Smo.Server(txtServer.Text);
SMO.Database db = srv.Databases[txtDb.Text];
DataSet ds = db.ExecuteWithResults("select * from
sys.procedures");
foreach (DataRow row in ds.Tables[0].Rows)
{
newObject = new TreeNode(row[0].ToString());
newGroup.Nodes.Add(newObject);
}
============================================================================
==

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

bob

3/20/2007 3:30:00 PM

0

Shouldn't the SELECT be:

SELECT *
FROM sys.procedures
WHERE is_ms_shipped = 0

wBob

"Charles Wang[MSFT]" wrote:

> Hi,
> I understand that your code took a long time to finish since it loopped
> through all the system SPs.
> If I have misunderstood, please let me know.
>
> It is indeed not an efficient way to query user defined stored procedures
> through Database.StoredProcedures. I recommend that you change your code
> like the following:
> ============================================================================
> =
> SMO.Server srv = new
> Microsoft.SqlServer.Management.Smo.Server(txtServer.Text);
> SMO.Database db = srv.Databases[txtDb.Text];
> DataSet ds = db.ExecuteWithResults("select * from
> sys.procedures");
> foreach (DataRow row in ds.Tables[0].Rows)
> {
> newObject = new TreeNode(row[0].ToString());
> newGroup.Nodes.Add(newObject);
> }
> ============================================================================
> ==
>
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Have a nice day!
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
> ications
>
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/de....
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>
>
>
>

changliw

3/21/2007 2:40:00 AM

0

Hi, Bob,
Thanks for your response.
Yes, that is more specific; however it is no need here. For the catalog
views such as sys.procedures, sys.tables, sys.views etc, they all return
user defined objects. For queries from sys.objects, the condition
is_ms_shipped=1 should be added. For example:
select * from sys.all_objects where type='P' and is_ms_shipped=0

Thanks.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/de....
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

moondaddy

3/21/2007 3:59:00 AM

0

thanks guys. this is good stuff.

"Charles Wang[MSFT]" <changliw@online.microsoft.com> wrote in message
news:NT6uBJ2aHHA.928@TK2MSFTNGHUB02.phx.gbl...
> Hi, Bob,
> Thanks for your response.
> Yes, that is more specific; however it is no need here. For the catalog
> views such as sys.procedures, sys.tables, sys.views etc, they all return
> user defined objects. For queries from sys.objects, the condition
> is_ms_shipped=1 should be added. For example:
> select * from sys.all_objects where type='P' and is_ms_shipped=0
>
> Thanks.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default....
> ications
>
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply
> promptly.
>
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/de....
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================
>
>
>


Razvan Socol

3/21/2007 7:20:00 AM

0

Hello, moondaddy

If you want to optimize access to a certain property that will be used
when looping through objects using SMO, you can use the
SetDefaultInitFields method to specify that that property should be
initialized first (for all objects), not on access (for each object).

For example:

Dim t As DateTime
t = Now
Dim srv As New Microsoft.SqlServer.Management.Smo.Server(".
\SQLEXPRESS")
Dim sp As New Microsoft.SqlServer.Management.Smo.StoredProcedure
srv.SetDefaultInitFields(sp.GetType, "IsSystemObject")
For Each sp In srv.Databases("AdventureWorks").StoredProcedures
If Not sp.IsSystemObject Then
Debug.Print(sp.Name)
End If
Next
MsgBox(DateDiff(DateInterval.Second, t, Now))

On my system, the above code executes in less than 1 second (compared
to over 2 minutes if I remove the call to SetDefaultInitFields).

In this case, however, it would probably be better to use the method
suggested by Charles Wang, because it would probably work even faster.

Razvan