> This thread is too interesting.. sort of brushing the basics.. here my few observations:
>
> 1.
Importance of Indexing> Recently i ran into a case where we had to deal with millions of records in dbf files. If i want to get a value from the table then i can very well run a select query to get the data.. But what i observed was that the time of response was too slow. In a production environment sometimes even a couple of seconds can be irritating..!!
>
> There can be a secondary solution to this too. What we did was, we created a compound index on the columns based on which we had to derive to a value.
>
> For Eg. if my select query were to look like
>
SELECT AVALUE FROM MYTABLE.DBF WHERE MYTABLE.B = X .AND. MYTABLE.C = "Y" .AND. MYTABLE.D = "Z"
;
>
> then i create an index as follows:
> SELECT MYTABLE.DBF
>
INDEX ON STR(B,3)+C+D TAG MYCOMPOUNDINDEX
>
> and my select query should look like this:
>
SELECT AVALUE FROM MYTABLE.DBF WHERE STR(B,3)+C+D = STR(X,3)+"Y"+"Z"
>
> This reduces my response time of the database by more than 300%. We can work on millions of data in a minute fraction of a second.
> There is a catch in this:
> Make sure that all the indexing columns are of the same data type (preferrably character)
>
> In case you need to access the table for some other query then you can create multiple compound indexes on the table and whenever you want to use the table then do a SET ORDER TO NEWTAG
>
> 2. One more common mistake that many do on tables. While working with tables make sure that you are restoring back the original filter conditions, indexes etc. Same with the work area and alias. Before opening a new table in a project, store the original alias using the ALIAS() function and the original active workarea using the SELECT() function.
> For Eg.
>
> LOCAl lcAlias, lnWorkArea
> lcAlias = ALIAS() &&Store the original table alias
> SELECT MYNEWTABLE.DBF ALIAS NEWALIAS &&Open a new table in work area
> ...
> USE &&Use the new table after you are done with it
> SELECT lcAlias &&Restore back the alias
>
> 3. It is always a good practice to use macros in .def or header files for numeric values. This way if there is a change in the value then you only need to go and change the value in the def file instead of changing all over the code.
>
> 4.
DIFFERENCE BETWEEN COUNT TO and _TALLY> In case you are using a SELECT query to get some rows to a cursor or a temporary table, and then you want to know if any row is retrieved then it is not a good practice to use COUNT TO for the purpose. _TALLY can be used instead.
>
> For Eg.
> Don't use..
>
> LOCAL lnSize
> SELECT X FROM MY TABLE WHERE TABLE.COL = "A"
> COUNT TO lnSize
> IF lnSize > 0
> ....
> ....
> ENDIF
>
> Instead use
>
> SELECT X FROM MY TABLE WHERE TABLE.COL = "A"
> IF _TALLY > 0
> ....
> ....
> ENDIF
>
> This is because COUNT TO will take your table cursor to the EOF file which you might not want. its not the case with _TALLY.
>
> 5. A common mistake among newbees.. Don't use a lot of IF ELSE statements.. instead use the DO CASE.. ENDCASE statement.
>
> 6. Keep the code presentable by keeping the code neat by proper indentation. It helps in easy reading of the code. Try to convert TABS to spaces in the EDIT --> Properties window.
>
> Hope this will be of some help.
> Will keep posting as and when i remember something.
>
> Thanks
> Sandeep
Sandeep,
When you post code, please use the Foxite code tags: < vfp > code goes here < /vfp > (without the spaces) or select your code with the mouse and then click the "code tags" button in the Foxite toolbar. (It will be located just to the left of the Bold button when creating or editing a post.) Code tags give your code syntax coloring, preserve your indent structure and generally make it easier for us old fellows to read your code. The eyes are the first things to go - or was that "The memory is the first thing to go" - can't quite remember ...
As you can see, I fixed it for you this time.
Ken
You shall know the truth - and the truth shall set you free. (John 8:33)