• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

indexing issue

 
Brian Percival
Ranch Hand
Posts: 163
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need a clarification from the experts. I have a db with more than a million records. I have a area code field (text) and phone number field( text). And then a different table with a phone field (includes area code also, the field is text).

I need to do some filtering, basically spitout records from table2 which do not have phone numbers (including area code)from the table1. since table1 has over a million records( table2 has like tens of thousands. I know in this case it is good to do filtering based on indexes.

My dilemma is, which would be faster? in both tables create a temporary field (int) which will have full number (areacode+text), create two indexes on the new fields in two tables, and then do a sql statement? or should I just go on with how it is right now, make the full numbers (in table 1) on the fly and search against numbers in table2? this si going to be a text against text search, without indexing, so it would be slow too. Trade off here is time to create new fields and indexes vs just doing it as I see it.

What do you suggest? I cann't experiment myself since this is a access database on a speed/memorywise challenged machine and it takes forever to even do one trail.

Please advise...

Brian
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33680
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian,
I suspect the answer to which is faster highly depends on the database. You can still try it both ways though.

Add a where clause to limit the phone #s searching to a small subset (maybe 100 rows.) Then try it both ways and see which is faster.
 
Joel McNary
Bartender
Posts: 1824
Eclipse IDE Java Ruby
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does access even support indexes? And why would you create the new fields as ints?

I don't even think that creating indexes would necessarily work, even on a system that supports indexes. The reason is that typically if more that 10% of the table would be returned, the system determines that a full table-scan would be better. So you wind up with the un-indexed performance. Now, I can't say with authority that this is what would happen here, and you might get a little bit of improvement, but I would be suprised if you got great performance.

What do I suggest? It's hard to say, but if you create the new field, can you change the logic so that the new field is populated? If not, then it's probably better to just do it as-is. Obnoxious and annoying, but probably is less headache.

Of course, I'd probably complain that you can't expect good performance from an access database anyway and then implement the logic in Java.
 
Brian Percival
Ranch Hand
Posts: 163
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your replies. Yes I would have to try it out with limited number of records. And yes, access supports indexes. The reason I am preferring int fields is because (I believe) a text comparision of numbers is slower than number comparison. Planning to use indexes because indexes (hopefully) would aid me in speedingup comparisions between the tables. Only I want to make sure the overhead of creating and maintaining indexes will not negate their hepfulness.
 
stu derby
Ranch Hand
Posts: 333
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know about Access, but many databases allow function-based indexes; if Access supports them, you could have a function-based indx that is the integer result of your two text columns, thereby avoiding the "temporary" column.
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Joel McNary:
Does access even support indexes?


Yes.
 
Balazs Borbely
Ranch Hand
Posts: 33
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A few possible solutions (I don't which would be applicable, depends on your business logic, and constraints)

-make those fields as number not text, since the area code is a 3 digit number and the phone number a 6 digits number.(number field + index=> good performance)
-if not you can use indexes on text fields as well

-use a professional database as Oracle (free for developers), or PostgreSQL (open source)

- use the same primary key for both tables , and that primary key should be a number, (the search on primary key is very fast)
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic