fast record selecting

[BruceN]

In a relatively large dbf file (possibly 500,000+ records) I need to further restrict records based on a non-indexed field value for numerous existing reports. The restriction could be {field} = a single value or {field} = one of numerous values. I'd like one function that I can easily insert into each report to do that. I can make a browse to select which value(s) to include, but the question is how to restrict the records.

My first thought is a simple filter, but form posts I've seen here over the years it seems they are SLOW. Is there a faster way to do it?

thanks to all you wizards.....

[rdonnay]

There is really no good way to do this without a filter, except you could use ADSDBE and a Data-Dictionary that would let you do this in SQL. It would be much faster.

Do you use ADS now?

[skiman]

Hi,

Sometimes I create a temporary index when creating reports. This can be done in a different thread, while the user is entering the values he/she wants. This way when the report starts, it is fast.

[BruceN]

Dont know anything about ADSDBE and didn't know you could use SQL with dbf files. Many years ago I played a little with SQL in RBase (anyone remember that?), but that was aparently a very early implementation of it.

A quick look at the xbase documentation indicates that ADSDBE is Advantge Database Server. I seem to recall hearing that that is a client-server system add-on that requires a license be bought for each user - that wouldn't work for my situation. Am I right or wrong on this?

A temporary index wouldn't be a viable solution as I would need to restrict records not by a range of values (A-D, for example) but by a selection of values (value = A, B, F, H, or X for example)... and once records are 'filtered' by that value I need to then run the basic report with it's own record selection from the subset of records. But thanks for the suggestion.

Guess I'm stuck with a filter. I'll have to play with it and see how slow it actually is.

thanks again to all you wizards out there.

[rdonnay]

You don't need to purchase anything to use ADS SQL.

There are 3 free DLLs available for use with Xbase++.

If you are interested, I can get you started and show you how to do it.
There is a full set of SQL functions in eXpress++.

[BruceN]

WONDERFUL, and THANKS... was I right about it being a client-server system?

I really don't know ANYTHING about it. Can it be used in conjunction with my existing CDX indexes (use it to filter records, then run the report wtih it's CDX restrictions, etc)?

From what I've heard, SQL is as fast or faster than std indexing and far more versitile. Is that right? It must have some disadvantages, what?

[rdonnay]

People often are confused about SQL and client/server. Yes, SQL is traditionally a client/server concept, but does not have to be. SQL means Structured Query Language, therefore it is really a language for communicating with databases. Client/Server doesn't have to be SQL. Ads supports standard ISAM (Indexed Sequential Access Method) in both a Remote Server mode and a Local Server mode.

If you are using Remote Server, you need to purchase the ADS remote software. This gives the most performance and security. I am currently consulting to Sybase to help them write a getting started guide for Ads and Xbase++. The intent of this document is to show Xbase++ programmers how easy it is to use ADS for both ISAM and SQL. Rather than try to cover everything in this forum, I am going to suggest that you wait awhile til this document is completed.

SQL and ADS may not be necessary for what it is you are trying to do. I am thinking that you may be able to accomplish what you want by using the DC_SetScopeArray() function of eXpress++.

Here's a scenario:

You want to produce a browse of a subset of data and you want the navigation of the browse to be very fast. There are 4 ways I can think of to accomplish this:

1. Use DC_SetScope() to set a scope based on an index, then SET FILTER to additionally filter records. The combination of a Scope and a Filter is much faster than just a filter, but it requires a controlling index.

2. Set a Filter on the database, then build a record array by skipping thru the entire database and adding each record number to the array, then applying the record set array to the workarea using DC_SetScopeArray(). Unfortunately, this requires a complete pass thru the database to build the record set array, but after that first pass, everything is lightning fast.

3. Build a temporary conditional index using the filter as a FOR condition.

4. Build a dataset using a SQL SELECT statement. This can be done via the ADSDBE and a data-dictionary or simply using DC_ExecuteSQL() which requires no ADSDBE and dictionary, but instead uses free client dlls from Sybase. The returned data set is a SQL cursor that can be passed to DCBROWSE. This takes a bit more code, but I'm working on making it even simpler than it already is.

[BruceN]

thanks... that's lot to go thru and 'grock'. let me look it over for a few days in my copious free time.