c# - 高效的一对多 SQL 查询

Question

我正在编写一个应用程序，该应用程序使用数据库来存储应用程序及其所依赖的系统。我正在使用 Microsoft Access 数据库来存储我的信息。我的应用程序是用 C# 中的 VS 2013 编写的，使用 OleDbCommand 和 OleDbDataReader 与数据库进行交互。

当我将一个应用程序存储在我的数据库中时，它可以拥有它所依赖的任意数量的系统。为了增加它的“乐趣”，每个系统也可以有任意数量的屏幕，每个屏幕可以或不能被使用该系统的应用程序使用，每个屏幕可以有任意数量的字段。我使用一对多表跟踪这两种情况，如下面的模拟模式所示:

AppTable
| ID | Name | 

SystemTable
| ID | Name |

AppToSystemTable
| AppID | SystemID |

ScreenTable
| ID | NAME | SystemID | 

AppToScreenTable
| AppID | ScreenID |

FieldTable
| ID | Name | ScreenID |

AppToFieldTable
| AppID | FieldID |

现在，我可以成功读取应用程序的所有数据，但效率很低。首先，我查询 AppTable 以获取只有一个值的属性，然后将它们存储到我的 App 对象中。然后我查询我的一对多表以获取我需要的 ID 列表。然后，我查询 SystemTable 以构建应用程序的系统列表。我查询 ScreenTable 和 FieldTable 并构建屏幕和字段的字典以跟踪哪个系统/屏幕是其父级。总而言之，我使用了 7 个查询来获取我的系统列表!这很糟糕，我知道。

我想问的是如何重组我的查询以更有效地执行此操作？我很想只讨论一个问题，但我不知道这是否可能。我查看了 JOINS，我可以看到它们帮助立即获取系统、屏幕和字段，而无需获取 id 的中间步骤，但我无法弄清楚如何更有效地查询每个应用程序 X 系统，Y屏幕和 Z 字段。任何人都可以建议有效查询一对多关系的方法吗？

编辑

这里是我当前的查询。我知道它们不是以典型的 C# 方式构建的，但那是因为这是我在 C# 中的第一个项目，而且我一直在学习。

*注意

实际的 App 对象比示例模式中显示的更复杂。这被简化以隔离我感到困惑的问题的一对多部分。

另请注意，对 GetLanguage(langId)、GetDeveloper(oridDevId) 或其他属性的调用都会调用一个简单的方法来从数据库中读取该属性的内容.其中许多是自定义对象。我不担心如何清理这些。我是从我已经得到的答案和其他来源中弄清楚的。我只是还没有重写它们。这正在发生。

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;
    }

我相信您可以看到这里有很大的优化空间。我首先只是让它进入工作阶段；现在我正处于 get is working well 阶段。

Answer 1

我可能误解了你的问题，因为这看起来相对简单 - 为什么不创建一个查询作为包含所有数据的“平面文件”？

尝试这样的事情:

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;

我相信我在这里正确地连接了您的所有 ID(并且我为名称字段分配了别名，因此您实际上可以分辨出它们属于哪个表)。