1

I have indexed some columns in my MS Access database, and I am using Java to query the database.

Before indexing, I used this code:

ResultSet rs = statement.executeQuery("Select * from Employees where FirstName = Sarah");

After indexing some columns in the database, should I make any changes to the code. Is there something like this needed/possible:

statement.getIndexes();

I am asking this because my MS Access database has 300,000+ records. Fetching records was too slow because of the size. After indexing, fetching records did not speed up at all. I think I might still be accessing the unindexed version of that column.

(I am writing the code for an Android app, if that matters)

sga4
  • 350
  • 4
  • 15

2 Answers2

0

No. The SQL command tells it to return a certain result, how it finds that result (use of indexes and the like) is an implementation detail of the db. Now you may need to do something on the database to get it to implement the index. Although you really ought to think of moving to a real database, Access is just not meant for large amounts of data.

Gabe Sechan
  • 90,003
  • 9
  • 87
  • 127
  • Thank you for the suggestion. I just figured out the problem is not with querying. It is the connection that's taking time to establish. The first-time ucanaccess connection between my app and the database requires a lot of heap memory, which mobile devices do not normally have. That is the reason why the connection takes too long, and the app crashes when the heap runs out of space. – sga4 May 13 '15 at 11:52
0

It's likely that your issue is the query. You should never use select * from a table. Always specify your columns. Have a look here.

Community
  • 1
  • 1
MadConan
  • 3,749
  • 1
  • 16
  • 27
  • Thank you for the suggestion. I just figured out the problem is not with querying. It is the connection that's taking time to establish. The first-time ucanaccess connection between my app and the database requires a lot of heap memory, which mobile devices do not normally have. That is the reason why the connection takes too long, and the app crashes when the heap runs out of space. – sga4 May 13 '15 at 11:52