none
Performance problem using Microsoft.ACE.OLEDB.12.0 RRS feed

  • Question

  •  

    Hello,

     

    I have create DB using MS Access 2007. The structure is very simple...

     

    table Feature: [id], [long name1], [long name2], [long mydata], ...

    table FeatureName: [id], [string namecol]

     

    I.e. the table Feature refers twice into table FeatureName.

    Next, there's a view FeatureView over these tables: [id], [string name1], [string name2], ...

     

    Because accdb file has about 500MB (cca 3,5 milion records), I've created indices over [name1], [name2] and [namecol].

     

    Now, I get very strange performance results for the following queries:

    SELECT * FROM FeatureView WHERE (name1 = 'asdf') - about 20ms

    SELECT * FROM FeatureView WHERE (name2 = 'asdf') - about 20ms

    SELECT * FROM FeatureView WHERE (name1 = 'asdf') OR (name2 = 'asdf') - about 60s!!!

     

    I'm using Microsoft.ACE.OLEDB.12.0 provider, but similar result I got directly in MS Access 2007.

     

    Has anyone idea, what's happing? It seems to me, that using 'OR' in query somehow denies indices over columns, because the first two queries would take 60s as well, if I do not create index.

     

    It is ridiculous, because if I do the first two queries separately, I'm done within 40ms, but single combined query takes over minute?!

     

    (Btw, I can't use SQL Server nor Express, because I have product targeted for end users having slow machines.)

     

    Thanks,

    Tomas

     

    Saturday, April 5, 2008 4:52 PM

Answers

  • Another fairly simple solution that might work for you (without having to add more indexes):

     

    Change this ->

     

    SELECT * FROM FeatureView WHERE (name1 = 'asdf') OR (name2 = 'asdf') 

     

    To this ->

     

    SELECT * FROM FeatureView WHERE (name1 = 'asdf') 

    union all

    SELECT * FROM FeatureView WHERE (name2 = 'asdf') 

     

     

    Matt
    Monday, April 7, 2008 11:16 PM

All replies

  • What happens if you create one index that is combination of both columns, not two separate indexes? Have you tried to do this?

     

    Monday, April 7, 2008 9:48 AM
    Moderator
  • Another fairly simple solution that might work for you (without having to add more indexes):

     

    Change this ->

     

    SELECT * FROM FeatureView WHERE (name1 = 'asdf') OR (name2 = 'asdf') 

     

    To this ->

     

    SELECT * FROM FeatureView WHERE (name1 = 'asdf') 

    union all

    SELECT * FROM FeatureView WHERE (name2 = 'asdf') 

     

     

    Matt
    Monday, April 7, 2008 11:16 PM
  •  

    Hello,

     

    Val, Matt, thank you for your tips.

     

    I've tried to created single index over both fields, but the only result was that simple select over one column decreased to 4 seconds. Combined select using OR remained at 60 seconds.

     

    Union of the two selects works for me.

     

    Best,

    Tomas

     

    Thursday, April 10, 2008 8:48 PM