IsSystemObject attribute of smo

Question

I am getting list of stored procedures from database by using SMO. I have foreach loop over stored procedures to make my intended operatioans on them. However I need to use only user created stored procedures. I use IsSystemObject attribute of stored procedures. However it is so much slow:

Approximately 10 sec:

foreach (StoredProcedure mystr in mydb.StoredProcedures)
{
    if (!mystr.IsSystemObject)
    {
        classGenerated += mystr.Name + Environment.NewLine;
    }
}

Less then 1 sec:

foreach (StoredProcedure mystr in mydb.StoredProcedures)
{    
    classGenerated += mystr.Name + Environment.NewLine;
}

Is this difference normal for only one if condition? If not, what is the cause of this performance difference? I can use another way to differentiate the system and non-system objects, if there is another method.

Plebeian

Answer 1

Yep. And here's why. SMO prefetches certain properties by default and leaves the others as just in time. So, you essentially get a call per procedure to find out if it's a system procedure or not. Luckily, there a fix. Check out this BOL article

SMO optimization allows only the minimum properties to be loaded when an object is created. When uninitialized properties are accessed, SMO makes individual requests to load the information from the instance of SQL Server. You can use this method to adjust which properties are initialized for an object when it is first created to further optimize performance

Cryptonym

Yes, it worked. I have changed the VB syntax to C#. For other users here is the better usage: myserver.GetDefaultInitFields(typeof(StoredProcedure)); myserver.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject"); – Plebeian

The link is not showing VB so here is what I used: myserver.SetDefaultInitFields(GetType(Microsoft.SqlServer.Management.Smo.StoredProcedure), "IsSystemObject") – Midvictorian

I'm not a VB guy, but does GetType(Microsoft.SqlServer.Management.Smo.StoredP‌​rocedure) return the StoredProcedure type correctly? – Cryptonym