CODEWITHSUNDEEP
c#.netsqlsql-serverdatabase
Dot NET
Dot NET

Reputation: 4897

Removing performance overhead

I've narrowed down a performance overhead within my application to this block:

Server currentServer = new Server(databaseConnection.DataSource);
Database currentDatabase = currentServer.Databases[databaseConnection.InitialCatalog];

foreach (View view in currentDatabase.Views)
{
    if (view.IsSystemObject == false)
    {
        if (view.Name.Equals(viewName))
        {
            MessageBox.Show(parent, "A virtual table with that name already exists! Virtual\ntable not created.", "Not created", MessageBoxButton.OK, MessageBoxImage.Information);
            break;
         }
         else
         {
             valid = true;
             break;
         }
    }
}

I would basically like to iterate over database views which do not belong to the system. However, with this approach, the SMO library iterates through all views regardless. Any idea how it can be arranged?

EDIT:

For example I've only got around 10 user-defined views, but over 1000 system ones. How can I skip the system views and iterate through the user-defined ones only?

Upvotes: 0

Views: 110

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062550

It is not clear what API you are using - but if you drop down to regular TSQL / ADO.NET, you can look at things like INFORMATION_SCHEMA.VIEWS / sys.views, which only contain the user-defined views. The system views are in sys.system_views (both are in sys.all_views). This will do all the restriction at the server, so if you query INFORMATION_SCHEMA.VIEWS / sys.views, the only data coming back over the wire is the data you are interested in. If you filter at the client you need to pay to transport all the data you aren't interested in too.

Upvotes: 2

Related Questions