locked
Querry Optimisation on an Access Database RRS feed

  • Question

  • I was wandering if anybody could help  chip in an idea for me to speed up data retrieval for my access database.

    The database has about 900 thousand items which is huge.

    I suspect that it be as a result of how my query is setup. its a very simple query but I have a feeling that it could be optimised better.

    DevilSQL = " SELECT Column1, Column2, column3, column4,column5,column6,column7,column8 " _
                & "FROM LLATTRDATA " _
                & " WHERE Column1 LIKE '*" & Me.txtkeywords & "*' " _
                & " OR Column2 LIKE '*" & Me.txtkeywords & "*' " _
                & " OR Column3 LIKE '*" & Me.txtkeywords & "*' " _
                & " ORDER BY Column1 "
    

    DevilSQL-Improved = " SELECT Column1, Column2, column3, column4,column5,column6,column7,column8 " _ & "FROM LLATTRDATA " _ & " WHERE Column1 IN (SELECT Column1

    & "FROM LLATTRDATA " _
                & " WHERE Column1 LIKE '*" & Me.txtkeywords & "*' " _
                & " OR Column2 LIKE '*" & Me.txtkeywords & "*' " _
                & " OR Column3= textboxToInteger " _
                & " ORDER BY Column1 " )

    I tried to redo the first query to the improved version. But I'm not very sure if I should be looking at the query or looking somewhere else. The columns that are in the where clause are indexed.

    Does anyone know how I can improve the speed of my database. I understand that my database is large anyway and I can expect some delay in data retrieval. Buut at the moment it takes a minimum of 1 minute to retrieve a search item.

    Tuesday, March 22, 2016 11:54 AM

Answers

  • i am interested in all the words in 3 columns from the original table. would i correct by creating a new Keywords table  and storing these 3 columns then creating a query like the one you posted to search through the keywords table first before then retrieving the record from the original table? 

    Apologies if i'm repeating exactly what you've said. This makes perfect sense to me now. i'll go ahead and try this now, hopefully it works(fingers crossed).

    Very good.  To break the text in those columns up into individual words, you would probably use the VBA Split() function.  If the text might contain punctuation marks that you would want to ignore, you might first use the Replace function to replace each punctuation mark into a space, and then use the Split() function with the space character as a delimiter, to return an array of words.  This would probably not be completely perfect, but adjustments to the code could be made as you see what exceptional cases you run into.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by David_JunFeng Wednesday, March 30, 2016 9:40 AM
    Wednesday, March 23, 2016 9:03 PM
  • If you are able to use a free edition of SQL server, you could place the tables into that system and indexing of keywords is built into the system.

    I guess this comes down to:

    a) Can I use SQL server here - how much time to learn

    b) Roll your own keyword indexing system - this can be a fair bit of work based on your level of VBA skills

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by David_JunFeng Wednesday, March 30, 2016 9:40 AM
    Thursday, March 24, 2016 3:23 AM

All replies

  • Hi,

    Have you an indexe in Column1 ?

    Column1 does not accept the Null value.

    - Ideally it should be long digital.

    - It should not be too indexes.

    See you !

    Fabrice.


    Visitez mes sites : http://2gm.free.fr | http://loufab.developpez.com

    Tuesday, March 22, 2016 2:14 PM
  • Do you regularly compact the database?  Are you running this query directly in the query window?  Do you actually need to return all the columns in your SELECT clause?  Keep in mind that just because a column is in your WHERE clause doesn't mean it has to be in the SELECT as well...only SELECT the columns that you really need.  Is LLATTRDATA itself a query or a table?

    -Bruce


    Tuesday, March 22, 2016 2:21 PM
  • Does anyone know how I can improve the speed of my database. I understand that my database is large anyway and I can expect some delay in data retrieval. Buut at the moment it takes a minimum of 1 minute to retrieve a search item.

    Hi dongotti,

    Indexes do not help if you search for:  LIKE '*' & Me.textkeywords & '*', all records have to be searched anyway. It could help if you leave off the first asterix.

    Is there a way to structurize the contents of the columns 1, 2 and 3?

    Imb.

    Tuesday, March 22, 2016 2:30 PM
  • Your use of "Like '*" & Me.txtkeywords & "*'" means that, even if there's an index on Column1 and Column2, it can't be used, so evey record has to be read and checked to see whether it meets the criteria.  With 900,000 records, that's going to be very slow.

    Unfortunately, Access (Jet/ACE) databases don't have the fiull-text indexing features of, say, SQL Server.  So assuming the database containing the table is .accdb or .mdb format, and you really need to look for [txtkeywords] anywhere in those fields, the only way this could be made efficient would be to enhance the structure of your database by pre-parsing the records and storing a table of what keywords are found in what records, indexed by keyword.  Then you could build a query that joins that table to your original table on the record ID field, and applies "=" criteria to the keyword field in the Keywords table.  That ought to allow the query engine to first restrict the rows in the Keywords table to just the ones for the keyword you're looking for (using an index), and then pull the matching records from the original table.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Tuesday, March 22, 2016 2:56 PM
  • Thank you very much Dirk Goldgar  for your help.

    This sounds like a good plan. I'm not very good with VB.NET but I guess this will require some level of programming

    if I understand you correctly- the solution Would be to

    1. Create a new Keywords table to store records of what is found during a search.

    What will this new table store? will it be the keywords and say the ID column on the original table only?

    2. Create a inner Join Query between ORiginal table and new Keywords table (JOIN on ID column) applying a criteria on keyword

    Where I think I may struggle will be the LOGIC of how to combine the two. how to store the keyword in a new table when a record is found

    and then how to get the database to first check the keywords table before proceding to running the join querry.

    Wednesday, March 23, 2016 3:51 PM
  • I'm not very good with VB.NET but I guess this will require some level of programming

    Note:  Maybe your reference to VB.Net was only a typo, but be aware that Access uses VBA (Visual Basic for Applications), not VB.Net.  They are superficially similar, but deeply different.

    if I understand you correctly- the solution Would be to

    1. Create a new Keywords table to store records of what is found during a search.

    I'm not sure what you mean by "what is found during a search".  What I am proposing -- which may or may not turn out to work -- is that whenever a record is stored in the table LLATTRDATA, whatever columns might contain the keywords you are looking for be parsed to determine what keywords it contains, and a record for each of the keywords found be stored a new table that would contain these fields:

    1. the ID of the LLATTRDATA record
    2. the keyword

    This table would have indexes on the LLATTRDATA ID field and on the keyword field.

    The parsing would require a little VBA, it's true.  It isn't clear to me whether there are only certain keywords that you are interested in, or whether you need to split the text into words and store every word (except maybe certain excluded words).

    If you're only interested in certain keywords, you would probably have a table of those keywords, and the parsing process would involved determining whether any of those specific keywords is in the text.  If you need to create an index of all (or most) words, then the process needs to identify the individual words -- possibly splitting the text up on spaces and punctuation marks -- and then save all the (non-excluded) words that it finds.

    By doing this as each individual record is stored, the time it takes to parse each record is minimized and is probably negligible.  However, if you have a large existing store of records, you need to run a one-time process to pass all the records through the parser to create the initial table of keywords and record IDs.  That process may take some time to run, but once the parsing is done, searches by keyword should be much faster.  If you use a query like this:

    2. Create a inner Join Query between ORiginal table and new Keywords table (JOIN on ID column) applying a criteria on keyword

    then I don't think you need to do anything more to make the query "first check the keywords table".  However, if for some reason the database engine's default query plan doesn't do it that way, there are ways to work around it.  Normally you would write something like:

    SELECT LLATTRDATA.*
    FROM LLATTR_Keywords INNER JOIN LLATTRDATA 
       ON LLATTR_Keywords.LLATTRDATA_ID = LLATTRDATA.ID
    WHERE LLATTR_Keywords.Keyword=[Keyword Sought]
    
       

    If that doesn't run quickly enough, you could try this:

    SELECT * FROM LLATTRDATA 
    WHERE ID IN 
        (SELECT LLATTR_Keywords.LLATTRDATA_ID
         FROM LLATTR_Keywords
         WHERE LLATTR_Keywords.Keyword=[Keyword Sought])
    

    And if neither of those queries comes up with a satisfactory query plan, it would be possible to do the search using recordsets in VBA, first opening a recordset on a simple query of theLLATTR_Keywords table, and then using the set of IDs returned to build WHERE clause for a query of the LLATTRDATA table.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, March 23, 2016 4:43 PM
  • The best solution is not to rely on keywords in the text.

    I'm not sure how these records are created but create a keyword table at the point the records are created.

    You will have a separate Keyword assignment table that contains the primary key of the main table with a single other column 'keyword'.

    In order to apply this solution to your current data, you will need to write a program to build the Keyword assignment table from your existing data.

    Wednesday, March 23, 2016 6:19 PM
  •  Thanks again for your clear explanation and apologies if  i haven't explained myself properly. To answer some of the uncertainties about my issue.

    There is no growth anticipation on this database. The database is pretty much an archive and their wont be any new records added.So i'm basically dealing with a static database.

    "It isn't clear to me whether there are only certain keywords that you are interested in, or whether you need to split the text into words and store every word (except maybe certain excluded words"

    i am interested in all the words in 3 columns from the original table. would i correct by creating a new Keywords table  and storing these 3 columns then creating a query like the one you posted to search through the keywords table first before then retrieving the record from the original table? 

    Apologies if i'm repeating exactly what you've said. This makes perfect sense to me now. i'll go ahead and try this now, hopefully it works(fingers crossed).

    thank you :)

    Wednesday, March 23, 2016 7:45 PM
  • i am interested in all the words in 3 columns from the original table. would i correct by creating a new Keywords table  and storing these 3 columns then creating a query like the one you posted to search through the keywords table first before then retrieving the record from the original table? 

    Apologies if i'm repeating exactly what you've said. This makes perfect sense to me now. i'll go ahead and try this now, hopefully it works(fingers crossed).

    Very good.  To break the text in those columns up into individual words, you would probably use the VBA Split() function.  If the text might contain punctuation marks that you would want to ignore, you might first use the Replace function to replace each punctuation mark into a space, and then use the Split() function with the space character as a delimiter, to return an array of words.  This would probably not be completely perfect, but adjustments to the code could be made as you see what exceptional cases you run into.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by David_JunFeng Wednesday, March 30, 2016 9:40 AM
    Wednesday, March 23, 2016 9:03 PM
  • If you are able to use a free edition of SQL server, you could place the tables into that system and indexing of keywords is built into the system.

    I guess this comes down to:

    a) Can I use SQL server here - how much time to learn

    b) Roll your own keyword indexing system - this can be a fair bit of work based on your level of VBA skills

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by David_JunFeng Wednesday, March 30, 2016 9:40 AM
    Thursday, March 24, 2016 3:23 AM