Efficient one-to-many SQL Query

Question

I'm working on writing an application that uses a database to store applications and the systems on which they rely. I am using a Microsoft Access database to store my information. My application is written in VS 2013 in C# using OleDbCommand and OleDbDataReader to interact with the database.

When I store an application in my database, it can have any number of systems it relies on. To add to the 'fun' of this, each system can also have an arbitrary number of screens, and each screen can or cannot be used by an application using that system, and each screen can have any number of fields. I track both of these situations using one-to-many tables as shown in the mock schema below:

AppTable
| ID | Name | 

SystemTable
| ID | Name |

AppToSystemTable
| AppID | SystemID |

ScreenTable
| ID | NAME | SystemID | 

AppToScreenTable
| AppID | ScreenID |

FieldTable
| ID | Name | ScreenID |

AppToFieldTable
| AppID | FieldID |

Right now, I can successfully read all my data for an application, but it is very inefficient. First I query the AppTable to get the attributes that only have one value and then store them into my App object. Then I query my one-to-many tables to get the lists of ids I need. I then query the SystemTable to build the list of systems for the application. I query the ScreenTable and FieldTable and build dictionaries of the screens and fields to keep track of what system/screen is its parent. All in all I'm using 7 queries just to get my list of systems! THIS IS BAD and I know that.

What I'm asking is how can I restructure my queries to do this more efficiently? I would love to get down to just one query, but I don't know if that is possible. I've looked into JOINS and I could see them helping with getting the systems, screens, and fields right away without the intermediate step of grabbing the ids, but I can't figure out how to more efficiently query each apps X systems, Y screens, and Z fields. Can anyone suggest methods to efficiently query one-to-many relationships?

Edit

As requested here are my current queries. I know they are not built in typical C# fashion, but that is because this is my first project in C# and I've been learning as I go.

*Note

The actual App object is more complicated than displayed in the sample schema. This was simplified to isolate the one-to-many part of my question which I was confused about.

Also note that the calls to GetLanguage(langId), GetDeveloper(oridDevId) or for other attributes all call a simple method to read what that attribute is from the database. Many of them are custom objects. I'm not worried about how to clean these up. I'm getting that figured out from the answer I've already got and other sources. I just haven't rewritten them yet. That is happening right now.

internal static App GetApp(int id)
    {
        App app = new App("");
        int langId = -1, origDevId = -1, mainDevId = -1, updateId = -1;
        List<SystemObj> systemList = new List<SystemObj>();

        bool wasOpen = true;
        try
        {
            // Get app core info.
            StringBuilder appSql = new StringBuilder();
            appSql.Append("SELECT " + DbContract.Apps.NAME + "," + DbContract.Apps.ORIGINAL_DEV_ID + 
                "," + DbContract.Apps.LANGUAGE_ID + "," + DbContract.Apps.MAINTENANCE_DEV_ID + "," + 
                DbContract.Apps.DEACTIVATION_DATE + "," + DbContract.Apps.DEPLOYMENT_METHOD + "," + 
                DbContract.Apps.UPDATE_ID + " FROM " + DbContract.Apps.TABLE + " WHERE " + 
                DbContract.Apps.ID + "=" + id);

            if (readConn.State == System.Data.ConnectionState.Closed)
            {
                wasOpen = false;
                open(readConn);
            }
            OleDbCommand appCommand = new OleDbCommand(appSql.ToString(), readConn);
            OleDbDataReader reader = appCommand.ExecuteReader();
            reader.Read();

            int appOrd = reader.GetOrdinal(DbContract.Apps.NAME);
            int langOrd = reader.GetOrdinal(DbContract.Apps.LANGUAGE_ID);
            int origDevOrd = reader.GetOrdinal(DbContract.Apps.ORIGINAL_DEV_ID);
            int mainDevOrd = reader.GetOrdinal(DbContract.Apps.MAINTENANCE_DEV_ID);
            int updateOrd = reader.GetOrdinal(DbContract.Apps.UPDATE_ID);
            int deployOrd = reader.GetOrdinal(DbContract.Apps.DEPLOYMENT_METHOD);
            int deactiveOrd = reader.GetOrdinal(DbContract.Apps.DEACTIVATION_DATE);

            app = new App(reader.GetString(appOrd), id);
            langId = reader.GetInt32(langOrd);
            origDevId = reader.GetInt32(origDevOrd);
            mainDevId = reader.GetInt32(mainDevOrd);
            updateId = reader.GetInt32(updateOrd);
            app.DeploymentMethod = reader.GetValue(deployOrd).ToString();
            app.DeactivationDate = reader.GetValue(deactiveOrd).ToString();
            reader.Close();
            if (!wasOpen) { close(readConn); }

            // Get app Language.
            app.Language = GetLanguage(langId);
            // Get app Developers.
            app.OriginalDeveloper = GetDeveloper(origDevId);
            app.MaintainingDeveloper = GetDeveloper(mainDevId);
            // Get app Update.
            app.LastUpdate = GetUpdate(updateId);

            // Get ids for app systems, screens & fields.
            List<int> sysIds = new List<int>();
            List<int> screenIds = new List<int>();
            List<int> fieldIds = new List<int>();

            if (!wasOpen) { open(readConn); }
            string appToSysSql = "SELECT " + DbContract.Apps.Systems.SYSTEM_ID + " FROM " +
                DbContract.Apps.Systems.TABLE + " WHERE " + DbContract.Apps.Systems.APP_ID +
                "=" + id;
            OleDbCommand appToSysCommand = new OleDbCommand(appToSysSql, readConn);
            reader = appToSysCommand.ExecuteReader();
            while (reader.Read())
            {
                sysIds.Add(reader.GetInt32(0));
            }
            reader.Close();

            string appToScreenSql = "SELECT " + DbContract.Apps.Screens.SCREEN_ID + " FROM " +
                DbContract.Apps.Screens.TABLE + " WHERE " + DbContract.Apps.Screens.APP_ID +
                "=" + id;
            OleDbCommand appToScreenCommand = new OleDbCommand(appToScreenSql, readConn);
            reader = appToScreenCommand.ExecuteReader();
            while (reader.Read())
            {
                screenIds.Add(reader.GetInt32(0));
            }
            reader.Close();

            string appToFieldSql = "SELECT " + DbContract.Apps.Fields.FIELD_ID + " FROM " +
                DbContract.Apps.Fields.TABLE + " WHERE " + DbContract.Apps.Fields.APP_ID +
                "=" + id;
            OleDbCommand appToFieldCommand = new OleDbCommand(appToFieldSql, readConn);
            reader = appToFieldCommand.ExecuteReader();
            while (reader.Read())
            {
                fieldIds.Add(reader.GetInt32(0));
            }
            reader.Close();
            if (!wasOpen) { close(readConn); }

            List<SystemObj> sysList = GetSystems(sysIds);

            Dictionary<Screen, int> scrnDict = GetScreens(screenIds);
            Dictionary<Field, int> fldDict = GetFields(fieldIds);

            foreach (SystemObj sys in sysList)
            {
                if (scrnDict.Values.Contains(sys.Id))
                {
                    foreach (Screen scrn in scrnDict.Keys)
                    {
                        if (scrnDict[scrn] == sys.Id)
                        {
                            sys.AddScreen(scrn);
                            if (fldDict.Values.Contains(scrn.Id))
                            {
                                foreach (Field fld in fldDict.Keys)
                                {
                                    if (fldDict[fld] == scrn.Id)
                                    {
                                        scrn.AddField(fld);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            app.SystemList = sysList;
        } catch (Exception e)
        {
            if (!wasOpen) { close(readConn); }
            LogWriter.Log(e, DateTime.Now);
        }
        return app;
    }

As I'm sure you can see there is much room for optimization here. I was first just getting it working stage; now I'm in the get is working well stage.

Answer 1

I may be misunderstanding your question, because this seems relatively straightforward -- why not just create a single query that acts as a "flat file" with all your data?

Try something like this:

SELECT AppTable.Name AS AppName, SystemTable.Name AS SystemName, ScreenTable.NAME AS ScreenName, FieldTable.Name AS FieldName
FROM (((((AppTable INNER JOIN AppToSystemTable ON AppTable.ID = AppToSystemTable.AppID) INNER JOIN SystemTable ON AppToSystemTable.SystemID = SystemTable.ID) INNER JOIN AppToScreenTable ON AppTable.ID = AppToScreenTable.AppID) INNER JOIN ScreenTable ON (ScreenTable.SystemID = SystemTable.ID) AND (AppToScreenTable.ScreenID = ScreenTable.ID)) INNER JOIN ApptoFieldTable ON AppTable.ID = ApptoFieldTable.AppID) INNER JOIN FieldTable ON ApptoFieldTable.FieldID = FieldTable.ID;

I believe I hooked up all your IDs correctly here (and I assigned aliases to the Name fields, so you can actually tell which table they belong to).