Improve search speed

[gate7cy]

I have an application in VS (vb) with a database in Access 2007. In the application I have some search functions. They were running very slow until recently when I played around with access andthe indexes. I found that made great improvement in speed. But as the application is running the speed of the searc function diminishes. Any reasons why?? Any help how to improve speed of the search function and make my database provide faster results. Thanks for all your help.

[Negative0]

When you say it slows down, does it slow down as the user is using the program? If so, does restarting the program help with the speed?

If that is the case, you probably have some memory leak somewhere that is slowing your whole app down. You should check to be sure that you are cleaning up all of the objects that you create, especially any COM objects.

If that is not the case and it just gets slower with more data, other indexes may help. Depending on the size of your database, you could also be running into Access limitations and may want to consider using SQL Express (or full SQL Server) instead.

[gate7cy]

It gets slower as the user is using it. How do I check and solve this issue with clearing up objects. Sorry for the stupid questions but I am only a newbie in VB and I am finding this issues. Thanks for the reply

[Negative0]

A lot of it can depend on the objects you are using. If you are using COM based components, you need to explicitly free the memory using Marshal.ReleaseCOMObject. If you are using .Net objects, many have a dispose method that can be called to clean up the objects.

Also, do you see the memory usage of your App continue to increase as you use it, with no signs of decreasing?

Posting some of your data access code may also help in pinning down the issue.

[gate7cy]

The memory of the application increases slightly but still is very much ( 100mb! ). No signs of decreasing. This is the code I use to call my parameter query to search through the data:

Private Sub ToolStripTextBox1_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripTextBox1.TextChanged
        Dim search1 As String = Me.ToolStripTextBox1.Text
 
        Try
            Me.CustomerTableAdapter.search(Me.Database2DataSet.Customer, search1, search1, search1)
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try
 
    End Sub

Thanks for the replies

ps. again repeating that I am a newbie. How do I find out what kind of objects I am using; COM or .Net? Where in code are they used and where in code do I use the clear function to release them. Thank you for your time. Cheers

[Jenner]

Honestly, a large part of it is the fact that it's an Access database. Access is somewhat of a semi-managed hybrid of a database system. It has no "server" except the application trying to talk to it, thus, a lot of the background tasks most commonly found in server-based databased systems aren't present. It's not caching commonly used data to memory for speedy retrieval, and it doesn't optimize and defragment tables unless you explicitly tell it to.

If you're the only one using the database and don't foresee a multi-user use, then you may want to open your database in exclusive mode. This gives a huge speed increase since it no longer has to worry about record locking. Proper record locking in Access also makes big speed differences. Using the DataReader provides read-only forward-only data access that Access is much faster with.

[dminder]

If you want the user interface to be more responsive, then you can also implement backgroundworkers to do your searches. This will keep your UI more responsive as the search works its magic.

Another tip is to try to limit the amount of records to return and return ONLY what you need, ie: if you have 30 fields in a table you do Select *, it will take alot longer and increase your memory usage tremendously than if you do Select Top 10 Field1, Field2, Field 10 Where (x = x*2).

Already covered open exclusive...here is what the connection string would look like if you use that option:

Dim MyConnStr as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDataSourceLocation;username=myname;password=mypassword;mode=12;"

That is just what I could come up with quickly off the top of my head.

Hope it helps!

D