I have a database with about 260,000 records. When searching for an product the records have to be sorted by six criteria. According to the fact that products of several manufacturers may have the same or a very similar name and that there are a lot of different types of the products a search process may result in several hundreds of matches.

As the database is queried very often the waiting time for sorting is felt to be too long.

I am looking for a quick sorting process. Quickdex could solve the problem. However it does not work according to the 64 K limit.

Does anybody have an idea how to work around this problem?

Thank you very much for helpful comments!

W. Hollenhorst

I'm a little bit confused. Are you looking for a fast search routine or a fast sorting routine? Indexing the several fields you use could help in both although the performance would vary depending upon the kind of data you have.

One thing that can frequently make a big difference is to make your search a two part search, the first part performed on an indexed field primarily to cut down the number of records used by the other criteria. In other words, a select statement such as

select filename with criterion1 and with criterion2 and with criterion3, etc.

would be changed to

select filename with criterion2

followed by

select filename with criterion1 and with criterion3

This technique is most effective when the first select narrows down the search significantly.

If it is really a sort you wish to optimize you may get better performance by creating a symbolic field which concatenates the other fields, particularly if you can do it without {} or xlate references. Then you would select by this new symbolic field and LIST (or whatever) using the regular fields.

Victor

You might be able to mix up some system subroutines in a programatic search/sort, though it would probably be specific to your application, and perhaps not easily updated when you want to change things. But I suspect you could improve your search times with a plain BTREE.EXTRACT, and V119 with the keys stuffed into an array a beneficial order and then sorted.

Sometimes the native utilties are hard to beat though. Whether your system would benefit would have to be determined on an individual basis.

I agree with the other two suggestions. Depending on your sort & search needs, you could combine several fields into one symbolic, put an index on it and use that in the query. For example, For our receiving screen we need to search a purchasing detail file to determine one items are ready to received. Several criteria must be met:

The item is approved.

The item is not fully received.

The Vendor matches the vendor # entered by the user.

The Vendor may or may not match the PO # entered by the user.

We have one indexed symbolic that concatenates the data:

Approvalstatus:(Qty <0):VendorNum:PoNum

We then use Btree extract on the file and put together at least

the approval:(qty<0) portion, that way all items ready to receive, 

all items for a certain vendor or all items for a po# sent to a 

vendor can be retrieved fairly quickly.

Note: Btree.extract does have an interesting way of handling 64K

of data. 

Brock Prusha

DFM Systems, Inc

[email protected]

View this thread on the forum...