StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelecting data in MS Access with vb.net is very slow. Am I doing it right?
text
Body
copied!<p>I'm currently working on a program for work that uses data in a very weirdly organized MS Access database.</p> <p>I have to jump from tables to tables to get the information I need.</p> <p>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.</p> <p>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.</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload