Searching Through 900000 Records

[mattdaddyjuice]

I am working with an inventory table in a database that contains over 900000 records. I am coding in VB.NET and my select statement can take up to 15-20 seconds to retrieve info based on the barcode that has been scanned. Here is the select statement I am using:

code = txtBarcode.Text

Dim da3 As New OleDb.OleDbDataAdapter("SELECT TOP 1 * FROM Inventory WHERE Barcode = '" & code & "' ", Con)

If the barcode scanned is one of the first few thousand records, it will give me the info in a second or two, but if it is one of the bottom records it slows down by at least 10 seconds. Anyone got any ideas? Thanks!

 

[replyme]

Keep the table indexed by barcode

 

[mattdaddyjuice]

Ha...I am an idiot! It works much much better now. Thanks for your help!

 

[cjard]

also, use a parameterized query:

Setup the query ONCE at application load:
Dim myCmd as OleDbCommand("SELECT * FROM table WHERE barcode = ?")
myCmd.Parameters.AddWithValue("pBarcode", "")

Then every time you want to use it:

myCmd.Parameters("pBarcode") = barcodeTextbox.Text
<then run the query>


If you would follow the advices given in the DW2 link in my signature (start with "creating a simple data app") then Visual Studio will create better data access code for you.

For more info on parameterized queries read the PQ link in my signature.

ps; if barcode was your PK you wouldnt need the "top 1"