CSDA Tips on
Alpha Five &
Product Tips

 Alpha Software VAR logo
Last Updated 08 April 2010

Any database program, Alpha Five included, is very complex in what it does and how it does it. Database programs push the hardware, software and networks much harder than almost every other typical programs people use, like Microsoft Word, Excel or browsers. So, while developing in Alpha Five is typically much faster, there are still things that are very useful to know when developing a database program so that it runs efficiently, with few errors, and is easy to maintain.

Below are tips on a variety of topics (many of which apply to any database) that you should be aware of as you develop an application in Alpha Five.  Re-read each tip very carefully for it's meaning and guidelines. And if this is just TMI (Too Much Information) for you to absorb, we provide consulting, mentoring and training to help you through these issues.
If any of these tips are particularly useful to you, or make a big difference in your Alpha Five application, or you believe any aspect of what is stated is incorrect, we'd love to hear your comments!
Just email us at support@csda1.com


CSDA Code Utility specific tips
  1. Speed up starting an A5 Desktop with a Database shortcut
  2. Menus are too complicated or too large
  3. Customize Menus for a specific database
  4. Restoring messed up button files
  5. Lost customized button
  6. Accurate Timing of code
  7. Quick Backup to send files
  8. Modeless Interactive Window
Alpha Five General Tips & Information
The Short List - (What to do without the details!) Updated 23 Dec 2009

Otherwise, click below:
  1. Use functions, Not scripts
  2. Choosing the right & fast function
  3. Variable usage, dimensioning and deletion Updated 30 Mar 2010
  4. Field Rule Recalcs
  5. Delimiter characters for CSV Updated 26 Dec 2009
  6. Files over 2 Gigabytes
  7. Why Network Optimization (shadowing) does not necessarily improve speed
  8. Lightning Query Optimization (LQO)
  9. Record Locking 101 (and how to speed up Reports/Appends/Updates)
  10. Coding with XBasic versus Action Scripts Updated 21 Mar 2010
  11. AEX Compiled libraries Library Numbers
  12. Coding Tips Updated 07 Mar 2010
  13. Isolate Code that depends on tricks Updated 03 Jan 2010
  14. Using Active-X objects tips Updated 27 Feb 2010
  15. Bitwise Logical operations (Binary) with numbers Updated 08 Apr 2010
Links to Other Alpha Five Tips
  1. AIMS Web site - Alpha Five naming conventions
  2. AlphaToGo - various Alpha Five Articles
  3. LearnAlpha.com - various Alpha Five articles
  4. Proctor & Peake - various Alpha Five tips
  5. Marcel Kollenaar's tips on using DLL's with Alpha Five
  6. Dan Blank's Tips
  7. QODBC - QuickBook interface to Alpha Five tip

CSDA Code Utility Tips


To speed up starting, create a Desktop Restore shortcut for each specific database you use.
When you start with the A5vX Desktop Restore, or the A5vX Last Desktop restore desktop icon shortcuts, it starts a special database and then starts the actual database. Just start your database, then select the CSDA Code Utility "Create Shortcut"  button.  Press OK, and a new A5 Desktop Restore specific to the database will be placed on your desktop.
Menus are too complicated or too large or have features I never use 
Simply edit the dialog box menu for the appropriate dialog type (Rolldown, Rollup, Minimize, Horizontal dock or Vertical dock) and remove buttons you want.  Do this by pressing the CSDA Code Utility "Help"  button.  At the bottom are various edit buttons for the dialog boxes.  Just press one you want to edit, scroll down to the button lines (near the bottom) and "comment out" any button you don't want to use by simply putting a single quote at the beginning of the line to eliminate the button.  If you made a mistake, removing the single quote will restore the button.  After you made desired changes save the file and exit.  Then use any utility's restart buttons to restart the utility with the new menus.

In a similar fashion you can also add buttons (both predefined and ones that run your own code) to the menus.  Just read the comments at the top of the button files and copy similar buttons as a guide and modify.
Can I customize Menus for a specific database? 
Yes, you can.  This is sometimes referred to as "skinning".  Simply select the "Edit A5 Buttons Loc" on the Utility's help button, and when done, save to the database directory as a file name "databasename.abt"  (Be careful, as Notepad will try to save it to "databasename.abt.txt".  Rename it if this happens).  This file has lines that point to where the various other button files are located, and they could be anywhere in the system, but typically should be in the default Alpha Five "application" folder, or the database directory.
Restoring Button files
If you have a problem with the CSDA Code Utility button files that are not operating properly after changing them, you can restore the original ones by either
Lost customized button
You lost your customized button code for the CSDA Code Utility after reinstalling or updating the utility.   Because the button files are subject to change from version to version (although they haven't in a long time), the new versions always use the current default definitions.  However, all of your old button files are renamed with a date at the end of the file name.  

You can either edit the new ones copying in your older changes, copy the old ones to the newer ones (with the risk of a format change), or use a button Location file skin to redirect it to some custom named files that will not be overwritten.
Quick Backup to send files
When you need to send database files to others (or post on the message board), here is a quick way with the CSDA Code Utility:
This will copy your database, dictionaries and web components, compact (Pack) them, and then zip them together into a file of your choosing.
Accurate Timing of code
To test code speed, using the CSDA Code Utility, select a code editor tab and press the Code timing button and that function or script speed will be tested.  It is generally best not to have any dialog boxes pop up in the code that require user responses, as the user response time will be included in the timing results.  

If you are on the Interactive Window (IW) tab, just highlight (select) some code, and press the Code timing button, and that code will be timed.  It is often best to initialize all needed variables in the IW, and then test the key code separately.  

Results may vary up to about 3%, depending upon what windows and Alpha is doing at the same time.  If you run the tests at different times where the Windows environment and available memory has changed, your results may vary Running different tests right after each other yields a more accurate comparative timing value.
Modeless Interactive Window
Sometimes you need an interactive window in a modeless dialog, that you can run code without changing the context of the primary window.  By using the Code Edit button to edit any existing code, you get a modeless script editor and an interactive window mode on the 2nd tab.  This allows you to probe variables and run test code without changing the context of the other windows.
Alpha Five General Tips & Info

The Short List (What to do without the details!)  (Email Comments) Updated 23 Dec 2009
Use a function, not a script  (Email Comments) Updated 23 Dec 2009
When you create code in Alpha, particularly using Alpha Five Genies, or Action Scripts, you are creating code as a script.  It may be embedded on a form, or available as a Global Script, but the result is the same.  

Move the code off the form (layouts)
The 1st point is to change your code to a global script or function, rather than embedding it on a form (or other layout - I'll refer to them all as forms for this tip).  

When you place the code directly on the form, you typically have access to all the variables and fields available on the form.  In other words it typically "inherits" the variable space of the object it is embedded in.  In addition, "parentform"  is an alias to the current form's name.  If you change the name of the form and use the alias as a pointer in the code, the code still works. The code is being executed in the context of the form, so has access to all of those properties, as well as all variables that the form has access to.

However, if you move the code off the form and make it a global script or function, you may lose the context of the form (which can be totally eliminated by passing the form's pointer).  Recent testing in A5V5 and up seems that this is not the case.  Parentform and Parent do seem to work in the context of the function. However local variables on the form are accessible only if you pass the pointer to the function, or retrieve the form's local variable space by using the parentform or parent alias to retrieve the pointer to the form's local variable space.

You get these advantages with Global Scripts & Functions;
Using Script_Play_Local("script_name") allows the global script code to inherit the space from where it is used, so all local variables of the form are also available (and changeable) within the script, which simplifies usage for beginners.

But this can be also be bad -- VERY BAD!  Even if you use SCRIPT_PLAY("script_name"), you still have access to other global or shared level variable names that have been defined by Alpha, or yourself, that you did not know were being used. This can cause your's or Alpha's code portions to fail, because of different uses of the same name. This makes debugging a problem very hard, if not close to impossible.  (Note: Anything listed here is just one example.  The problem exists everywhere, 100's of cases could be cited, and can fail even with you own code, if you don't dimension as suggested.)

Don't believe it?  Try this in the interactive window!
(A5v6 through A5v10's December 21,  2009 build).
- just one simple example out of 100's that are known)
dim shared woy as n
woy=-5
?woy
= -5
?week(date())
= 16
?woy
= 16
Obviously, Alpha Five's code does not dimension the "woy" variable name. Here's another example (I use the "delete woy" to clear any previous usage here for illustration)
delete woy
dim shared woy as L
woy=.t.
?woy
= .T.
?week(date())
ERROR: Variable type mismatch

Use functions, Not scripts!
I've seen people use 100's of scripts to accomplish the same as 1 simple function could do. The problem with the function is that, in general, if you don't use a passed pointer (e.g. local_variables() etc.), the function does not know the context of the code that called it.  No local variables from the calling code (where the function is referenced) are available.

Normally this is good for debugging, and the context is not needed. If it does need a context, e.g. it prints a report of an ID that is in the current layout, then you can pass the ID value as a parameter. If you need many variables, you can pass a pointer to the local variables, a pointer to the layout, or to just your own pointer with lots of property values.

To help isolate name usage, change the script code to a global function. A global function can be created as simple as

FUNCTION script_name as V ( )
' original script code here
END FUNCTION

Then just use script_name() instead of SCRIPT_PLAY("script_name") wherever you need that code to be placed.  Also, make sure each and every variable used is dimensioned (prior to the code's 1st use) with DIM (or is in the function parameter definition line).  See this tip for a discussion of variable dimensioning.

If you do need the context of the form (layout), in a function, pass a pointer to the context a parameter, such as

FUNCTION script_name as V (layoutpointer as P)

' in the use of the function, pass the layout pointer, as in
'    script_name(parentform.this)

with layoutpointer
    ' original script code here, has access and can change all of layout's variables
    ' (which can be bad, so use this method less)
end with

' Or prefix layout' variable name with pointer  (better method)
variablename=layoutpointer.layouts_variable_name


END FUNCTION

However, the function templates shown above are really not the best way to use a function, but will allow you to quickly move your script code into functions.  The next step is eliminate passing the local_variables() pointer, and instead pass just the parameters you need, combined with a returned value.  Here is an example;

FUNCTION SumNumbers as N (Number1 as N, Number2 as N)

' To use this function, use a line similar to
'    sum_of_the_numbers = SumNumbers(1.5, 3.4)
' or put any valid numeric value or numeric expression in the parameters, such as
'    sum_of_the_numbers = SumNumbers(numeric_variable1 *1.5, numeric_variable2)

' All of these are dimensioned in the function definition line, so they are already DIM'ed
SumNumbers = Number1 + Number2

END FUNCTION

The key advantages to a Function over a script are
mileage=Global_Distance(zipcode_1,zipcode_2)

simplifying the actual flow of code and yielding greater comprehension of what the code is doing.  What the function does inside may be complex, but using it is simple. 

My development library has over 1000 finished functions I have developed over time for many purposes, and exactly 1 finished script (the AUTOEXEC script).  'Nuff said?  :?)

Choosing the right Alpha Five Function  (Email Comments)
Alpha Five has over 10,000 functions and methods.  Methods are basically functions associated with an object like forms, browses, email etc, e.g. form.view(form_name), and can be considered to be a function.   How do you choose one function (or method) over another?  The main criteria are
  1. It has some capability that another one doesn't have, or
  2. It is clearer as to what you are trying to do so that others viewing the code (including yourself 6 months down the road) could understand it, or
  3. It is more modifiable for future changes, or
  4. It is faster
All of the above are important in selection of a function, but the one that you can have the most control of is speed. No matter how big databases are, they will grow over time to a point where speed will become important. No one usually cares if something takes 2 sec or 5 sec, and sometimes 1 min or 5 minutes, but running a report/operation/etc. that takes 10 minutes vs 1 hour is significant (especially if you need an update every 15 minutes!)   (Note: No matter how long something takes, once it exceeds a second or 2, you should display the status or at least a warning that the process will take a time to complete)

In terms of speed, Alpha Five functions (and methods) seem to fall into 4 basic groups
  1. Key Functions - Provided by Alpha, written in C or those that are in optimized function libraries that run very fast for what they do
  2. Key Xbasic Functions - Those written in XBasic code, but the majority of the operational code's time is spent using those functions of #1. They are typically slower than those written using techniques of #1 and could be user-written. These could also include XBasic wrapper code (See #4 below for definition) calling  Windows API, OLE or Active X functions
  3. Xbasic functions - Those written in XBasic, where most of the time is spent in Xbasic code - typically much slower than #1 or #2, could be user-written
  4. Wrapper functions - These are XBasic functions that call other functions of type 1, 2 or 3 and just rearrange or simplify the input parameters and then return values from a function for easier use
How can you tell the difference?

One way is to use the CSDA Code Utility's accurate timer to test samples of code or entire functions and scripts. This utility basically loops code many times to arrive at an accurate timing value.

Those in #1 or #2 are generally faster by a factor of 3 to 10 than the equivalent in #3 and #4. Type #4 tends to be just a little slower than the equivalent of type #3.

Also, those in type #1 and #2 typically have simpler types of parameters and inputs than those of #3 and #4.  Also they may have less error checking of parameter values (e.g. a numeric parameter less than 0 may fail) and less error error trapping than #3 or #4.

Occasionally, Alpha Software changes a function of type #3 or #4 to a type #1 because they need it for internal uses or because enough developers seem to need that speed, so newer versions and releases may be faster. Re-testing is sometimes required to ascertain this.  For code that operates on multi-Alpha Five versions, you can occasionally benefit by using version dependent code.

So in general, stick to types #1 and #2 if it makes no difference. Use type #3 and #4 if speed is not important.  If you need a type #3 or #4 and speed is important, either
  1. Write an equivalent function for your use that might be faster (I've done this many times), or
  2. Create a function specifically tailored to the usage without any bells and whistles (features), or
  3. Embed the equivalent code in each and every place you need to use that code that needs the speed.
The last affects readability, but can speed things quite a bit, particularly where the function's main code execution approaches the speed of the overhead of the function call.

A new feature (Called Code Count) to be released in an upcoming CSDA Code Utility update will do counts of various scripts and function usages in your databases, and will indicate if the code is user XBasic, Alpha Five XBasic or Alpha Five internal code, and whether there is any documentation on that function, a useful starting point.
Variable Usage, Dimensioning & Deletion  (Email Comments) Updated 30 Mar 2010

Alpha Software's webhelp also has some additional information here: Variable Scope

Basic guideline rules for variable usage are: Dimensioning styles
There are 2 styles typically used for dimensioning variables.  They are
Dimension as Local
If a variable is not DIM'd, and you don't reference an explicit variable space (addin, global, shared and local), the current most limited space that is DIM'd is used for references and assignments.

If not DIM'd at any level, then there is implicit DIM as a local variable (or at the space as defined by the WITH/END WITH command directive) upon 1st use.  

Note that as of Jan 1, 2010 an explicit "local" DIM, as in "DIM LOCAL varname AS type" does not work. So to dimension at the local variable space (or at the space as defined by the WITH/END WITH command directive), you must do a "DIM varname AS type".

In a similar way DELETE LOCAL varname does nothing, but DELETE varname deletes the lowest level space defined

DIM GLOBAL, DIM SHARED, DELETE GLOBAL and DELETE SHARED do work though

If you use a property pointer, it will use the scope of that pointer.  So you could force a local DIM or delete by doing something similar to (substituting the variable name for varname and the type below);
dim localptr as P
localptr=local_variables()
DIM localptr.varname as type        ' for each DIM needed at the local level
DELETE localptr.varname              ' for each DELETE needed at the local level
   
You could do similar things at other levels with
Cautions using SCRIPT_PLAY_LOCAL()
Dimensioning variables as local helps eliminate un-intended side effects, however, if you create scripts (action or XBasic) and use SCRIPT_PLAY_LOCAL() to execute it, it operates in the same variable space as the place it was invoked from.

For your code, you know what variables you are using and can avoid naming reuse.

However, Alpha Five uses many cases of SCRIPT_PLAY_LOCAL() in it's code generated by Genies and Action Scripts, so there is a potential (although relatively small chance) of their being a conflict of names between what you use and what Alpha generates at the local level.

Xdialog Variable Spaces (Updated 30 Mar 2010)
XDialogs inherit the local variable space from the code they were started from.  This means any variables dimensioned locally prior to starting the XDialog will be available in the XDialog.  And of course, as before, any references to variables that are not found dimensioned locally, will be checked in the shared variables and then the global variables.

Some of the controls in XDialogs, e.g. ListView objects, use an Active X Control (another program essentially).  They are implemented in ways that require some variables to be dimensioned at the shared (session) level, as those Listview events are running in a different variable space and must have access and set some values from the Xdialog variable spaces.

Minimize Variable Name collisions with code not your own
If you need to prevent conflicts (collisions) with creation and deletion of variables with the same name at different scopes (particularly at the shared, global or addin scopes), by code that is not your own (e.g. Alpha Five built-in functions, code generated by action scripts and genies) create a uniquely named variable space pointer (possibly with a GUID or time stamp in the name), and reasonably long to be unique to a maximum length of 23 characters (Alpha can use 24, but has had some places that only saw the 1st 23) and then use that pointer to create property values (variables) for all the ones required at that scope.

That's what is done in the CSDA Code Utility. Version 2.69 and after, creates 1 global and 1 addin pointers and no other top level variables in the entire program. The name is chosen to be long and not common to eliminate possible conflicts with other programs.  Many variables that are needed by many aspects of the program are stored under those pointers as properties.

Why you shouldn't delete a variable, nor have to
The reason for not deleting a variable, and for that matter using a variable from any other session (also referenced as the words process or thread in Alpha Five - a layout like a form or browse always runs in it's own session), is that it has an independent purpose and timing.

In most cases, when you create a variable, you intend on using it and for that particular variable type. Its use should have a determined purpose, and does not need to be redefined to another type. It would be better to create another variable of the type needed, e.g. tmpC, tmpN, tmpL etc. for Character, Numeric, Logical etc.

E.g. two forms create a global variable "tmp". One set's it to "text", the other deletes it and sets it to the number 2. This will cause an unintended side effect (read as failure) in the other form. Just changing the value alone will cause the other form to fail what it was doing, unless you intend it for use as an inter-thread communications.  In that case usage must done in a way that allows for proper operation, and that one process does not receive a partial message (A whole other subject to discuss!)

When a session finishes, all the variables at the session (shared) scope and local scope are released (deleted).  Similarly, when a function or normal script terminates, the local variables for it are automatically deleted.  

For Global or Addin variables, they persist.  Global variables remain until you close the database, Addin variables remain until you close Alpha Five (even if you open another database).  If you must delete the global or addin variable, try to do it in the same session that created it (and the same applies to variable writes, although it is generally OK to read from elsewhere).  If not, try to make sure, by program design, any other session that writes or deletes it, is not executing at the same time.

If you need to change variable types, e.g Character and Numeric, the only real place I've seen a true need for this, is in a function's return value where the return value is type A (can be any type).
E.g. MAX() function works with numbers, characters, dates, and the return value is the same type as the inputs.
You might start with an initial value of -1 to indicate error, but for some cases, you might be returning other types. If the function code works in a way that you only need to set the return variable once for any particular exit, then you can set it for the 1st time for that exit path, and not have to delete the previous usage at all, as there wouldn't be any

Issues with Action scripts using DELETE
Alpha Five action scripts don't assume any knowledge of other code that's been used previously (including the same action script action used on another line) and thus deletes the variable to make sure there is no conflict. This carries the risk of deleting another variable at a more "global" scope that might have an identical name (unlikely for Alpha script naming, but it could happen) and being currently run.  It could also delete a variable that you named that had the same name.  Making sure your code dimensions everything as local eliminates this issue.  If a variable can not be dimensioned as local, make sure it has a unique name.

Special Cases for using DELETE
You might be able to come up with some code examples that do need to delete variables, but those truly requiring it are going to be very unique in their purpose.

One example of this is the Code operations of the CSDA Code Utility. This has the ability to run other people's code for testing purposes and can't know what the potential return values types are going to be. Thus, there is exactly 1 delete of a variable in the code. And since it's a "local" variable, the code that created it is the same as the one that is deleting it, a safe operation.

Referencing Variables at other levels
Even if variables with the same name are defined at more than 1 level, you can reference these higher scoped variables, and test for their presence.

' They can also be referenced by creating a pointer
dim localptr as p
dim shareptr as p
dim globalptr as p

localptr=local_variables()
shareptr=session_variables()
globalptr=global_variables()

value1=localptr.varname
value2=shareptr.varname
value3=globalptr.varname

' They can also be referenced directly by prefixing the function on the property name
' This will not work with A5 directives (commands) like DIM, DELETE
value1=local_variables().varname
value2=session_variables().varname
value3=global_variables().varname

If you want to test for a scope of a variable, these examples will return logical values
logicalvalue1=eval_valid("global_variables().varname")
logicalvalue2=eval_valid("session_variables().varname")
logicalvalue3=eval_valid("local_variables().varname")
Don't use Field Rule recalcs  (Email Comments)
The recalc of field rules applies to all field rules, not just specific ones of interest. It's an all or nothing proposition (for the most part).

Suppose you have a field rule with a field with a default value of DATE() holding the record create date  (some of these might be blank from records imported). Another field has a calculated field of DATE() holding the date record was last modified (by a human, not a program).

If you do a recalc of the field rules, the blank create dates might be set to the current date, and the last modified date will be changed to the current date, not what was necessarily intended.

The field rules are intended as a set of rules to be applied while entering and changing data by human input (exception probably being auto-increment). The rules are applied to the record based upon the current values of the database at the time of entry or change.

If you have any field rule that depends upon date or time, or the current state of other records at the time, then a recalc of field rules totally invalidates the conditions that they were when they were created originally.

If you must perform an operation like the field rules, then run a global update or other operations that take in consideration of what must be done to get the correct results.

In the above example you, would not global update the create date, and may or may not global update the last modified date, depending upon intended results.

Also, using variables in a field rule is generally a bad idea.  If the variable is undefined or uninitialized, the field rule will not work.  The typical way to initialize a variable is via an Autoexec script or in a form or browse layout's initialization event code.  This does not happen if you open the table without the autoexec, or open a form (e.g. the default form) where there is no initialization event code.

So, to sum up, If you look at the descriptions of the general method and table method of recalc and this link, it is highly probably that they are using one of those functions to perform the recalc. Based upon the documentation of the link, it implies a wide variety of effects besides calc fields. Without testing intensively, it might be hard to prove that it doesn't affect other rules, although proving it does affect other rules means finding just 1 or more, which might come out in testing quite quickly.

Based upon the documentation of the functions and my link and that there seem to be no other functions applicable, I am guessing that recalc of calc fields is really recalc of field rules. This also agrees with historical history of the recalc process as well.


Delimiter characters for CSV (character separated values)  (Email Comments) Updated 26 Dec 2009
Normal choices for CSV lists are comma, Carriage Return/Line Feed, or Tabs.   Vertical bar ("|"), space, colon and semi-colons are also used often.  But whatever character (or character string) you choose, there is a chance the user could have text with that character.

But since Alpha can have any value from ascii 1 to ascii 255 (chr(1) -> chr(255)) in a text string, use a chr(x) as the delimiter, where x is a value between 1 and 31 with the exceptions of 7 (beep), 8 (backspace), 9 (tab), 10 (line feed), 12 (Form Feed), 13 (Carriage return), and 27 (escape). Alpha Five also uses values 1 (Start of Header), 2 (Start of Text), and 11 (Vertical Tab) in some of their code, so avoid these as well.  The value 28 is a good choice, as it is not common to find a binary "00011100" in text or binary files.  In most cases, set a variable name, such as chr28 to the value, as in chr28=CHR(28) or field_separator=chr(28)
What to do if your database files are 2 Gigabytes or larger  (Email Comments)
If you have a database data file (*.dbf) or memo field (*.fpt) that is approaching the 2 gigabyte maximum allowed size for these formats, you must get all files under the 2 Gigabyte limit.

The solution is to segment each memo field of the table into separate tables with links back to the main table holding all the other non-memo fields. If you are exceeding 2 Gigabytes with the main dbf file, then separate fields into more than 1 table with a link.

Alternatively, use an SQL database if you are able to.
Why Network Optimization (shadowing) does not necessarily improve speed  (Email Comments)
Network Optimization makes a copy of all layouts (such as forms, browses, reports), operations (like exports) and user code to a faster location (typically a local drive) in attempt to minimize network traffic of these.  Data is still accessed from the "server".  

Only if more than one system's access of layouts, operations or code interferes with the network traffic of another, does the network optimization become useful from a global network performance of all systems.  Alpha Five caches the code to some degree, so any local increase in speed is seen only for those applications that open forms, browses and other layouts repeatedly. Operations and code entries seldom cause a cache miss, and thus their reloading does not happen often during normal (not-development) operation.
Lightning Query Optimization (LQO)  (Email Comments)

Lightning Query Optimization (LQO) is a process of Alpha Five that happens automatically whenever you apply any filter or query to a table (except for the filter inside an index definition). It speeds up query time by creating a dynamic range to an index.

Back in Alpha Four, (and actually is still doable in Alpha Five), you might do a search (query) by setting a range that reduced the number of records that needed to be searched.

E.g. If you wanted only people whose last name began with S, your range for an index whose expression was LastName+FirstName would be "S" through "Sz". Alpha would only access those "S" records.

In order to be more generic than a range, LQO takes a part of the expression of the filter (e.g Between(LastName,"S","Sz) and looks to see if there is an index that uses the field of that part of the expression at the beginning of the index expression. It then tries to set a range that restricts the range of records that need to be accessed, and then applies the filter to only that record. So if your filter expression was

(FirstName="John") .and. Between(LastName,"S","Sz)

then ideally it would search the records only starting with "S".

Unfortunately, LQO isn't super intelligent. You need to help it a bit to find a matching index. It is best that the beginning part of the filter expression that you want to match to an index rearrange the filter terms to either start with a matching equality (or >= or <=) statement or use a Between() with constants for the 2nd and 3rd arguments, as in

Between(LastName,"S","Sz) .and. (FirstName="John")

or

LastName="Smith" .and. FirstName="John"

So for a last name search, Alpha Five would be just as happy with index expressions of

LastName
LastName+FirstName
LastName+FirstName+Zipcode

It may not work with things like

trim(lastname)+trim(firstname)

LQO may also not work with query expressions that use expressions that are complicated inside a between() like

between(trim(lastname),"S","Sz")
between(lastname,BeginValueVariable,EndValueVariable)

But it may work with

between(lastname+firstname,"S","Sz")

Different versions of Alpha Five may be better than others in using LQO for more complicated expressions, but in generally, make sure it happens by keeping the expressions simple.

LQO will not help (and could slow it down a bit) if the range of selected records is a large percentage of total records. This is not typical, but happens if you create filter and index expressions incorrectly or reference a large portion of the table.

When you get the LQO right, your speed of accessing records for large tables will increase a lot. That's the only way to really tell if LQO is happening (unfortunately it would be nice if Alpha had an LQO evaluator which would show you when it would actually happen)
Record Locking 101  (Email Comments)

In databases that are not "True" client-server databases, which includes Alpha Five's DBF file format, as well as Filemaker Pro, and Microsoft's Access (typical usage) and many others, there is a process that happens with each access of data that is referred to as "Locking".  Since each client computer is actually writing to a portion of a shared file someplace on a network, this needs to occur so that data is not used that is only partially changed.  

Imagine that data was in the process of being written to the shared file and you read the data at a point when only part of the data had been written.  This would give you a mix of the old and new data.

To prevent this, writing and reading of the data must be performed in "Atomic" operations (one that can not be interrupted during the process). This includes parent records and every child record that is linked with referential integrity (referential integrity does not actually require this child locking, but is the way that it is implemented in Alpha Five). Locks are performed by the operating system, so each one has an associated time that it takes that can add up.  Since it is atomic, every access by other users and processes must wait while this happens.  This atomic operation generally goes something like this;
  1. Request and wait for a file lock on all relevant data files of the database (typically, data, indexes, memo).  Some allow you to cancel this step if waiting
  2. After receiving a file lock, request a record lock for each of those files for the needed records. There can be read record locks and write record locks for the respective needs, or they may be identical depending on the operating system.  Reads are typically faster than write locks, as the writes have to read the original sector(s) on the server hard drive, modify the data on the record part, and then write back the sectors.
  3. Release the file lock while keeping the record lock.
The record data can now be used or changed.  When done working with the specific records, the reverse process must be done to release the locks.  For a read, the data is generally read quickly and released.  

For a write, it depends on the database program.  Some release it after a quick read, then later repeat the process to write the final changed data.  If more than 1 process tries to write the same data, the last one written generally is one.  Others, such as Alpha Five, hold the write lock on the record forever until the changed data is saved.

The release process goes something like this;
  1. Request and wait for a file lock on all relevant data files of the database (typically, data, indexes, memo).  Some allow you to cancel this step if waiting
  2. After receiving a file lock, release the record locks. 
  3. Release the file locks
These atomic locks and releases are sent by the operating system to the computer where the data is stored.  It is fastest if it's the local drive (microseconds), a bit longer on a Local Area Network (LAN), and can be seconds over a Wide Area Network (WAN) (such as the Internet).  

Usage on a WAN will slow a database to being unusable.  However, there is a solution. Using a remote program like PCAnywhere, VNC, or a Citrix server, they move the programs operation to the LAN or local computer, and essentially just send an image and allow control of the program remotely over the Internet.

In some cases, the database program may actually hold the file locks for multiple accesses of record data, say 50 at a time.  While the file lock is asserted, anything can be done.  Then releasing the file lock so other processes can use it.  This might be done for generating a report, or appending data etc. to lessen the impact of many record accesses, as the lock process has a fairly high overhead compared to reading and writing records.  Some database programs may allow you to explicitly control this to "Batch" process data, however, Alpha Five does not.  In Alpha Five, you can open tables with exclusive use if no one else is using it, but this would cause other processes to fail that tried to share the data.

In SQL and other True client server databases, the same lock processes basically happens, except now 1 computer controls the entire database. No file locks or file unlocks are needed, and the computer manages what amounts to the equivalent of a record lock.  Any requests to read and write data go to the server, who honors the request to the data and sends back the data read, or updates the database with the new data.  This gets rid of the time required to get and release locks over a network, speeding access to the data. However this comes at the cost of requiring more work for the server, that was previously being done at each computer. The server can allow multiple workstations to open the same record for change, and when the data is presented back, can manage which of the workstations' data will be accepted for changing, and/or flag differences between the multiple data being written, and manage those differences.

There is one other advantage of a True client server database.   In a non-True client server database if a workstation crashes during the lock/unlock process, it can stop every computer using the database.  If it crashes while a record write lock is present, no one can change that record.  In most cases, these locks are released after the computer is rebooted, and in some systems (but not Alpha Five) may have some automatic time-out.  But in a True client server database, the workstation crashing just loses the data it was working with, and the server continues normal operation.

Alpha Five specific locking info tips
Disclaimer:  Many of the items listed below could change between versions (or even patched updates) of Alpha Five.  They are guidelines and should be verified for your usage.

Normally, for most normal record accesses in a shared environment, A5 locks (arbitrate might be a better word here) records (and indexes) on a record-by-record basis. If a record change causes a change to a record, or when a new record is entered, A5 performs a very short indivisible reading/writing of the tables and indexes that for that short time (typically milliseconds on a LAN), you effectively have exclusive control of the database "files" (tables/indexes), but not of the individual records locked by other users/processes changing another record.

If the table is being used by another process/user, you can perform all the normal single record type XBasic commands.

Same thing is true for the more global operations like append, copy, crosstab, export, intersect, join, post, subtract, summarize and update. The most interesting cases most would use are the append and update. In these, the table's indexes are potentially modified.

Shared vs Sole Use during Appends and Updates
A5 has 2 different modes of operation for this kind of case. If the table is in use by another, it will update the index as each record is modified. No problem sharing here.

The 2nd way, if it determines it has sole use (still could be in share mode though), will do the operation and then rebuild the indexes. This temporarily locks (for what could be a long time, mind you) the tables from all other users. They will get a locking error with an automatic retry. As soon as A5 finishes the re-index (and it only rebuilds indexes that use fields that have been modified in the append or update), the file locks are released and the other users go on their merry (although a little miffed for someone locking them out for potentially a long time!) way.

Why do it this way you say? If the majority of records are going to be modified, it is much faster to update the indexes at the end of the operation.  This can backfire when the number of changed records is small compared to the total size of the table. Assuming you are the sole user, where the break even point is, I've never actually tested, but I suspect it's something about where you change about 40% of the records.

To force Appending/Updating to use the shared method, you can not count on other users also using the table.  To force this, open the table in a different session.  It is easiest to just open a form (which can be hidden) while doing the operation, and close the form afterwards.

Some code that does it

frm=form.load("tablename")
update.run_silent("updateoperationname")
frm.close()

Tests on a 35000 record file had an update take about 11 seconds in the above method, versus 19 seconds for the index rebuilds happening at the end (index expressions are very complex for this table) when the table is available exclusively.

Measuring code speed for short time events is difficult, and the profiler functions of A5 can give wildly inaccurate values to the execution speed differences in many cases. The only way to get an accurate time result is to run short time code pieces many times over, and make sure the loop overhead is not significant. The second part can be difficult to do.

The accurate speed timing button of the CSDA Code Utility for Alpha Five does just that.

Is the overhead of an Update operation slower than parsing through the records?
For large numbers of records where either the index rebuilds don't take long, or using the method shown above to update indexes on the fly, normally the answer is yes. For under 10 records, it is probably faster with code, with 10 to 100 records probably being the cross over point. It's even possible to use both methods, depending upon # of records to process, if the value changes dynamically.

Force LQO where possible
Depending upon your options selected in operations, the 1st thing it might do is perform a query of the source or destination tables. Make sure your filters will invoke LQO (Lightning Query Optimization), otherwise you will be doing a slow query if the query results do not include almost every record of the table.  Each read of the record for a query requires record locks.  Accessing less records (by using LQO) will reduce locking/unlocking requests on the network.

Open and Close of Tables
The Opening and closing of a table, either explicitly by code, or implied by the code being executed is a fairly long process over a network. (typically 25 milliseconds).  For some code, e.g lookupc(), lookupn() and similar, they actually are much faster if the table is already open (at least in more recent versions of A5).  This implies that they are using an already opened table, to save time.  If you are performing a lot of these operations, it is best to open the table before the lookups, and close after all are done.

To check which functions might use an already opened table, try the code with a table open, and without, and measure the speed.

Of course, you should always close tables the code has opened on the way out for cleanliness.

Report Locking
Alpha Five locks reports for the whole process while generating the report, unless you take specific steps to not have this happen.  A report that either doesn't uncheck lock table on the report properties, or uses summary values that are not of the running type, or has widows and orphan turned on anywhere will lock the table, sometimes for a relatively long time.  Having the Lock tables checked in the report properties (and this is the default), using summary values that are not running values, placing summary values at tops of groups as opposed to bottom, sorting/breaking groups differently from the sort of the entire report, using widows or orphans, using page x of y, are just a few things that slow the application down for everyone, by locking the table(s).

A report needs to be locked when it is required to make multiple passes through the table for calculations that summarize items.   It guarantees that a pass 1 though the data to get some values needed to format the report is identical to pass 2 where it actually creates the report.  Some of these double passes go all the way to the end, some just to the end of the current page or group.  

E.g. to compute percentages, you need to know the total 1st.  Let's say you don't lock the table. You pre-process the total of some number. Then as you process each record's value, you divide by that total times 100 to get the percentage. If some record that is part of the total is changed, or a new record is added, the actual total might change, but the total number that A5 computed will not change. It's possible you could have percentages totaling more or less than 100%

Instead, use calculated fields (on reports) that use functions like (which I'll call running summary values)
RUN_TOTAL, RUN_AVERAGE, RUN_COUNT, RUN_MAXIMUM, RUN_MINIMUM, RUN_STDDEV, RUN_VARIANCE
instead of
TOTAL, AVERAGE, COUNT, MAXIMUM, MINIMUM, STDDEV, VARIANCE
PAGE_TOTAL, PAGE_AVERAGE, PAGE_COUNT, PAGE_MAXIMUM, PAGE_MINIMUM
PAGECOUNT

Following the above means that they must also be used in the footers, and can't be placed in headers, as it does not exist prior to the header being generated. Page x of y will also cause 2 passes as the y can't be determined until running through the data 1st generating a quasi-internal report.

Group Keep/Balance column height/Keep with detail/Balance column height (widows) Report Group Properties, Keep together on page (Orphans) Report Region Properties all force multiple passes and hence locking through the data.

As you might surmise, the running versions are much faster, and should always be used unless you must have the summary prior to when all the records are processed for the printing pass..

Simplify Sets
Use of a set that is overly complex for the report's needs is another bad idea, as it requires unneeded locks.  Create a set tailored for the usage by the report.  Turn off referential integrity for that set and set any children to read only where possible.

Sometimes it makes sense to copy the needed table records to a local drive (could also be any place, but the local drive is fastest normally) and run the report there (this requires a fair amount of code).  The copy goes very fast, and then only the local user is impacted by the report (and does not cause any locking of the shared tables over the network)

The general rule is never lock shared tables, ANYTIME, ANYHOW!
Xbasic versus Action Script Coding  (Email Comments) Updated 21 Mar 2010

One should be familiar with the strengths & weaknesses of any tool they use, and XBasic or Action Script coding in Alpha Five is no different.  Below should give you some of the advantages and disadvantages of Action Scripting and how to "bullet-proof" your code a bit more.

What is an Action Script?
Action Script coding contains individual Action Items.  Each Action Item allows specifying in a menu or dialog driven sequence, a number of values and settings that generate an underlying XBasic code. 

The Action item's underlying XBasic code is regenerated each time you save the Action Script.

Action Script Advantages
Action Script Disadvantages
Real-Life Action Script example vs XBasic
This is the XBasic code that an action script line to set a form field to an expression value generates:

'Set 'Value' property of 'TimeStamp' in Form 'TestForm' .
DIM form_name as c
if is_object(topparent.this) then
    form_name = topparent.name()+".this"
else
    form_name = ""
end if

DELETE expression_result
expression_result =  eval("now()",form_name)
parentform:TimeStamp.value = expression_result

This is the same exact functional XBasic code without the Action Script un-needed code

parentform:TimeStamp.value=NOW()

In the Action Script, the actual code has the disadvantages of being slower, more complex, deleting a variable named "expression_result", that could have potentially been a global and/or shared variable prior to that point that would now not exist.  If both global and shared levels existed before the delete, now the global will still exist.  The "expression_result =  " line will now assign a new value to the global variable "expression_result" since it was not explicitly dimensioned at a lower level.  Either the global variable will change it's value, or possibly cause an execution error as the object type (type T for time here) does not match the current type of the global variable.  

Recommendations
Most users of Alpha Five probably use Action Scripting more than XBasic coding. It is useful, particularly to non-programmers, but even as a 1st step for programmers. However, I would encourage moving to XBasic coding as your personal abilities allow.

Use Action Scripting as a tool to learn Xbasic. Use them to show the way or at least to point in a direction to go.  Then convert the Action Script to XBasic code, take out the extraneous coding and redundant sequences and you almost have "normal" code. The difference will be faster, more reliable code, over the long haul.

In the long-term, the more Xbasic you know, the better off you will be. It will allow you to use the debugger more effectively and generate faster, cleaner code. Inevitably you will end up needing something Action Scripts can not address. Action Scripts can do many things, but it doesn't do everything that XBasic coding can.

Some more comments of Action Script Code Generation
The moment you save an action script being edited (even with no modifications), the action script dialog settings are evaluated and new code is generated. So let's say you created in release "X", but are now editing in release "Y". Saving the Action Script will generate the code per the new releases' Action Script generating code. So even if you did not change anything, a small edit of an Action Script line followed by a save, can result in potentially different (and untested) code!

A similar thing could occur in the way a function works in an Xbasic piece of code operates, but at least with XBasic code, your code would not have changed! Also, it is much less likely that a function would change in a bad way in what it does, versus the code that Action Scripting might generate.

So, one could argue that the prudent thing to do would either change the code to XBasic so that it will not change, or make sure the underlying Xbasic code you start with, is the code you end with (except for your changes) when you edit in a new release.

Despite the surface appearance that they are the same, they are actually radically different. A function (Alpha's or a user's UDF has code that is syntactically compiled without errors otherwise it would never be able to be used) is tested for what it is.

But Action Scripting can generate myriads of combinations for the settings of a single action script line that would generate code that was never validated.  In probably 99.9% of the cases this is fine and works as expected.  But not always (see this thread Re: Variable Type "H" )

Some Action Script lines are very simple and straightforward. These seldom have issues, as there is almost a 1 to 1 correspondence to the XBasic code they generate. Others actions generate code from many selections of menus and settings. These generate a string of code that needs to be compiled (which Alpha does when you save it) without errors, and more importantly makes sense for all the settings. While, in general, each setting (or multiple settings) of an action script builds a portion of the code, there is no way every combination of possible codes could ever be fully tested. Some parts can be very straightforward to generate (and to test - this is why it generally works) and other parts depend on multiple settings which can be a nightmare to generate and can create extremely complex code.

In most cases, there is a very specific code generation sequence where you take setting "A", generate it's code, take setting "B" append it's generated code and so forth. But then there are cases where "B" implies what "A" generates, and where it needs to be located in the code, and so forth. It can get so intertwined, that it's impossible to totally predict what it will or could generate and these are where the typical failures tend to be.

AEX Library Numbers  (Email Comments)
The CSDA Code Utility has an AEX library compiling utility called LibMake that simplifies the process of making a desired library.  But whether you make an AEX library with LibMake or Alpha Five's built in tools, you still need to select a Library number.

Depending on how the libraries are loaded, the library numbers may or may not be important.

Alpha Five currently uses the following library number assignments:
Library # Purpose
0 Alpha5.AEX, Startup.AEX
1 System.AEX
2 modern.AEX
3 classic.AEX
100 Temporary for addin_manager explorer, and default compiled number
125 used in loading aex files
126 used for aex with database name
127 used for Addin_Library.Aex (which combines all installed library AEX's and ignores their library number)
200 used by web server to load AEX libraries to a web page
60-63 for AIMS products
64 for CSDA Code Utility and for AlphaSports.AEX

Coding tips  (Email Comments) Updated 07 Mar 2010
Isolate Code that depends on tricks  (Email Comments) Updated 03 Jan 2010
Any code that uses special tricks or depends on an odd or undocumented way that Alpha operates, or is Alpha Five version-specific, should first tried to be avoided.  These could lead to future support issues with the code.  

However, if you still think you need to use this, place the code inside a function.  It should contain a lot of comments in the code documenting the trick or usage so that anyone (including you in the future) understands what is happening.  

By placing it inside a function, you can update it easily as needed in the future to account for slight variations between Alpha Five versions or patches.

An example is the code ""+numbervalue

This code converts the numbervalue to a string, keeping all decimals, but without trailing decimal zeros and with no leading spaces padding. There are other ways to do it, but they can be more complicated. Since it is not well documented, it might change (although unlikely) in a future version.

Placing code like this in a function will allow replacement with a working version should it prove necessary.

Here is a version specific example that returns the current EXE name, should it be renamed.  It is the typical way many might write version specific code.

FUNCTION get_exe_name AS C ( )

IF version()>=8
    get_exe_name=a5.get_exe_name()
ELSE
    get_exe_name="Alpha5.exe"
END IF   

END FUNCTION

The above works fine, however, there are times when specific builds (patches) of Alpha work or don't work.  You could process each case as needed, but this can get to be very hard to check each build. In this case, and in any cases where you can test the operation of the "trick", you can dynamically test it and use some alternative value or method when it doesn't work.  For the above, rewrite it this way, and now it is not version-specific, but whether the method works or not-specific.

FUNCTION get_exe_name AS C ( )

IF eval_valid("a5.get_exe_name()")
    get_exe_name=a5.get_exe_name()
ELSE
    get_exe_name="Alpha5.exe"
END IF   

END FUNCTION

If the test requires a lot of time to process, continue storing the final result or whether the trick works so that you need to only do the test once
Using Active-X Objects tips  (Email Comments) Updated 27 Feb 2010
Please note the following:

1. Xbasis code using activeX controls can only reference the Active X properties (variables) with one pointer level (one dot). This is due to an issue with the Alpha Five XBasic Interpreter and Visual Basic Interface.  To solve it, set the parent pointers of a variable to a temporary variable pointer, and then use that pointer to reference. E.g.

objectpointer.subpointer.propertyvalue=-1

should be replaced with code similar to this

dim temp_pointer as p
temp_pointer=objectpointer.subpointer
temp_pointer.propertyvalue=-1

2. Active X Arrays referenced from Alpha Five start at base of 1 for the 1st array element vs the Active X control bases it at a base of 0.  Hence add 1 to the array element when using in Alpha Five Xbasic.  Also, reference to arrays in XBasic are always addressed with [ ] square brackets.  E.g. the lines in visual basic

objectpointer.subpointer(5).propertyvalue=-1
objectpointer.arrayvalue(9)="test string"

should be replaced with Xbasic code similar to this (taking in to account both issues)

temp_pointer=objectpointer.subpointer[6]
temp_pointer.propertyvalue=-1
objectpointer.arrayvalue[10]="test string"
Bitwise Logical Operations (Binary) with numbers  (Email Comments) Updated 08 April 2010

When interfacing to other programs using Active X controls or similar, they often use numeric values to act like having many logical flags, one per binary bit.  Each binary bit (base 2 arithmetic) can have a value of 1 or 0.

However Alpha Five only operates with decimal numbers in it's expressions.  So any time you have values in other formats, typically either binary (base 2), octal (base 8) or hexadecimal (sometimes called just hex - base 16)

So if you had a hexadecimal number 23, typically shown in other languages and programs as &H23 or 0x23, this is equivalent to the following

Value Base Valid digits for base
23 hexadecimal 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F
00100011 binary 0,1
043 octal 0,1,2,3,4,5,6,7
35 decimal 0,1,2,3,4,5,6,7,8,9

To convert a hex number from some other language to decimal in Alpha, Alpha needs for you to give the number portion as a string, so for the above, you might use

numeric_var=hex_to_dec("23") ' Translate Hex string &H23 to decimal number

Alpha Five does not have any binary or octal functions to convert those formats.

Once a numeric value, Alpha Five logical operators,
.AND.
.OR.
.XOR.
will all work on each bit of the binary equivalent as if you performed the logical operation on each bit position of each side.  So if doing a bitwise .OR. , an example might be

Expression Result Base Valid digits for base
23 .OR. A1 A3 hexadecimal 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F
00100011 .OR. 10100001 10100011 binary 0,1
043 .OR. 121 243 octal 0,1,2,3,4,5,6,7
35 .OR. 161 163 decimal 0,1,2,3,4,5,6,7,8,9

Since Alpha Five's expression parser understands only decimal numbers, you must convert them all to decimal numbers if not already in that format, e.g.
hex_to_dec("23") .OR. hex_to_dec("A1")
or
35 .OR. 161

Inverting the Bits (Bitwise NOT)
As it turns out, to invert the bits (change 1's to 0's and 0's to 1's), one might expect he .NOT. operator to work on the bits, but it does work.  The easiest way to invert the bits is to use .XOR. with the value on 1 side and a decimal equivalent of all binary 1's (for the needed width) for the other side.  So to invert hexadecimal 23, for a width of 16 bits, we would use an expression like
hex_to_dec("23") .XOR. ((2^16)-1)
which simplifies to
35 .XOR. 65535
and yields

Expression Result Base Valid digits for base
23 .XOR. FFFF FFDC
 hexadecimal 0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F
00100011 .XOR. 1111111111111111 1111111111011100 binary 0,1
043 .XOR. 121 377,334
 octal 0,1,2,3,4,5,6,7
35 .XOR. 65535 65500 decimal 0,1,2,3,4,5,6,7,8,9

Accessing specific Bits
To operate on a particular binary bit in "n" (where n is the bit position starting at 0 on the right most bit) in a long word of binary width "w" (typically values, 8, 16, 32, 64 which correspond to 1, 2, 4 or 8 bytes of information), the following expressions should be helpful

numeric_var=numeric_var .OR. (2^n) ' Set a bit at position n in a binary representation

numeric_var=numeric_var .XOR. (2^n) ' Invert bit at position n in a binary representation

numeric_var=numeric_var .AND. ((2^w-1) .xor. 2^n) ' Clear bit at position n in a binary representation

numeric_var=numeric_var*2 'Shift bits left 1 position, and zero-fill lowest bit

Test Bit n for a 1
IF((numeric_var .AND. (2^n))>0,.T.,.F.) ' True if bit n is set to a 1, else false

These are not the most efficient ways to do these, as in most cases the numbers n and w are known at the time of writing the expression, e.g. 2^2 is just the constant 4. And likewise
((2^8-1) .xor. 2^2)
is equal to
255 .xor. 4
which is equal to
251
In addition, you can do multiple bits simultaneously by combining individual bit expressions together

Blob bitwise operations
Using Blob functions, there are blob operations OR, AND, XOR and NOT that will work on each byte of the blob in a bitwise manner as well.
Alpha Software Value Added Reseller If you haven't yet upgraded to
Alpha Five Version 10, now's the time to
download the free Alpha Five trial as well!		 Alpha Five Version 8 box