CODEWITHSUNDEEP
c#wpfdatabasems-accessbackgroundworker
Keven M
Keven M

Reputation: 992

MS Access DB search times/performance

After much searching trying to find the best solution to using an embedded DB in my WPF app, I finally settled on/discovered the ease of using an MS Access DB. I had played around with SQL, but basically kept running into errors and problems that Access ultimately solved. So in order to use the DB once imported, I simply dragged the DataSet onto the WPF window, and VS generated a heap of code that allowed access. It works beautifully.

There is only one problem though, that being that the ZipCode table lookup causes the program to stall for 4-5 seconds, including hanging between keypresses. This takes some of the ease of use away, and I would like to find a way to speed this up.

I thought of using a BackgroundWorker, but I can't seem to figure out a way to pass the commands to it at only the appropriate times. I'm not even sure if this is the best solution, or if there is another way to increase the speed.

In the DB file, the zipcodes functions as the Primary Key, and I have both columns (zipcode & location) indexed, also this didn't seem to improve performance all that much. The two functions below are accessed through the OnTextChanged event of various textboxes.

Any suggestions will be greatly appreciated.

public void LocateZipCode(TextBox source, TextBox destination)
    {
        LocationsDataSet locationsDataSet = ((LocationsDataSet)this.FindResource("locationsDataSet"));

        // Load data into the table ZipCodes. You can modify this code as needed.
        LocationsDataSetTableAdapters.ZipCodesTableAdapter locationsDataSetZipCodesTableAdapter = new LocationsDataSetTableAdapters.ZipCodesTableAdapter();
        locationsDataSetZipCodesTableAdapter.Fill(locationsDataSet.ZipCodes);
        CollectionViewSource zipCodesViewSource = ((CollectionViewSource)(this.FindResource("zipCodesViewSource")));
        zipCodesViewSource.View.MoveCurrentToFirst();

        try
        {
            if (source.Text.Length == 5)
            {
                destination.Text = locationsDataSet.ZipCodes.FindByZipCode(source.Text).Location.ToString();
            }                
        }
        catch (NullReferenceException)
        {

        }
    }

    #region Area Code Lookup
    public void LocateAreaCode(TextBox source, TextBox destination, TextBox destination2 = null)
    {
        LocationsDataSet locationsDataSet = ((LocationsDataSet)(this.FindResource("locationsDataSet")));

        // Load data into the table AreaCodes. You can modify this code as needed.
        LocationsDataSetTableAdapters.AreaCodesTableAdapter locationsDataSetAreaCodesTableAdapter = new LocationsDataSetTableAdapters.AreaCodesTableAdapter();
        locationsDataSetAreaCodesTableAdapter.Fill(locationsDataSet.AreaCodes);
        CollectionViewSource areaCodesViewSource = ((CollectionViewSource)(this.FindResource("areaCodesViewSource")));
        areaCodesViewSource.View.MoveCurrentToFirst();

        try
        {
            if (source.Text.Length >= 3 && destination2 != null)                                        //Info tab area code check
            {
                destination.Text = locationsDataSet.AreaCodes.FindByArea_Code(source.Text).Location.ToString();
                destination2.Text = locationsDataSet.AreaCodes.FindByArea_Code(source.Text).Time_Zone.ToString();
            }
            else if (source.Text.Length >= 3 && destination.Text.Length == 0 && destination2 == null)   //Other area code checks
            {
                destination.Text = locationsDataSet.AreaCodes.FindByArea_Code(source.Text).Location.ToString();
            }
            else if (source.Text.Length < 3 && destination2 != null)                                    //Info tab area code check
            {
                destination.Text = "";
                destination2.Text = "";
            }
            else if (source.Text.Length < 3 && destination.Text.Length == 0 && destination2 == null)    //Other area code checks
            {
                destination.Text = "";
                if (destination2 != null)
                {
                    destination2.Text = "";
                }
            }
        }
        catch (NullReferenceException)
        {
            destination.Text = "Invalid Area Code";
            if (destination2 != null)
            {
                destination2.Text = "";
            }
        }
    }
    #endregion

Upvotes: 1

Views: 395

Answers (1)

Keven M
Keven M

Reputation: 992

In playing around with this I actually found the answer on my own, actually a very simple fix that I should have noticed right away. When I copied the VS generated code, I copied the whole thing into the Locate methods, which were called by the OnTextChanged events, causing a new instance of each DB every time a character was entered in the textbox. Needless to say this was a massive memory drain.

To fix it, I just moved the declarations for the DataSetvariables into the class level of the main window, and created a new InitializeDB() method called from MainWindow().

Leaving this here (and answering it) just in case anybody else makes such a ridiculous error in the future.

Upvotes: 1

Related Questions