本文介绍了使用vb.net在MS Access中选择数据非常慢.我做对了吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我目前正在开发一个程序，该程序使用组织非常奇怪的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中选择数据非常慢.我做对了吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！