query question

[WillieLucas]

I have always used indexing, but now I think query will be best for this situation. I want to sort on date and department when entering this one form. My questions are

1. Is there a quick query command, because this table contains about 200,000 records and growing and I don't want them sitting their waiting while the query runs.

2. Does the query update when records are added, changed, or deleted. if not do I need to rerun the query using the on timer event (that why I need the quick query).

3. When query are created or updated does it create a new $$.mdx file everytime. if so, can I delete these files when exiting but will that affect other people that are in the same form using the same query command.
Thanks
Willie

[Tom Cone Jr]

RE: query question

William, this is discussed reasonably well in the last half of chapter 3 of the User's Guide. The discussion concerning query by form and query by table applies equally to queries you script with xbasic.

Here's my take on your 3 questions:

1. Read up on Lightning Query Optimization, especially. If Alpha Five can take advantage of an existing index the query processing time is dramatically reduced. Otherwise, not.

2. No, the query list is static. Whether you need to re-run the query is up to you and the requirements of your application. Personally I would never do it that way. If immediate access is needed, I'd use an index. Otherwise I build each query when I need to use it, or re-use it.

3. Options are available to force the query list to be rebuilt. Check the discussion of tbl.query_create() in the xbasic reference manual. Also, the MPX files can be automatically deleted by Alpha Five on exit... this is a View / Setting option.

[WillieLucas]

RE: query question

Thanks Tom
Your advice is always good
Willie

[David Sander]

RE: query question

William,

Tom's advice is generally good. The query will take an increasing amount of time as the record count builds, unlike an index which is always maintained. Whenever the table is modified, the query will need to be refreshed before being used. You can probably delete the old query files that are more than a day old without any harm.
There is a good use for queries, its for those ordering and filtering needs that aren't used frequently. The idea of an index is that its frequently used and always maintained, but at some cost in CPU time and disk space. So its not good to have unused indexes on hand.

If you have an index with the correct ordering available, there is an older method called a Range which instantly selects records using an index and a start and stop point. I think you can find this process still for temporary settings but its difficult to print with it.

On larger tables, some people have used filtered indexes to speed up queries. The idea is to keep dead records out of the index so the query doesn't see them when it runs. If you specify a filter that drops records older than say 3 months, the index won't go back and drop these daily but it will drop them whenever you run a reindex command. Users will probably reindex often enough for other reasons for this to be an effective method.

Dave

[forskare]

RE: query question

David,

Tom did not promote the query over the index but rather pointed William to information about the query. William was not specific in his questions other than the sort order which is best accomplished by utilizing the form's sort order. I believe Tom is well aware of the difference between the index and query and is reflected in his 'non specific' answer.

Additionally, Tom stated he would use an index to do what William had in mind and I believe Tom's position is to use queries sparingly.

One thing I've learned about how Tom responds, sometimes he'll be very specific and other times he'll 'nudge' you in the direction to do some research. In this case, he provided a bit of both.

"Tom's advice is generally good." To me, that's, on a scale of 1-10, somwhere around 4-6. I'd say Tom's advice on this thread and overall is a 9!!! I'll take that to the bank any day!

kenn