Creating an Efficient Item Search Tool for a Massive Database

I'll tackle it of course but I need requirements. I need to know at least what you want to search by, and what you want returned.

BTW I don't think posting the TrackRequests database will do anyone any good since it the ItemMaster is a link and I don't think that the linked table will be included in the attachment.

Also do you want the Search to be another Request Type or do you want it attached to and existing Request Type as a button or do you want it as a button on the Help Desk sheet, or???

As the ItemMaster currently is set up, the Item table has the ID (which is an autonumber field as the key and that doesn't help as far as searching for anything useful. If we change the key to the field that we search for I believe that that will speed things up considerably. If you can tell me which field we'll be using for most of the searches I'll change the primary key to that. The key can also be made up of mare that one field like Vendor Name + Vendor Part Number.

To answer your question in Thread ID 39512307:

 Let's include the following attributes with the search return, if possible:


To answer your question in Thread ID 39512311:

To answer your question for Thread ID 39512331:

How will the user benefit from the search as far as the Hrlp Desk is concerned? I ask because right now after entering a vendor name in the NIUF they can see all the part numbers for that vendor, so what does the new search do for them?

The new search offers the user the ability to search for an item and its attributes.  This would allow the user to confirm certain attributes before they submit a request.  For instance, the user can search if an item exists or not before they submit a NIUF request, or they can search the item and determine if it is marked as Berry Compliant 'Y' or not before submitting a request to have it changed.  And so on.  Currently, any item search is done by opening a spreadsheet with all items and performing a Ctrl+F.  Thank you so much for the help!!

Thanks for the explanation. I think I have a better understanding now.

The goal will be to eliminate any joins between the Access database and the Oracle database.

1. Do you have the ability to create stored procs in the Oracle database?
2. Do you have the ability to create and populate any tables in the Oracle database?

Although I have write access to the Oracle database, I prefer to maintain a read only position with it.  So, to answer your question, I do not have the ability to do either 1 or 2.  Sorry.  :(

your problem solution might require some PL/SQL stored procs.  You will need the ability to create these and sometimes these use temporary tables.

Okay, No problem.  I will work with my Oracle DBA to make it happen.  If you don't mind, please lay out the guidelines so I can present that to him.  Thanks again for your help!

aikimark, how would the performance of what you are proposing compare with ADO selecting against a key in an Access database?

@Martin

If the retrieved data requires any table joins, which is likely, then those joins should be done on the Oracle database.  Sometimes, the local (Access) data may be required to select the data.  This may also require some non-trivial amount of data to be transferred a temp table for server-side joining.

@xfitguru

Since I don't know what your database server data looks like, can only say that I'm pleased that you are working with your DBA.  You are not alone on your end of this problem and the DBAs will have the authority, knowledge and skills required to implement any of our suggestions an monitor and tweak the Oracle queries and tables.

Thanks aikimark.  I appreciate that.  I have a close relationship with my Oracle DBA team since they feel my pain in terms of enhancing our user interface between Office and Oracle.  I have been on the witch hunt to identify absurd and/or impractical processes and streamline them to the best of my ability and with A LOT of help from EE (especially Martin Liss!!).  Have a great day sir.

Here's an update that is a proof of concept only. Some notes and questions:
1.    You should add an Index for Part Number in the Item Master Item table
2.    While the name of the file says it's version 46, internally it's version 45.
3.    When you talk with people at ADS, what do you call the Oracle database?
4.    The search function currently only asks for part number and in my fully fleshed-out version of this I plan on having two comboboxes at the top rather than the one textbox. One combo will list all the vendor names, and when one of them is selected the other combo will list the part numbers, and clicking one of the part numbers will do the search.
5.    In the search results is ADSCost = Vendor Cost (in the Items table)?
6.    In the search results is MSRP = Market Price?
7.    In the search results  do I need to convert the country code to the country name?

To run the search, click the new 'Search' button, enter a part number and click 'Search' on the form.
Is the search performance (by that I mean nits run time) acceptable?
ADS-Parts-Help-Desk-V46.xlsm

Hello Marty.  I do apologize for the disconnect in the attribute names.  That was sloppy on my part.  I have provided answers to your questions below.  Thank you again for your help man!!

4. "is the user allowed to just search for part number if they do not know the vendor name". I'll have to think about how to do that but what if there's more than one vendor with the same part number? I assume then that you'd want the user to be given the ability to choose the appropriate vendor, right? The complexity begins:)

Is the response time OK?

Good news is that our database does not allow duplicate part numbers.  So technically, a part number is unique.  Now, some issues have came up in the past with 0's preceding a part number thus resulting in 0250 becoming 250 in ,csv files.  That is why I wanted to get away from using spreadsheets as our source for data and use Oracle instead.  So, if we pull the part numbers directly from Oracle, there should be no issue with duplicate part numbers. Right?

The response time is CRAZY fast!! How did you get it that fast?  Good stuff man!

Okay I won't worry about duplicate part numbers. And the Part Number Index allows ADO to go directly to that record which is fast.

Wow.  I am convinced.  It is fast! With the previous version, I was having speed issues when I go into the NIUF and hit the back button to return to the home page.  It appeared as though it was reading the entire list of item numbers every time.  Now it speeds right through it!!

It appeared as though it was reading the entire list of item numbers every time.

It was, sort of:) In previous versions I was opening and closing the DB connection in several procedures. Now it pretty much stays open once it is opened. We've built this application piecemeal and for quite some time it didn't matter. As the complexity has grown I thought on occasion about making the change but it never really mattered and so I left it as it was. Once we linked to the Oracle database I should have done it, particularly since in another thread you said it was slow, but for whatever reason at that time I didn't think of making the change.

Here's version 46 which includes the following.

o Added a Part Number Inddx in the Items table (Chris, make sure you've done this)
o Corrected a bug where Ctrl+2 macro was available from the NIUF (Chris have you ever used the Ctrl+1 and 2 shortcuts on the BOM?)
o Made several changes so that the DB connection stays open as much as possible
o Added a search function including a 'Search' button on the HelpDesk sheet
ADS-Parts-Help-Desk-V46.xlsm

I forgot to mention that this version does what you want. You can either choose a vendor name and then select a part number and click Search, or you can manually enter a part number and then click Search. In the latter case the Vendor Name will be changed to the vendor associated with the manually entered part number.

Hi buddy.  The search tool is looking incredible.  I do have one question.  Can we change the order of the Vendor Dropdown and Part Number Dropdown?  

Also, just want to run something by you. I am having pretty long wait times while searching by the Vendor.  Is there any way to alleviate this or do you think it would be best if we limit the search to only part number and add the Vendor name as one of the search result fields?  The part number search is incredibly fast, so I thought I'd run it by you.

I did not catch this yesterday, but could we add another field to the search results.  The Expired flag?  This would be another attribute they would look for if attempting to have an item expired or not. Thanks again for all of your help buddy!!

Can we change the order of the Vendor Dropdown and Part Number Dropdown?

Sure.

I am having pretty long wait times while searching by the Vendor.

What do you mean? All the vendors are listed in the 'Vendor Name' combo box when the userform is shown.

could we add another field to the search results.  The Expired flag?

Sure.

In postID ID: 39516717 I had a comment and a question.

o Added a Part Number Inddx in the Items table (Chris, make sure you've done this)
o Corrected a bug where Ctrl+2 macro was available from the NIUF (Chris have you ever used the Ctrl+1 and 2 shortcuts on the BOM?)

I just made the "change the order of the Vendor Dropdown and Part Number Dropdown" change you requested but it IMO it doesn't have the right "feel". The way I planned this search function was that most of the time to have the user select a Vendor Name which automatically fills the Part Number combo box with all the parts for that vendor. He then selects the part he's interested in and clicks the Search button. Most solutions that involve two related comboboxes are done in a left-to-right manner with the primary combobox on the left. In my design the Vendor Name is the primary and having it on the left seems better to me than the right-to-left that results from the change. Are we using the search in the same way?

I'll do it either way, just let me know.

Please also see post IDs 39518287 and 39518376.

When we created the TrackRequests database, all it did was what the name implies. It now also is the repository for the data that winds up in Form Codes and the place where official version numbers are stored, so how would you feel about changing its name to something more general like ADS.mdb or HelpDesk.mdb or some other name of your choosing?

Hi friend.  Sorry for the delayed response.  It's been a long day, and it's still going.  To address your questions:

In response to your first Thread ID 39518287:

What do you mean? All the vendors are listed in the 'Vendor Name' combo box when the userform is shown.

 
In response to Thread 39518376:

In response to your most recent Thread 39518716:

Again, I know I always say, but I sincerely and truly thank you so much for the help and for teaching me a TON!! You just don't know.  You're turning me into a prodigy at my workplace.  Hahaha  Thanks man!!

What do you want to call the former TrackRequests.mdb?

Let me do some research on improving the 45 to 60 second time it takes for you to get the data. Note that in my smaller version of the ItemMaster it's seems almost instantaneous.

Also, I am unable to manually enter a part number for the Kit on the Kit BOM form.  I am referring to the very first row which contains the "header' part number.  This is not a problem when it is automatically populated from the NUIF.  This only occurs when manually attempting to modify that field

. It's my understanding that if a kit is generated from the NIUF that you should not be able to change that part number in the BOM. If my assumption is correct then you should not be able to change it.

@Martin

Do you have a feel for how much data can be brought down in some reasonable time period?  Maybe you can populate a recordset with all the item data and then filter it locally when you need a user-selected slice of the data for the combobox/dropdown.

@aikimark: The production version of xfitguru's database has a table with over 600,000 records. Two of the fields in that table are Vendor Name and Part Number and I'd like to get all the parts numbers for a given vendor as quickly as possible. I assume that that means adding an Index to the table that consists of Vendor Name and Part Number which I did for the VendorPart Index you see here.
Assuming that that was the right thing to do, how do I use that Index in the most efficient way possible. If it wasn't what should I do?

Also I'm not sure what you mean by "Do you have a feel for how much data can be brought down in some reasonable time period? " but a given vendor may have from 1 to 100 or more part numbers.

@Martin

Are you showing me the index definitions on an (attached) Oracle table or an Access table that has all of the Oracle data?  The indexes need to be on the Oracle table.

I would create the index on the (VendorID , PartNumber) columns.  Searching text columns is much slower than a Long Integer field.  I assume that the VendorName does not related to different VendorID values and that you already have the Vendor data from a prior data request.

The use of an index is determined by the database engine, so this two-column index might not even be used if your query only references the VendorID column in the Where clause.

Also, some queries are more 'sargable' than others when it comes to avoiding table scans.

This is where my earlier comments come into play.  If you are trying to retrieve data from both the local Access table(s) and the Oracle table(s), you might be causing all the data to be brought down to Access instead of only the relevant rows.

I was thinking about caching the index data in a recordset variable that could, possibly, access the data in memory faster than another call to the Oracle database.  I do not know the source of the performance bottleneck from my vantage point.  It might be useful to add some performance metrics to your code.  Log the events to a table or text file.

=================
@xfitguru

Your DBA group should be able to capture some performance metrics on these queries.  Please share with use, the explain data.

Also, please ask them how the table defined.  If you are normally accessing the parts relative to each vendor, then a clustering arrangement might be optimal.

The database that xfitguru and I are using is an Access database that has a table called Items1 that is a link to another Access database. User1 in production has 600,000+ records and contains Vendor Names (no dupes) and Part Numbers (no dupes) along with other data and the VendorPart Index is in that table. In a userform there is a combo box that contains all the User Names from the Items1 table. That's quick and easy. There's a second combo on the userform that is meant to be filled with all the Part Numbers associated with whatever Vendor Name the user selects in the first combo box. Before I added the VendorPart index I was using a simple rs.Open "Select * from Items1 Where [Vendor Name] = '" & cboVendorName.Text & "'", gConn to retrieve the data and xfitguru is finding that it takes almost a minute to retrieve the data. I thought that an Index like I just created would help but I don't know how to take advantage of it, or even if it will help at all.

In your comments you mentioned VendorID and I assume you meant Vendor Name which as I mentioned is the name of the field in Items1.

That's an interesting idea and I'll try it.

What do you think of the idea of using a stored procedure?

Wow! I am learning a lot from your dialogue.  Two Experts sharing ideas, it's like Clash of the Titans on EE, except there really isn't a clash but merely friendly suggestions.  Both of you guys are way above me in terms of knowledge, but watch out, I'm catching up.  Thank you both so much for the help here!

@Marty
In response to Thread ID: 39519940:

@aikimark
I am coordinating with my DBA to collect the following info:

Again, let me thank you both for showing interest in helping me.  I sincerely appreciate the help and am grateful for the shared knowledge here.  God bless!!

@Martin

I was thinking of Oracle stored procs in my initial comments.  You might have to pass a non-trivial list of values to a stored proc to simulate a table join on the Oracle server with some Access data.

@xfitguru

If multiple people are using this application, each user should have their own copy of the front-end (FE) database, containing the forms, reports, queries, code, etc.  There can be one or more back-end (BE) databases connected to the FE database.  I've seen some performance problems at client sites because of FE database sharing.

This thread is a good example of how EE should work.  You have asked a clear question, posted supporting/example files, correctly zoned the question, and actively participated with the experts.

You said...

@Marty
In response to Thread ID: 39519940:
Let's name it "ADS.mdb" or "ADS.accdb"  - I believe we are using Access 2007 now, correct? Either way, they are saved in the *.accdb format on my server. Should I change them?

The current code refers to the mdb version of the database because we previously had problems with the accdb format, so yes you should change them and rename it ADS.mdb.

The attached Help Desk version includes the following:
o In the Search userform
    - Added Expired Flag to the returned values
    - Allowed more room for Description, and alphabetized the result fields.
    - Added a second, invisible column to the cboVendorName combobox, populated it from Vendor ID  and used that as the key for retreiving Part Numbers instead of Vendor Name (following aikimark's suggestion). In the SQL  for that I changed it so it returns just the fields it needs rather than doing Select *
    - Loaded the data for efficiently into cboVendorName by using the List property rather than looping through the table values
o Changed the name of TrackRequests.mdb to ADS.mdb

I have two questions.
1) Is the part number retrieval now faster and if so is it acceptable?
2) You never mentioned if there was any slowness with the Vendor Name combobox being filled. If there was is it faster now?

I'm also attaching the TrackRequests.xlsm file because it needed to be changed so that it refers to ADS.mdb rather than Trackrequests.mdb. Even though we changed the name of the database I don't think that the name of this Excel file should be changed because all it does is track the requests.
ADS-Parts-Help-Desk-V47.xlsm
Track-Requests.xlsm

Sorry for the delay.  I just got a chance to review the files.  The Track Request looks great.  The spreadsheet said version 8 so I went ahead and put that in the DB as well.  I had a couple of issues at first then realized that was the issue.  The Help Desk is amazing man!  It is exponentially faster!!!  Wow.  

The Vendor Name combobox is filled instantly. When I search by a specific part number, the Vendor name is there instantly.  When I open the search userform, the combobox fills instantly.  I have not seen any slowness with that whatsoever.

The part search is way faster.  It is easily acceptable.  I am still running through a few tests but so far, it is GREAT!! Thank you so much Marty!!!

Uh oh.  I am running into a problem when I go into the NIUF and try to go back to the home screen.  The error message below pops up:


This is the line of code that is highlighted to debug - It is located in
 'Public Function ValidateNIUF() As Boolean'

 rs.Open "Select Count(*) as rsCount from Items1 Where [Part Number] = '" & .Cells(lngRow, C.VendorPN).value & "' and [Vendor Name] = '" & .Cells(lngRow, C.VendorName).value & "'", gConn

Select allOpen in new window

When you were testing, did you go into the Visual Basic Environment at any time? If so that will cause that error and you can restore the connection by typing Ctrl+Shift+R (where R is for Restore connection).

Roger that.  It worked.  Thanks for the tip.  Sorry for the silly question.  This question is answered as far as the item search is concerned.  I am still working with my DBA to get the performance metrics for the table in Oracle.  Can I go ahead and mark this as complete and post my next question relating to a New process on this form?  This search is phenomenal!  It's really fast for both searching the item and for the vendor search (filling the combobox).  It's great.  Thank you so much for the help again! :)

If you don't have any performance issues, it probably isn't necessary to gather the data I had mentioned/requested.

If you still have any performance issues related to this question we prefer you leave the question open until the problem(s) have been resolved.

Roger that aikimark.  Thanks for the update.

@MartinLiss

Hello Marty, I have been testing the form all morning and have a couple of questions.  

Question 1:

Question 2:

1) I'm not at home right now but ire ember from my testing that if, say, I started to type Oakley into the Vendor Name combo that by the time I got to Oak the Oates would be listed. Doesn't that happen for you?

2) I'll look into that when I get back home on Monday.

I can't edit my post on my iphone but Oates should have been Parts.

For Q1, could it be that you chose a vendor that doesn't have any parts?

To answer your first question.  When I type Oakley into the combobox, the items do populate by the time I have finished typing.  

I double checked the vendor and verified that it has parts.

Here version 48 which includes the following changes.

o In the "SELECT PART NUMBER" search use Vendor ID instead of Vendor Name which is much faster
o Made several changes to the Search, including the following
    - Removed the "No parts found for vendor" message in favor of "- None -" in  cboPartNumber
    - Changed code so that cboPartNumber is not filled until the  the Vendor Name is selected from the list, the entry is clicked or Enter is pressed. Note that former problems with certain Vendor Names seemingly not working were do to the fact that formerly the "Click" event of the combobox was used as a trigger to fill the part number list, but that event only fires when the letters typed are unique in the list. For example as soon as "OA" was typed the click event would fire because "OAKLEY" is the only vendor that starts with "OA", but when typing the letters in "BENCHMADE KNIFE CO", the Click event would not fire until "BENCHMAD" was typed because there's another vendor named "BENCHMARK CRAFTSMEN" and you need to type until the "D" to make the letters unique.
ADS-Parts-Help-Desk-V48.xlsm

I really like the functionality!  It returns results very fast for both part and vendor searches.  I do have one concern.  I hope it isn't petty but I just want to avoid any confusion.  When I click into the Vendor Name, by default it states, "- Select Vendor Name -" but still allows me to enter text into that field.  See the picture below:

When I do this, I receive the error message below.  While it is not an issue for me, I am concerned about my users.  Can we make the default "- Select Vendor Name -" unwritable?

Add line 3 to 7 and let me know if that works for you.

Private Sub cboVendorName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

If cboVendorName.ListIndex = 0 Then
    KeyCode = 0
    Beep
    Exit Sub
End If

'******* 48b Start *******
If KeyCode = vbKeyReturn Then
    SelectVendor
End If
'******* 48b End *********
End Sub

Select allOpen in new window

There is a LimitToList property for combobox controls that should be set to True.

There is a LimitToList property for combobox controls that should be set to True.

I believe that that only applies to comboboxes in Access.

True.  I didn't know if you might be using a compatible combobox control.

That seemed to do the trick.  Now the user cannot add values to the default '- Select Vendor Name -'.  

I am not sure if this was happening before, but now if the user selects a Vendor name from the drop down and hits the space bar, Delete, or any other button, the value is replace or removed.  Then, when the Search button is clicked or the Enter key is pressed, the same error message populates.  Please forgive for not noticing this before.  I hate not giving you all of what I find all at once.  Sorry about that friend.  

No problem. To fix it add two pieces of code.

1) In the SelectVendor sub change

If cboVendorName.Text <> SELECT_VENDOR Then

Select allOpen in new window

- to -

If cboVendorName.Text <> SELECT_VENDOR And Trim(cboVendorName.Text) <> "" Then

Select allOpen in new window

2)  Add lines 7 to 9 in the following sub.

Private Sub cboVendorName_Change()

    '******* 48b Start *******
    cboPartNumber.Clear
    '******* 48b End *********
    'new
    If Trim(cboVendorName.Text) = "" Then
        cmdSearch.Enabled = False
    End If

End Sub

Select allOpen in new window

This looks great.  I have tested and have not had any issues as of yet.  It goes much faster now than when we started.  Thanks to aikimark for pointing some things out for you.  I'd like to thank you both for your help and support here.  Truly phenomenal!!

This looks great.  I have tested and have not had any issues as of yet.  It goes much faster now than when we started.  Thanks to aikimark for pointing some things out for you.  I'd like to thank you both for your help and support here.  Truly phenomenal!!

As always you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013

I have posted another question concerning a brand new "module" I am attempting to add to the Help Desk.  This will involve Purchase Orders and allow the user to submit requests to have Purchase Order attributes updated or have the Purchase Order line cancelled.  I am sure I have not provided the best explanation so please feel free to ask away if you decide to help out with this one.  Again, I really appreciate all of your help and support. I really have learned a lot from you.  That's something I will always keep close.  

New Help Desk Question