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.

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