使用vb.net在MS Access中选择数据非常慢.我做对了吗? [英] Selecting data in MS Access with vb.net is very slow. Am I doing it right?

问题描述

我目前正在开发一个程序，该程序使用组织非常奇怪的MS Access数据库中的数据.

I'm currently working on a program for work that uses data in a very weirdly organized MS Access database.

我必须从一个表跳到另一个表才能获得所需的信息.

I have to jump from tables to tables to get the information I need.

现在，我选择的数据少于200 x 100，但是执行所有查询似乎要花费很多时间.大约需要150秒.

Now I'm selecting less than 200 x 100 data but it seems like it takes a lot of time to perform all the querys. It takes about 150 seconds.

我没有数据库的实际背景，除了我用php和mySql编码的几个网站以外，所以我想知道你们是否可以快速浏览一下我的代码并告诉我查询的结构和位置是否正确还是让它变得如此缓慢的原因.

I have no real background in databases exept for the couple websites I coded in php and mySql so I was wondering if you guys could take a quick look at my code and tell me if the structure and the emplacements of the querys is correct or if it's what makes it so slow.

Dim connectionStr = Constants.Input.MDB.CONNECTION_STRING & _
        "Data Source=" & dbFullPath & ";"

    Dim connection As New System.Data.OleDb.OleDbConnection(connectionStr)

    connection.Open()

    Dim query = "SELECT " & Constants.Input.MDB.Columns.CYCLE_ID & ", " & _
                            Constants.Input.MDB.Columns.COMMAND_ID & ", " & _
                            Constants.Input.MDB.Columns.QUANTITY & ", " & _
                            Constants.Input.MDB.Columns.FINAL_TEMP & ", " & _
                            Constants.Input.MDB.Columns.DATE_TIME & ", " & _
                            Constants.Input.MDB.Columns.DRY_MALAX_TIME & ", " & _
                            Constants.Input.MDB.Columns.TOTAL_MALAX_TIME & _
                " FROM " & Constants.Input.MDB.Tables.CYCLE & _
                " WHERE " & Constants.Input.MDB.Columns.DATE_TIME & _
                " BETWEEN #" & startDate.ToString("yyyy/MM/dd") & "# AND #" & endDate.ToString("yyyy/MM/dd") & "#"

    Dim dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

    ' Table readers
    Dim reader_cycle = dbCommand.ExecuteReader

    Dim reader_command As System.Data.OleDb.OleDbDataReader

    Dim reader_strCache As System.Data.OleDb.OleDbDataReader

    Dim reader_recipe As System.Data.OleDb.OleDbDataReader

    Dim reader_coldFeedsRecipeDetails As System.Data.OleDb.OleDbDataReader

    Dim reader_materials As System.Data.OleDb.OleDbDataReader

    Dim reader_cycleDetails As System.Data.OleDb.OleDbDataReader

    Dim reader_location As System.Data.OleDb.OleDbDataReader

    ' Cycle table
    While (reader_cycle.Read)

        Dim row(nbCols) As String

        row(Constants.Input.MDB.CYCLE_ID_COLUMN_INDEX) = reader_cycle.Item(0)
        row(Constants.Input.MDB.COMMAND_ID_COLUMN_INDEX) = reader_cycle.Item(1)
        row(Constants.Input.MDB.TOTAL_QTY_COLUMN_INDEX) = reader_cycle.Item(2)
        row(Constants.Input.MDB.FINAL_TEMP_COLUMN_INDEX) = reader_cycle.Item(3)
        Dim date_time = CStr(reader_cycle.Item(4)).Split(" "c)
        row(Constants.Input.MDB.DATE_COLUMN_INDEX) = date_time(0)
        row(Constants.Input.MDB.TIME_COLUMN_INDEX) = date_time(1)
        row(Constants.Input.MDB.TOTAL_MALAX_TIME_COLUMN_INDEX) = reader_cycle.Item(5)
        row(Constants.Input.MDB.DRY_MALAX_TIME_COLUMN_INDEX) = reader_cycle.Item(6)

        query = "SELECT " & Constants.Input.MDB.Columns.FORMULA_NAME_ID & ", " & _
                            Constants.Input.MDB.Columns.TRUCK_ID & _
                " FROM " & Constants.Input.MDB.Tables.COMMAND & _
                " WHERE " & Constants.Input.MDB.Columns.COMMAND_ID & " " & _
                " = " & row(Constants.Input.MDB.COMMAND_ID_COLUMN_INDEX)

        dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

        reader_command = dbCommand.ExecuteReader

        Dim recipeNameID As String

        ' Command table
        While (reader_command.Read)

            recipeNameID = reader_command.Item(0)
            row(Constants.Input.MDB.TRUCK_ID_COLUMN_INDEX) = reader_command.Item(1)

        End While ' End Command Table

        reader_command.Close()

        query = "SELECT " & Constants.Input.MDB.Columns.STRING_CONTENT & _
                            " FROM " & Constants.Input.MDB.Tables.STRING_CACHE & _
                            " WHERE " & Constants.Input.MDB.Columns.STRING_ID & _
                            " = " & recipeNameID

        dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

        reader_strCache = dbCommand.ExecuteReader

        ' String Cache (formula name)
        While (reader_strCache.Read)

            row(Constants.Input.MDB.RECIPE_NAME_COLUMN_INDEX) = reader_strCache.Item(0)

        End While ' End String Cache

        reader_strCache.Close()

        query = "SELECT " & Constants.Input.MDB.Columns.RECIPE_DESC & ", " & _
                            Constants.Input.MDB.Columns.QUANTITY & ", " & _
                            Constants.Input.MDB.Columns.COLD_FEEDS_RECIPE_ID & _
                " FROM " & Constants.Input.MDB.Tables.RECIPES & _
                " WHERE " & Constants.Input.MDB.Columns.RECIPE_NAME & _
                " = " & "'" & row(Constants.Input.MDB.RECIPE_NAME_COLUMN_INDEX) & "'"

        dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

        reader_recipe = dbCommand.ExecuteReader

        Dim coldFeedRecipeID As Object

        ' Recipe
        While (reader_recipe.Read)

            row(Constants.Input.MDB.RECIPE_DESC_COLUMN_INDEX) = reader_recipe.Item(0)
            row(Constants.Input.MDB.RECIPE_QTY_COLUMN_INDEX) = reader_recipe.Item(1)
            coldFeedRecipeID = reader_recipe.Item(2)

        End While ' End Recipe

        reader_recipe.Close()

        If (Not IsDBNull(coldFeedRecipeID)) Then


            query = "SELECT " & Constants.Input.MDB.Columns.MATERIAL_ID & ", " & _
                                Constants.Input.MDB.Columns.MATERIAL_PERCENTAGE & _
                    " FROM " & Constants.Input.MDB.Tables.COLD_FEEDS_RECIPES_DETAILS & _
                    " WHERE " & Constants.Input.MDB.Columns.CFRD_TABLE_RECIPE_ID & _
                    " = " & coldFeedRecipeID

            dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

            reader_coldFeedsRecipeDetails = dbCommand.ExecuteReader


            ' Cold Feeds Recipe Details
            While (reader_coldFeedsRecipeDetails.Read)

                query = "SELECT " & Constants.Input.MDB.Columns.LOCATION_NAME & _
                    " FROM " & Constants.Input.MDB.Tables.LOCATION & _
                    " WHERE " & Constants.Input.MDB.Columns.MATERIAL_ID & _
                    " = " & reader_coldFeedsRecipeDetails.Item(0)

                dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

                reader_location = dbCommand.ExecuteReader

                Dim locationName As String

                ' Location
                If (reader_location.Read) Then

                    locationName = reader_location.Item(0)
                Else
                    locationName = Constants.Input.MDB.DEFAULT_LOCATION

                End If ' End Location

                Dim locationIndex = Constants.Input.MDB.getColumnIndex(locationName)

                row(locationIndex + Constants.Input.MDB.LOCATION_ROW_INDEX) = locationName

                reader_location.Close()

                query = "SELECT " & Constants.Input.MDB.Columns.MATERIAL_NAME & _
                        " FROM " & Constants.Input.MDB.Tables.MATERIALS & _
                        " WHERE " & Constants.Input.MDB.Columns.MATERIAL_ID & _
                        " = " & reader_coldFeedsRecipeDetails.Item(0)

                dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

                reader_materials = dbCommand.ExecuteReader

                ' Materials
                While (reader_materials.Read)

                    row(locationIndex + Constants.Input.MDB.MATERIAL_NAME_ROW_INDEX) = reader_materials.Item(0)
                    row(locationIndex + Constants.Input.MDB.PERCENTAGE_ROW_INDEX) = reader_coldFeedsRecipeDetails.Item(1)

                End While ' End materials

                reader_materials.Close()

            End While ' End Cold Feeds ...

            reader_coldFeedsRecipeDetails.Close()

        End If

        query = "SELECT " & Constants.Input.MDB.Columns.MATERIAL_NAME_ID & ", " & _
                            Constants.Input.MDB.Columns.FORMULA_QUANTITY & ", " & _
                            Constants.Input.MDB.Columns.DOSAGE_QUANTITY & ", " & _
                            Constants.Input.MDB.Columns.REAL_QUANTITY & ", " & _
                            Constants.Input.MDB.Columns.LOCATION & ", " & _
                            Constants.Input.MDB.Columns.MANUEL_MODE & _
                " FROM " & Constants.Input.MDB.Tables.CYCLE_DETAILS & _
                " WHERE " & Constants.Input.MDB.Columns.CYCLE_ID & _
                " = " & row(Constants.Input.MDB.CYCLE_ID_COLUMN_INDEX)

        dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

        reader_cycleDetails = dbCommand.ExecuteReader

        ' Cycle details
        While (reader_cycleDetails.Read)

            Dim materialNameID = reader_cycleDetails.Item(0)

            Dim location = reader_cycleDetails.Item(4)

            query = "SELECT " & Constants.Input.MDB.Columns.LOCATION_NAME & _
                    " FROM " & Constants.Input.MDB.Tables.LOCATION & _
                    " WHERE " & Constants.Input.MDB.Columns.LOCATION_ID & _
                    " = " & location

            dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

            reader_location = dbCommand.ExecuteReader

            Dim locationName As String

            ' Location
            While (reader_location.Read)

                locationName = reader_location.Item(0)

            End While ' End Location

            Dim locationIndex = Constants.Input.MDB.getColumnIndex(locationName)

            row(locationIndex + Constants.Input.MDB.LOCATION_ROW_INDEX) = locationName

            reader_location.Close()

            query = "SELECT " & Constants.Input.MDB.Columns.STRING_CONTENT & _
                    " FROM " & Constants.Input.MDB.Tables.STRING_CACHE & _
                    " WHERE " & Constants.Input.MDB.Columns.STRING_ID & _
                    " = " & materialNameID

            dbCommand = New System.Data.OleDb.OleDbCommand(query, connection)

            reader_strCache = dbCommand.ExecuteReader

            ' String Cache (formula name)
            While (reader_strCache.Read)

                row(locationIndex + Constants.Input.MDB.MATERIAL_NAME_ROW_INDEX) = reader_strCache.Item(0)

            End While ' End String Cache

            reader_strCache.Close()

            row(locationIndex + Constants.Input.MDB.RECIPE_QTY_ROW_INDEX) = reader_cycleDetails.Item(1)
            row(locationIndex + Constants.Input.MDB.DOSAGE_QTY_ROW_INDEX) = reader_cycleDetails.Item(2)
            row(locationIndex + Constants.Input.MDB.REAL_QTY_ROW_INDEX) = reader_cycleDetails.Item(3)
            row(locationIndex + Constants.Input.MDB.MANUAL_ROW_INDEX) = reader_cycleDetails.Item(5)

        End While ' End cycle details

        reader_cycleDetails.Close()

        rowList.AddLast(row)

    End While ' End Cycle

    reader_cycle.Close()

    connection.Close()

    Return rowList

推荐答案

您正在对数据库进行许多不同的选择.每次打开和关闭连接时，除了实际查询的开销外，还会有大量的IO开销.

You are doing many many different selects against the database. Each time you open and close a connection there is a large IO cost in addition to the cost of the actual query.

正如@RobertHarvey所提到的，您需要学习使用JOIN.

As @RobertHarvey mentioned, you need to learn to use JOINs.

这篇关于使用vb.net在MS Access中选择数据非常慢.我做对了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！