Can anyone suggest a good primer on how the dbf indexes work, and thus how they can be optimized. I'm sure it's a form of B-tree setup but the particulars like cardinality, and selectability are unkown(s) to me at least.
-
-
Re: Indexes
not sure about a primer, but:
I like simple indexes without filters. They just seem to work better.
It is a good idea to have an index on fields that would connect 2 or more tables in a set.
The indexes are save in a cdx file on disk.
I believe you can create and delete indexes on the fly with code.
Yoy can also use a query which basically filters records into or out of whatever you need the data for
Lightning query can be set up and there are some good threads on here to look that up.
Good luck
Others are probably more knowledgeable than I on this, but these are the basics.
Btrieve was fast, wasn't it??
. -
Re: Indexes
Not sure what you mean by optimizing indexes. Here's the primer on optimal use of indexes. You would need to prioritize your expected query usage.
The downside to indexes is that they must be updated as records are added. Updating requires (due to possible hardware issues) preserving the current index file, building the new index file, removing the old index file. For several index tags on a large file this can be a significant amount of processor time.
Watch the blue progress bar creep on a ten-fifteen million record 1.6 gig file a few times and you'll reconsider the actual need for all of the indexes.There can be only one.Comment
-
Re: Indexes
Dave, Stan, thanks for the feedback,
My experience is that good index management and usage can boost performance, and poor index usage can indeed wreak havoc.
I'll study up and test the lightning query (Something I had read about but had forgotten).
Often I have seen posts in respect to Alpha's speed, and I suspect the real problem is many users and developers not using indexes and other features appropriately.
Note: I have one operation that processes, 200,000 records at button press and release speed, but another very similar operation, takes enough time that I can make an drink a coffee etc.Comment
-
Re: Indexes
The short answer is the fewer indexes the better and if you have an issue with speed/reliability, move to SQL. MS SQL 2008 Express is free -- as are a couple other SQL DBs.
I plan on sticking with DBF to begin with but as the files grow, reassess my options.
Regards,
Greg WoodComment
-
Re: Indexes
Colin:
That's a good question, unfortunately there is no good answer, not in Alpha and perhaps not in any other DB. I don't believe you could control how a B-Tree is structured. It's an internal function of the DB. Alpha certainly uses B-Trees for indices, unfortunately there is no way for you to know what scheme regarding Cardinality & selectability is used except if you have the patience to add one record at a time and try to make some sense out of it. And assuming you figured it out, it won't help you a bit as you cannot change that scheme. I venture to say that most B-Trees operate at the same scheme but as to how wide and how deep, it's really a question for the ages.
From a practically point of view, you need to decide:
Do you really need an index?
That's a tough question to answer. Programmers figured out that if an object could travel at the speed of the light, you cannot make it travel faster than that but you could make it look like it does. How?
The best example is how webpages load.
Sorry,...have to go. Will finish this worthless banter later..Comment
-
Re: Indexes
"G Gabriel"
I'm finding indexes to be like a black box that I keep putting things into but I have no idea of what happens to them in the inside.
Regularly I create new indexes and have no idea whether I have created them optimally, or near optimally.
What I'm looking for is some general rules as per the lightening query page that suggest how to treat and use indexes.Comment
-
Re: Indexes
Related to but not your original question -
The Alpha help file has tons of info. I think around 8700+ pages. I remember finding a write up on index -- and couldn't find it. But I did find another one.
Help -> Getting Started -> About Indexes, Queries, and Ranges
Two things that I found in that article:
- Indexes work on primary files, not sets. If I read that right, you can't build an index that bridges two primary files even if they are joined by the set.
- Queries are static snapshots and updates are not automatically maintained by Alpha.
Comment
-
Re: Indexes
Colin:Originally posted by ColinJD View Post
I owe you an apology. I think I , unwittingly, must have offended you with the remark "worthless banter". I was referring to my part of it, not yours. The problems, when you start talking about indices, it could go on and on and I felt that I started doing that and wasn't getting anywhere fast.. so I dropped it for later time.
Just a quick mention, you could create your own indices with your own parameters, only problem with that is: How are you going to use them in alpha? You pretty much have to re-write all the indices functions used. But again, it's doable. Would it make much difference? I doubt it, which takes us back to the original question: do you really need an index?
When time allows, we could explore that further..Comment
-
Re: Indexes
Indexes become critical when queries are done on very large files. The necessity for an index sometimes does not become apparent until a table has a very large number of records A well designed index invoking LQO can make queries run hundreds of times faster than without.
As far as the speed of operations goes, there is an article in the newsletter archive which discusses speeding up operations.I suggest you read it and see if the suggestions in the article apply to your situation.
JohnComment
Comment