Will Indexing speed up Application Server?

[LTinPB]

Will the App server speed up searches, if I prepare database indexes for each field that is searchable in the web?

I have tried creating indexes with the database A5 then moving that index file into the WAS folder. On searching with via the web, the search times seem to be the same, with and without any database indexes.

[Steve Workings]

Re: Will Indexing speed up Application Server?

Indexes will help with queries if they can be used for Alpha's Lightning Query Optimization (LQO). Check the help docs for starters on this.

[michaelwpayton]

Re: Will Indexing speed up Application Server?

Proper use of indexes will, of course, speed database access. However, increases in what a user experiences/interpets as the "speed" of a Web app typically has more to do with the app's design, page rendering, etc. This is, ALWAYS, due to the "slow" (read: Internet) environment in which Web apps are delivered to users.

That is, the time associated with your code executing on, and accessing the database on, the Web server is very likely to be minimal compared to the time required to deliver, across the Internet, responses to your end users.

[LTinPB]

Re: Will Indexing speed up Application Server?

Thanks. I will run some more bench tests on Web App with and without index files.

In addition, two other factors seem impact speed:

1. RAM available on the server, and

2. The "Search Style" # 2 "Is contained in" is much slower than 1 or 3 (1=exact match, 3= "starts with"). That makes sense since the string must be examined in pieces; instead of matching against the whole fields. Changing the structure of the database to accommodate #1 or #3 can speed up searches. For example, using #2 with only one field with a full name ("John Jones"); searches with only last name are slow. It's faster to have two fields: first, last and #3.

Thanks again.

[Lawrence]

Re: Will Indexing speed up Application Server? YES

Here are results of a couple of tests, with and without indexes:

#1. File with 1.2 million records, 16 fields, record length=202, 3 search fields indexed:
Seconds to search: Not indexed = 13 seconds, Indexed = 2 seconds.

#2. File with 1.2 million records, 65 fields, record length=562, 30 search fields indexed:
Seconds to search: Not indexed = 56 seconds, Indexed = 5 seconds.

Both tests were done with "local host" to eliminate issues with Internet speed.
Also both used Search Style 3, "Begins with".

Thanks again.

[Steve Wood]

Re: Will Indexing speed up Application Server?

A related question about indexes. I had a situation yesterday where my online billing application had a bad index on the local User's table. The result of this was (for example) that Security Framework said the ULINK was 00021, which should match up to Steve Wood, but because the index was corrupt*, matched up to Jane West's user record. That mismatch is because I use index-based LOOKUPx() functions to grab extra information from my local user's table, used then to filter records. (That is, its not a Security Framework problem). So, Steve Wood, who was logged in, was viewing Jane West's private billing information. Not cool.

As soon as we reindexed, it fixed everything.

I'm not so concerned about what caused the index corruption this particular time (because it's bound to happen). But I do want to investigate how to know IMMEDIATELY if an index is corrupt and to be able to fix it, go around it, or shut the system down.

Alpha has several functions that will determine if the index is bad, and I can write a script that checks and then rebuild the index if required. So one option is to either run such a script periodically, or perhaps each time a user logs in.

Another option is to NOT use any index-based LOOKUPx() functions. Use instead LOOKUP() which does a sequential search, but its much slower.

I could shift from DBF tables to an SQL backend, which I'm sure has fault-tolerant methods to automatically detect and rebuild indexes.

Any thoughts on these or other options?

* probably "out of sync" is a better description than corrupt.

[Steve Workings]

Re: Will Indexing speed up Application Server?

In critical situations like this, I don't use the lookups at all Steve. I open the table and run a query to get the value or values.

When you run a query, then you have the whole record available for examination.

In your current example, I'd check a couple things. First make sure that the query returned but one record with qdx.records_get(), and then compare the record's key value against its expected value. If they don't match, your script will know you have a problem.

I know the method is a bit slower than the lookup() methods, but it's much better.