Index on date range help - thanks

[iKonTechDev]

Hello,

Have not been in Fox Pro for a bit but i need to change a simple deal/or create. all i need is some help with INDEX ON a date range i cant seem to get it to work.. Any help would be great thanks

Something along these lines i know i can use SQL select but i just want to display a date range in an order based open an Number.
Exp: INDEX ON Date >= ThisForm.DateFrom+Date + Date=< ThisForm.DateTo+ALLTRIM(empid) TAG MyOrder

Thanks in advance!

 

[Olaf Doschke]

You don't index such things, especially not depending on some form. You want to set a filter, perhaps. To optimize the filtering and INDEX ON Date TAG Date would suffice.

And then your filter expression should rather be along the lines of SET FILTER TO DATE>= startdate AND Date=< enddate

I don't know where you get the idea you can add dates to dates in a partial expression like ThisForm.DateFrom+Date+Date. You can add days to a date, so you can add Date+N, but this is not commutative, eg N+date isn't working.

Bye, Olaf.

 

[iKonTechDev]

Hello,

YEs your right been a bit sense i have been in this code i ended up doing this as you said..

Select HoursEmployeesB
Set Order To DateTime
Set Filter To Date >= ThisForm.DateFrom .And. Date =< ThisForm.DateTo .And. EmpID = AllTrim(ThisForm.Combo1.Value)
Goto Top

Thanks

 

[NasibKalsi]

This is simply to answer your question, such that you can do it. But follow what is said above.

* Upper case is FIELDNAME
* Limit dataset access based on DateRange
index on trans(DATEFLD >= thisform.FromDate.value and DATEFLD <= thisform.ToDate.value) + alltrim(EMPID) tag MyOrder
set key to ".T."
brow

 

[iKonTechDev]

Nice one thanks!!

 

[Mike Lewis]

I disagree with Nasib. Using THISFORM in an index is not a good idea, for three reasons. First, it means that the index will only be valid within a method of the form. You are liable to get "THISFORM can only be used within a method" if you try to open the table anywhere else.

Also, the table needs to be opened exclusively in order to create the index, which might or might not be what you want. And finally, it is time-consuming to create the index every time you run the form. It is always more efficient to create it once only, when you first create the table; it will then remain available for the life of the table.

For those reasons, yit would make more sense to use the filter, as shown in your code. However, that doesn't mean you shouldn't have indexes on one or more of the fields involved in the filter. For example, if you have an index on EmpID, you will get some partial optimisation on the filter.

That would also be true of Date - assuming Date is a field in the table. (If it is, is not a very sensible field name, as it could and will easily be confused with the DATE() function. And you seem to have an index named DateTime, which also clashes with a function name.)

Final point: You could slightly simplify your filter expression by using

[tt]BETWEEN(Date, ThisForm.DateFrom, ThisForm.DateTo)[/tt]

in place of

[tt]Date >= ThisForm.DateFrom .And. Date =< ThisForm.DateTo[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed, as I also mentioned already, THISFORM or THIS or any other dependency on objects, variables etc not coming from the table you index should never be part of an index. If at all, this should be made a temp separate IDX file, which would need the syntax [tt]INDEX ON expression TO some.IDX[/tt].

Most important: Every INDEX command will read all records and transform them according to the expression result sort order into a index tag, which technically is a tree structure with fast access to siblings and leaf nodes point to record numbers. It's needless to say alone for the reason INDEX reads ALL data, this is not helpful to optimize reading just the part of data you really want to read. An index only makes your queries run fast, when it already exists for using it. Creating it is a costly routine and rather slowing things down. With low number of records you're better off not indexing at all, if you don't comprehend that nature of indexes.

You rather only once index date and then use that index, that means you remove the INDEX ON command from your code, an INDEX stored as a tag of a CDX index file is as permanent to the table as its fields. So you spare the time to create the index everytime from creating it once, that spares much more time. So no, Nasibs code is not a nice one.

You should also have an index on EmpID to further optimize the filter.

Select HoursEmployeesB
* Set Order To Date && I named the tag Date, but whatever tag name now exist you don't need to set some order, SET FILTER will decide on it's own what index(es) to use for optimization.
Set Filter To Date >= ThisForm.DateFrom .And. Date =< ThisForm.DateTo .And. EmpID = AllTrim(ThisForm.Combo1.Value)
Browse

[tt]Date >= ThisForm.DateFrom[/tt] is optimized with Date index
[tt]Date =< ThisForm.DateTo[/tt] is optimized with Date index, too
[tt]EmpID = AllTrim(ThisForm.Combo1.Value)[/tt] is optimized with EmpID index you also need to create.

In short: Create indexes in table management/creation routines or manually in the table designer. Don't do that within application code running, indexes are permanent structural parts of your data like the fields and data itself, indexes update together with changing data without any further ado from yourself or your code. Look for any INDEX commands in your code and rather make it a comment, instead do an index once and for all. Think about atomic (single field) indexes, mainly on the most interesting fields of your table to filter, that's mainly ID fields and for date filtering also date fields. You would also index currencies for prices or loans to have fast results for a certain price or loan range, but stop creating indexes on the fly for every complex expression. Create them just once in the table designer, like you also only create the table fields once. To INDEX will mean to never read less than all data, unless you INDEX ... FOR some condition, then this index will only contain partial record numbers and it's creation itself is optimized, if it can be. You need the atomic indexes on single fields for this process too, which again hints on them being much more useful. INDEX within an application only is acceptible for creating sort order on cursors, to index views or any other data freshly creating a cursor, view cursor or table and when it's acceptible to go through all data once and use that index many times at least within the current applicaiton session. Otherwise INDEXing wastes time.

Further steps to make much better use of indexes will be going for SQL queries, which can use index combinations in a much more efficient way than FILTER or BROWSE or any FOR clause optimized workarea related xBase command can do. SQL builds up bitmaps of single/partial where conditions, then bitands/bitors all those bitmaps and thereby has a set of records being the full result to fetch, while any FOR clause is evaluated and optimized only to find the first or next row matching, not the full result set. That only has corner case better perormance, if the result set is huge and positioning on the first result row is much faster than fetching the full result set.

BROWSEing a filtered or indexed table you also only get the impression you are faster than SQL, because unless a result set is small finding a first result row or the few first result rows displayed in a browse always is faster and thus seems to be faster overall, while it just postpones fetching further result rows. With tables growing to more than 10000 rows scrolling through a filtered workarea or through a key value can get sluggish because with each scroll down or up you trigger another portion of the postponed work to find next/previous rows.

The transition is a lot of work, but for the start you better index your tables in some single fields, then use SET FILTER or BROWSE FOR to get your results, even though that's not ideal, it's most probably fitting your usage of INDEX to limit shown records. VFP will keep the CDX file indexes current, which are used for the FOR/Filter condition optimization at least, no need to INDEX again and again. You then can aim for better performance in sections your application is slowing down with more data and go SQL, unless you are at a very old legacy foxpro version not at all allowing SQL.

Bye, Olaf.

 

[NasibKalsi]

Mike -> "I disagree with Nasib. Using THISFORM in an index is not a good idea"

But same is true for filter. In IkonTechDev' situation using 'index' or 'filter' will have the same scope limitations.

There is extra cost on recreating his logic to accomodate the suggested changes. I guess IkonTechDev now have enough information from this thread to complete the task.

 

[Mike Lewis]

But same is true for filter. In IkonTechDev' situation using 'index' or 'filter' will have the same scope limitations.

I take your point, Nasib. But my point is that an index is persistent. Once you create it, it stays created. So he creates his index using THISFORM. What would then happen when he tries to open the table somewhere else? Or even drill down into it in the Project Manager?

For that matter, I'm not even sure that VFP would allow him to create the index in the first place.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I second Mike, the difference is a FILTER isn't permanent and a filter can make use of indexes. If you go about such special indexes, you will have an overflow of the CDX file besides the limitation of only being able to use certain index tags on certain contexts, i.e. on the current form only. The usage of Thisform can be mended in both cases, the more important point is, that CDX should not be misused and flodded with index tags, it also has a 2GB limit and it's not for temporary use, while a filter is just a temporary workarea property.

Bye, Olaf.