Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very slow query (MSACCESS 2003)

    The query below runs extremely slow, its probably due to what it does.

    Me.List5.RowSource = "SELECT DISTINCT KEYWORDS.Keyword, TXMASTERS.SportorSports" _
    & " FROM (TXMASTERS INNER JOIN TXCLIPS ON TXMASTERS.ID1=TXCLIPS.ID1)" _
    & " INNER JOIN KEYWORDS ON TXCLIPS.Comments Like '*' & KEYWORDS.Keyword & ' *' " _
    & " WHERE TXMASTERS.SportOrSports & ' ' Like '*" & Me!LNAME55.Caption & "*" & "*' " _
    & "ORDER BY KEYWORDS.Keyword"

    It looks through all records to check a sport field matches a chosen sport, it then sifts through the comments (memo) for any matching keywords to a table of keywords. The list fills with appropriate keywords found for the sport. Any suggestions on if/how the query can be speeded up? Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Very slow query (MSACCESS 2003)

    I don't know what your data look like, so I can only give a general reply. I suspect that the inner join between TXCLIPS and KEYWORDS is the main cause of the slowness because it joins with a Like condition: searching through the contents of the Comments field for a keyword is much more work than comparing the entire field to the keyword. I don't think you can do much about it if you want to keep this setup.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very slow query (MSACCESS 2003)

    Thanks Hans. I think the best way round the problem is to have a child table to the table that holds a list of sports. Then to arrive at a list of keywords for each sport can be entered and that would solve the issue. I inherited this problem, and it can only get worse when the table of records grows. Someone did not think it out first. Thanks for the confirmation, regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •