 |
A code block is an unnamed or anonymous function. Code blocks are handy for writing short routines and attaching statements directly to an event. |
by Paul Mahar
Today you'll learn the ins and outs of query building and data manipulation. Like most data-driven Web applications, the basis of the shopping cart application is a query system. Four of the eight forms in the application are dedicated to searching the Title table. The cornerstone of any query system is performance. To make each of the shopping cart query forms as fast as possible, you need a solid understanding of the database classes and how BDE interacts with different table types. The following tasks of the day will provide you with the knowledge you need to make efficient and effective queries.
This chapter introduces many new methods and properties for working with databases, tables, and rows. You will learn about methods to create, copy, and delete tables. The information about working with rows includes methods and properties to order, append, filter, locate, and modify rows. You also will learn about some of the undocumented classes that the benchmark script employs to create indexes.
The database classes in IntraBuilder have properties that make searching tables easy. With very little code, you can add query by form and filter by form capabilities to a JavaScript data entry form. The Form Expert can also generate forms that include query by form and filter by form.
You can test drive query by form and filter by form without creating any forms or scripts. When you open or run a table, IntraBuilder adds both search options to the menu. Give them a try to find books about Delphi by following these steps:
IntraBuilder will look for the first row that contains "Delphi" in the Product field. If the table does not contain any books on Delphi, an alert message informs you that the value was not found, and the window shows the last row in the table.
Query by form tries to locate rows without restricting the rowset.
If a matching value is found, the row pointer moves to show that
row, but the user can still navigate to non-matching rows. Filter
by form does restrict the available rowset.
| NOTE |
The Apply Query By Form menu option sets the last row as the current row when the query does not match any rows in the table. This behavior is unlike the Rowset::applyLocate() method, which moves the row pointer beyond the last row to endOfSet. |
If you repeat the preceding exercise, substituting filter by form for query by form, the available rowset becomes much smaller. After a filter is in place, you can use queries to move around within the filtered rowset. Filters are not accumulative. If you apply another filter by form, IntraBuilder reverts to using the entire table before using the new filter.
If you know exactly what you are looking for, query by form and filter by form work great. When you are not as familiar with the data, these searches quickly lose their appeal. When you enter the search criteria in the Table Run window, you must enter exact values that match in length and case. If you look for DELPHI instead of Delphi, you will find no books. Similarly, if you enter Visual to look for books on Visual dBASE, the search comes up empty.
The Table Run window works around this limitation with a Find Rows dialog. The dialog, shown in Figure 10.2, defaults to search for rows with partial matches that ignore case.
Figure 10.2 : Using a partial case-insensitive search to find books on Visual dBASE.
| TIP |
You can remove a filter by form condition by selecting Table|Clear Filter by Form. This menu option is only enabled after creating a filter through Table|Apply Filter by Form. |
The IntraBuilder Form Expert can generate JavaScript forms that use query by form and filter by form. Things get a little more flexible when the query by form and filter by form operations take place in a JavaScript form. With this flexibility also comes a slight loss of intuitiveness. To see how it works, follow these steps to create a simple form that works with the Title table:
Try out the new form. Like the Table Run window, Expert-generated forms are case sensitive and require a complete match. Unlike the Table Run window, generated forms display no alert dialog when rows are not found. Instead the query is placed at endOfRowset, a place beyond the last row.
The Rowset class contains two properties to control case
sensitivity and partial matches. The filterOptions property
works with filter by form, and the locateOptions property
works with query by form. Table 10.1 shows the options you can
use with each property.
| Meaning | |
| Match length and case (the default) | |
| Match partial length, case sensitive | |
| Ignore case, match length | |
| Match partial length and ignore case |
These values for filterOptions and locateOptions correspond to all possible combinations of the Table Run window's Find Rows dialog. These values have no effect on numeric or date values. Exact matches are required for all non-string searches.
There isn't an option to search for values contained within a
field. For example, you cannot look for Visual dBASE books by
searching for dBASE if all the values start with Visual
rather than with dBASE. Such a search is possible through
events, and you'll get to that later this week when you create
the Keyword form.
| NOTE |
Properties that deal with query by form use the term locate instead of query. In the database class system, all tables are opened through a query. You cannot query a query, but you can apply a filter to a query or locate a row in a query. The filter terms in the user interface do match the underlying property names. |
You can set the rowset locateOptions and filterOptions properties of an Expert-generated form to 3. Changing the properties to 3 lets the end user relax about matching exact length or case. You can make this change through the Form Designer's Inspector. Another change to consider making is the removal of the full path for the query's sql property. As when you drop a table on a form, the Form Expert creates query objects with a full path in the sql property. Follow these steps to make these minor improvements:
The form will restrict the rowset to show several books from the
Teach Yourself series. These are easy to find because each
one starts with the word "Teach." Locating books in
the Unleashed series is not so easy. The titles of books
in the Unleashed series start with product names and cannot
be filtered into a group with this form. You'll learn how to deal
with this type of search tomorrow.
| TIP |
To remove a filter from an Expert-generated form, click New Filter and Run Filter without entering any values. The filter goes away, and the row pointer moves to endOfSet. Press Ctrl+PgUp to move back to the top of the rowset. |
The Form Expert generates a JavaScript form class with button
events set to code blocks. As a rule of thumb, you should not
use code blocks for anything but simple routines that contain
fewer than five statements. Explicit functions are also necessary
for routines that you plan to call from more than one event.
|
A code block is an unnamed or anonymous function. Code blocks are handy for writing short routines and attaching statements directly to an event. |
Unfortunately, the Form Expert ignores the rule and creates code blocks containing logical blocks that extend beyond five statements. Listing 10.1 shows a more readable but less executable version of the search code blocks. In this listing, each statement of the code block appears on a separate line. The logic and sequence are the same as is generated. Only the line breaks are different. This version of the code does not compile, because code blocks can reside only on a single line.
Listing 10.1. The Expert1 form with expanded code blocks.
1: // {End Header} Do not remove this comment//
2: // Generated on 11/02/96
3: //
4: var f = new expert1Form();
5: f.open();
6: class expert1Form extends Form {
7: with (this) {
8: height = 15;
9: left = 10;
10: top = 0;
11: width = 61;
12: title = "Title";
13: }
14:
15: with (this.title1 = new Query()){
16: left = 53;
17: top = 0;
18: sql = 'SELECT * FROM "title.dbf"';
19: active = true;
20: }
21:
22: with (this.title1.rowset) {
23: filterOptions = 3;
24: locateOptions = 3;
25: }
26:
27: with (this.html1 = new HTML(this)){
28: height = 1;
29: left = 1;
30: top = 3;
31: width = 14;
32: color = "black";
33: text = "Title";
34: }
35:
36: with (this.text1 = new Text(this)){
37: left = 16;
38: top = 3;
39: width = 41;
40: dataLink = parent.title1.rowset.fields["TITLE"];
41: }
42:
43: with (this.html2 = new HTML(this)){
44: height = 1;
45: left = 1;
46: top = 4;
47: width = 14;
48: color = "black";
49: text = "Author";
50: }
51:
52: with (this.text2 = new Text(this)){
53: left = 16;
54: top = 4;
55: width = 40;
56: dataLink = parent.title1.rowset.fields["AUTHOR"];
57: }
58:
59: with (this.html3 = new HTML(this)){
60: height = 1;
61: left = 1;
62: top = 5;
63: width = 14;
64: color = "black";
65: text = "Isbn";
66: }
67:
68: with (this.text3 = new Text(this)){
69: left = 16;
70: top = 5;
71: width = 15;
72: dataLink = parent.title1.rowset.fields["ISBN"];
73: }
74:
75: with (this.html4 = new HTML(this)){
76: height = 1;
77: left = 1;
78: top = 6;
79: width = 14;
80: color = "black";
81: text = "Product";
82: }
83:
84: with (this.text4 = new Text(this)){
85: left = 16;
86: top = 6;
87: width = 20;
88: dataLink = parent.title1.rowset.fields["PRODUCT"];
89: }
90:
91: with (this.button1 = new Button(this)){
92: left = 1;
93: top = 11;
94: width = 13;
95: text = "New Query";
96: onServerClick = {; with(this)
97: {
98: if (form.rowset.state==5)
99: {
100: text="New Query";
101: form.rowset.applyLocate();
102: }
103: else
104: {
105: text="Run Query";
106: form.rowset.beginLocate();
107: }
108: }
109: };
110: }
111:
112: with (this.button2 = new Button(this)){
113: left = 15;
114: top = 11;
115: width = 13;
116: text = "New Filter";
117: onServerClick = {; with(this)
118: {
119: if (form.rowset.state==4)
120: {
121: text="New Filter";
122: form.rowset.applyFilter();
123: }
124: else
125: {
126: text="Run Filter";
127: form.rowset.beginFilter();
128: }
129: }
130: };
131: }
132:
133: with (this.TITLE = new HTML(this)){
134: height = 2;
135: left = 1;
136: width = 51;
137: color = "black";
138: fontBold = true;
139: text = '<H1><Font Size="+4">T</Font>itle</H1>';
140: }
141:
142: this.rowset = this.title1.rowset;
143:
144: }
Most of the form defines the HTML and text components that show the field names and values. The default component names are composed of the class name and a number. It is a good idea to change the
default names to more specific names if you plan to add methods that refer to these components. One popular naming convention is to replace the number with the field name. For example, change text3 to textISBN. This makes it easily
identifiable as a text component that is linked to the ISBN field. If you're not going to add any methods, keep the default names.
The following two with blocks define the query and rowset objects. The first, on line 16, creates both the query and the rowset. The Rowset class is one of several that exist only within the context of another class. You cannot use new Rowset() to create an independent rowset. The same is true with the field classes, which exist only within the context of a Rowset or TableDef class.
The with block on line 22 does not contain a new operator. The rowset object was created along with the query object on line 15. Most with blocks in a form class definition create objects in addition to setting property values.
with (this.title1 = new Query()){
sql = 'SELECT * FROM "title.dbf"';
active = true;
}
with (this.title1.rowset) {
filterOptions = 3;
locateOptions = 3;
}
Like the other controls, the buttons are given very generic names. The first button is defined on line 91. It controls the query and contains the following as a code block of the onServerClick. This code executes on the IntraBuilder server when someone clicks the button and submits the form through a browser. Standard IntraBuilder buttons are submit type buttons in HTML.
with(this)
{
if (form.rowset.state==5)
{
text="New Query";
form.rowset.applyLocate();
}
else
{
text="Run Query";
form.rowset.beginLocate();
}
}
The query code block is enclosed in a with block
to avoid including a this reference prior to the text
and form property references. The code block relies on
the rowset's state property to determine whether
the user wants to enter a locate condition or has already
entered values and wants to start looking for values. Table 10.2
shows the possible values for the state property.
| Meaning | Query | DataLinks | Position | |
| Closed | Inactive | None | EndOfSet | |
| Browse | Active | Read only | True row | |
| Edit | Active | Modifiable | True row | |
| Append | Active | Modifiable | Buffer | |
| Filter | Active | Inactive | Buffer | |
| Locate | Active | Inactive | Buffer |
Prior to setting the active property to true,
a query is closed. When you drop a table on a form or use the
Form Expert, the query defaults to an edit state. When a query
is in an edit state, changes to dataLinks are saved if the query
is live. The dataLinks are fields tied to controls through the
dataLink property. Not all controls have a dataLink
property. The most common dataLink happens through a text control.
| NOTE |
An edit state is not synonymous with a live query. You can create queries against SQL tables where the requestLive property is false and the state property is 2. Such a query appears to be in an edit state although the data remains read-only. Do not assume all dataLink updates can be saved when in an edit state. |
The browse state occurs when the autoEdit property is set to false, which is not the default. In a browse state, dataLinks are read-only and an explicit call to the Rowset::beginEdit() method is required to switch to an edit state. When autoEdit is true, IntraBuilder works like Visual dBASE, where any modifications made to live rows get saved. Setting autoEdit to false makes IntraBuilder work more like Paradox or the Database Desktop, where a conscious effort is required to change from a read-only view to enable data editing.
Calling the Rowset::beginAppend() method sets the rowset into an append state. This causes IntraBuilder to create an empty row buffer that you can update through dataLinks or through the value properties of a field object. The Rowset::abandon() and Rowset::save() methods switch the rowset from an append state to the previous state.
The filter and locate states apply only to queries used within forms with dataLinks. The other four states can exist without a form or on forms without dataLinks. The Rowset::beginFilter() and Rowset::beginLocate() methods invoke these very similar states. In either state, all dataLinks go blank, and the user can enter sample values. The Rowset::applyLocate() and Rowset::applyFilter() methods change the state back to either edit or browse depending on autoEdit.
The two code blocks use the state property to figure out whether it is time to prepare or run a search. Prior to switching states, each code block also sets the text properties of the current button to an appropriate string value.
IntraBuilder enables you to create queries for many data sources. With any given data source, an IntraBuilder query has a wealth of properties for controlling how the query works. Minor modifications to a query can have a major impact on performance. Although an application may dictate using a particular table type or setting certain properties, you are likely to still have some leeway in how you set up your queries.
The best way to understand query performance is to try out a wide array of configurations and get times on each. This type of speed test is often called a benchmark. In the previous section you learned how filters and locate operations work. In this section you will learn how to make them work fast, and then you can get back to the shopping cart application armed with an in-depth understanding of queries.
The first step in doing a benchmark is figuring out what to test. Perhaps the biggest factor in query speeds is the table type. IntraBuilder uses BDE for dBASE and Paradox tables, and ODBC for Access tables. The professional edition can also connect to Microsoft SQL Server and InterBase through SQL-Links. The Client/Server edition adds SQL-Links for Sybase, Oracle, DB2, and Informix.
When you work with dBASE, Paradox, and Access tables, the data processing happens on the same PC that runs IntraBuilder. Some client/server configurations run database servers on the same machine as IntraBuilder; however, it is more common to run the database server on another dedicated server. The database servers also provide many configuration options that affect query speeds and are beyond the scope of this book. As a result, the benchmark presented here deals only with dBASE, Paradox and Access tables. You can run it with any version of IntraBuilder. If you are working with a client/server system, consider modifying the benchmark to work with your server. Most of the code shown for Access tables will work with SQL-based systems. See Day 7, "Tables, Queries, and Database Administration," for more information on setting up connections through SQL-Links.
To make a benchmark realistic, it should mimic what happens in
common applications. The shopping cart application requires searches
through a table of books based on products covered. Shoppers will
frequently look for all the books about Delphi. Because this is
a public Internet application, many shoppers may perform similar
searches in a very short period of time. To emulate this, the
benchmark will perform 25 consecutive searches through a table
looking for Delphi books.
| NOTE |
Searching the current Title table will not reveal much because it only contains 20 rows. To make a more realistic data set, the table needs to expand to 10,000 rows. This has proven to be enough records to detect differences between table types. Increasing the row count to 100,000 gives unreliable results due to disk caching. A 10,000-row table can immediately load into the cache of a machine with 32MB of memory. Running with much larger tables makes consecutive queries against the same table increase in speed because the table gradually loads into the cache over the course of the benchmark. |
The sample Title table is a dBASE table with 20 rows. The benchmark requires dBASE, Paradox, and Access tables with 10,000 rows each. The Bench0 JavaScript in Listing 10.2 creates the tables for you.
An ODBC driver is required to create the Access table. If you do not have an Access ODBC driver, comment out the call to makeAccess() before running the script. If you do want to create Access tables, you must first create an empty MDB file along with a matching BDE alias. Use the following steps. If you do not have the 32-bit ODBC driver for Access, skip ahead to Listing 10.2.
Although you can create new Access tables within IntraBuilder,
you must place them in an existing Access database. IntraBuilder
does not provide a facility to create an Access database.
 |
The ODBC Access driver does not surface indexes for MDB files stored in folders that use long filenames. If you place the bench.mdb file in C:\Program Files\Borland\IntraBuilder\Store, you will be able to create but not use indexes on the Access tables. This problem also arises when working with the Solution application Contacts database. The setup program installs the MDB to a lone filename folder by default. |
You can now open the empty database from the IntraBuilder Explorer.
Try it out to see whether the connections are working. If you
click the Table tab, the alias will appear in the Look In drop-down
list.
|
Always close all IntraBuilder Agents and instances of the Intra-Builder Designer before making changes to the BDE configuration. IntraBuilder loads BDE definitions at startup. Attempting to use an alias created during the current IntraBuilder session can lead to unexpected results. |
Listing 10.2. Bench0-The benchmark table generation script.
1: //
2: // Script: bench0.JS
3: //
4: // Author: Paul Mahar
5: //
6: // Date: 11/02/96
7: //
8: // Version: IntraBuilder 1.01
9: //
10: // Description: Create dBASE, Paradox and Access tables for benchmark.
11: // Access table create requires an installed ODBC driver
12: // with an alias of ACCESSBENCH pointing to an empty MDB.
13: //
14: // Parameters: None
15: //
16: // Tables: Titles.DBF - 20 row dBASE sample table
17: //
18: // Usage: _sys.scripts.run("bench0.JS")
19: //
20: // Reference: Chapter 10
21: //
22: //
23:
24: makeBench("title.dbf") ;
25: makeParadox() ;
26: makeAccess() ; // Comment out if ODBC driver not available.
27:
28: function makeBench(cTable)
29: {
30: make1000(cTable) ;
31: dittoBench(9) ;
32: }
33:
34: function make1000(cTable)
35: {
36: var q = new Query() ;
37: q.sql = "select * from " + cTable ;
38: q.active = true ;
39: var nCount = q.rowset.count() ;
40: q.active = false ;
41: _sys.databases[0].copyTable("title.dbf","bench.dbf") ;
42: generate("bench.dbf",1000 - nCount) ;
43: }
44:
45:
46: function generate(cTable, nCount)
47: {
48: var q = new Query(),
49: status = new Form(),
50: fObj = new Object() ;
51: status.top = 1 ;
52: status.left = 1 ;
53: status.height = 1 ;
54: status.open() ;
55: q.sql = "select * from " + cTable ;
56: q.active = true ;
57: for (var i = 0 ; i < nCount ; i++ )
58: {
59: status.title = "Creating record: " + (i+1) +
60: " of " + nCount ;
61: q.rowset.beginAppend() ;
62: for (var nf = 0 ; nf < q.rowset.fields.length ; nf++)
63: {
64: fObj = q.rowset.fields[nf] ;
65: if (fObj.type == "CHARACTER" )
66: {
67: fObj.value = randomString(fObj.length) ;
68: }
69: if (fObj.type == "NUMERIC" )
70: {
71: if (fObj.decimalLength > 0)
72: {
73: fObj.value = Math.random() * Math.pow(10,
74: (fObj.length - (1 + fObj.decimalLength))) ;
75: }
76: else
77: {
78: fObj.value = Math.random() *
79: Math.pow(10,fObj.length) ;
80: }
81: }
82: }
83: q.rowset.save() ;
84: }
85: status.close() ;
86: status.release() ;
87: }
88:
89: function randomString(nLength)
90: {
91: var cReturn = "",
92: sX = new StringEx() ;
93: for (var i = 0 ; i < nLength ; i++)
94: {
95: cReturn += sX.chr( 65 + ( Math.random() * 57 ) ) ;
96: }
97: return ( cReturn ) ;
98: }
99:
100: function dittoBench(nCount)
101: {
102: var q = new Query(),
103: u = new UpdateSet(),
104: status = new Form() ;
105: status.top = 1 ;
106: status.left = 1 ;
107: status.height = 1 ;
108: status.title = "Copying the source table" ;
109: status.open() ;
110: _sys.databases[0].copyTable("bench.dbf","temp1.dbf") ;
111: q.sql = "select * from temp2" ;
112: u.source = "temp2.dbf" ;
113: u.destination = "bench.dbf" ;
114: for (var i = 0 ; i < nCount ; i++ )
115: {
116: _sys.databases[0].copyTable("temp1.dbf","temp2.dbf");
117: q.active = true ;
118: while (! q.rowset.endOfSet )
119: {
120: q.rowset.fields[0].value = "" + i + "-" +
121: q.rowset.fields[0].value ;
122: q.rowset.save() ;
123: q.rowset.next() ;
124: }
125: q.active = false ;
126: status.title = "Expanding table - pass " +
127: (i+1) + " of " + nCount ;
128: u.append() ;
129: }
130: _sys.databases[0].dropTable("temp1.dbf");
131: _sys.databases[0].dropTable("temp2.dbf");
132: status.close() ;
133: status.release() ;
134: }
135:
136: function makeParadox()
137: {
138: var u = new UpdateSet(),
139: q = new Query(),
140: d = _sys.databases[0],
141: cSQL = "";
142: status = new Form();
143:
144: status.title = "Creating Paradox bench table" ;
145: status.top = 1 ;
146: status.left = 1 ;
147: status.height = 1 ;
148: status.open() ;
149: u.source = "bench.dbf";
150: u.destination = "temp1.db";
151: u.copy();
152:
153: q.sql = "select * from 'temp1.db'" ;
154: q.active = true ;
155: cSQL = getCreateStatement(q.rowset.fields,"bench.db",false);
156: q.active = false ;
157: if (d.tableExists("bench.db"))
158: {
159: d.executeSQL("DROP TABLE 'bench.db'");
160: }
161: d.executeSQL(cSQL);
162: u.source = "temp1.db";
163: u.destination = "bench.db";
164: u.append() ;
165: d.executeSQL("DROP TABLE 'temp1.db'");
166: status.close() ;
167: status.release() ;
168: }
169:
170: function getCreateStatement(feildObject, cTable, lODBC )
171: {
172: var cStatement = "CREATE TABLE "
173: if (lODBC)
174: {
175: cStatement += cTable + " (" ;
176: }
177: else
178: {
179: cStatement += "'" + cTable + "' (" ;
180: }
181: cStatement += q.rowset.fields[0].fieldName + " " +
182: typeSQL( q.rowset.fields[0], lODBC ) ;
183: for (var i = 1; i < q.rowset.fields.length ; i++)
184: {
185: cStatement += ", " + q.rowset.fields[i].fieldName + " " +
186: typeSQL( q.rowset.fields[i], lODBC ) ;
187: }
188: if ( ! lODBC )
189: {
190: cStatement += ", PRIMARY KEY(" +
191: q.rowset.fields[0].fieldName + "))" ;
192: }
193: else
194: {
195: cStatement += ")" ;
196: }
197: return ( cStatement ) ;
198: }
199:
200: function typeSQL( dbField, lODBC )
201: {
202: DBTypes = new AssocArray();
203: DBTypes["ALPHA"] = "CHAR(" + dbField.length + ")" ;
204: DBTypes["AUTOINCREMENT"] = "AUTOINC" ;
205: DBTypes["BCD"] = "DECIMAL(20,0)" ;
206: DBTypes["BINARY"] = (lODBC ? "BINARY" : "BLOB(0,2)") ;
207: DBTypes["LOGICAL"] = "BOOLEAN" ;
208: DBTypes["BYTES"] = "BYTES(0)" ;
209: DBTypes["DATE"] = "DATE" ;
210: DBTypes["FMTMEMO"] = (lODBC ? "BINARY" : "BLOB(10,3)") ;
211: DBTypes["GRAPHIC"] = (lODBC ? "BINARY" : "BLOB(10,5)") ;
212: DBTypes["LONG"] = "INTEGER" ;
213: DBTypes["MEMO"] = (lODBC ? "BINARY" : "BLOB(10,1)") ;
214: DBTypes["MONEY"] = "MONEY" ;
215 DBTypes["NUMBER"] = "NUMERIC" + ( lODBC ? "" : "(20,0)") ;
216: DBTypes["OLE"] = "BLOB(10,4)" ;
217: DBTypes["SHORT"] = "SMALLINT" ;
218: DBTypes["TIME"] = "TIME" ;
219: DBTypes["TIMESTAMP"] = "TIMESTAMP" ;
220: return ( DBTypes[ dbField.type ] ) ;
221: }
222:
223: function makeAccess()
224: {
225: try
226: {
227: var d = new Database("ACCESSBENCH") ;
228: var q = new Query(),
229: u = new UpdateSet(),
230: cSQL = "",
231: status = new Form() ;
232: status.title = "Creating Access bench table" ;
233: status.top = 1 ;
234: status.left = 1 ;
235: status.height = 1 ;
236: status.open() ;
237: q.sql = "select * from 'bench.db'" ;
238: q.active = true ;
239: cSQL = getCreateStatement(q.rowset.fields,"bench",true) ;
240: q.active = false ;
241: if (d.tableExists("bench"))
242: {
243: d.executeSQL("DROP TABLE bench") ;
244: }
245: d.executeSQL(cSQL) ;
246: q.database = d ;
247: q.sql = "select * from bench" ;
248: q.active = true ;
249: u.destination = q.rowset ;
250: u.source = "bench.db" ;
251: u.append() ;
252: u.null = false ;
253: status.title = "Indexing Access bench table" ;
254: indexAccess(d,q) ;
255: status.close() ;
256: status.release() ;
257: }
258: catch (DBException e)
259: {
260: alert(e.message) ;
261: }
262: }
263:
264: function indexAccess(d,q)
265: {
266: var idx = new Index() ;
267: for (var i = 0 ; i < 4 ; i++ )
268: {
269: idx.indexName = q.rowset.fields[i].fieldName ;
270: idx.fields = q.rowset.fields[i].fieldName ;
271: d.createIndex("bench",idx) ;
272: }
273: }
The Bench0 script is an example of a script that you should run only in the designer. It uses a procedural rather than an event-driven approach. When you run a JavaScript form, the only
procedural statements are the lines that create and open a form instance. Everything else is invoked through an event such as onServerLoad. In Bench0, the functions fire in sequence without event triggers.
A default or unnamed function consists of three function calls on lines 24 through 26. This code runs when the script is called through _sys.scripts.run(). IntraBuilder considers all statements that appear outside function and class declarations as part of the default function. The script would run the same if you were to move lines 24 through 26 to the bottom of the file.
The makeBench() function creates a 10,000-row dBASE table. The makeParadox() function duplicates the dBASE table as a Paradox table, and makeAccess() creates an Access table from the Paradox table. Comment out the call to makeAccess() if you do not have the 32-bit Access driver installed.
Line 28 calls make1000() to create the Bench table as a 1,000-row copy of the Title table. Then dittoBench() expands the Bench table to 10,000 rows.
function makeBench(cTable)
{
make1000(cTable) ;
dittoBench(9) ;
}
The make1000()function on line 34 starts by creating
a query for the base table and then uses Rowset::count()
to figure out how many rows exist. For the Title table, the count
will be 20. After deactivating the query, it uses the default
database object to copy the table. The default database object
is _sys.databases[0]. It points to an active database
that works with dBASE and Paradox tables. You cannot deactivate
the default database.
|
The Database::copyTable() method copies tables of the same type. If you try to copy a dBASE table to a Paradox table with Database::copyTable(), you will end up with a dBASE table with a .DB extension. To copy a table from one table type to another, use UpdateSet::Copy(). |
After the Bench dBASE table is created, line 42 calls generate() with two parameters. The first parameter is the table to add rows to and the second is how many rows to add. In this case, 980 rows will be added to the Bench table.
function make1000(cTable)
{
var q = new Query() ;
q.sql = "select * from " + cTable ;
q.active = true ;
var nCount = q.rowset.count() ;
q.active = false ;
_sys.databases[0].copyTable("title.dbf","bench.dbf") ;
generate("bench.dbf",1000 - nCount) ;
}
The generate() function provides status information while it runs. The status is shown in the title bar of a status form. Lines 59 and 60 update the title property once for each append. The title bar of the form dynamically updates while the function is running.
The function contains one loop nested inside another. The outer loop executes once for each appended row. The inner loop goes through the fields. For the Bench table, the outer loop executes 980 times while the inner loop executes nine times per outer loop. The inner loop executes a total of 8,820 times.
function generate(cTable, nCount)
{
var q = new Query(),
status = new Form(),
fObj = new Object() ;
status.top = 1 ;
status.left = 1 ;
status.height = 1 ;
status.open() ;
q.sql = "select * from " + cTable ;
q.active = true ;
for (var i = 0 ; i < nCount ; i++ )
{
status.title = "Creating record: " + (i+1) +
" of " + nCount ;
On line 61, Rowset::beginAppend() creates a row buffer to fill with random data. Fields are referenced through the fields property of the rowset. The fields property is a zero-based array. The inner loop uses the length property of the fields object to determine how many loops to go through.
q.rowset.beginAppend() ;
for (var nf = 0 ; nf < q.rowset.fields.length ; nf++)
{
Line 64 sets up fObj as a shortcut reference to the current field. The reference is used to assign random data to character and numeric fields. The generation of random string data is handled by randomString(). Random numeric values are adjusted to the field size, taking into account that a decimal place requires a single space.
fObj = q.rowset.fields[nf] ;
if (fObj.type == "CHARACTER" )
{
fObj.value = randomString(fObj.length) ;
}
if (fObj.type == "NUMERIC" )
{
if (fObj.decimalLength > 0)
{
fObj.value = Math.random() * Math.pow(10,
(fObj.length - (1 + fObj.decimalLength))) ;
}
else
{
fObj.value = Math.random() *
Math.pow(10,fObj.length) ;
}
}
}
| NOTE |
The DbfField class property decimalLength was changed from decimalsLength in Version 1.0 to decimalLength in Version 1.01. |
Line 83 uses Rowset::save() to commit the new row. After all the rows have been added, the status form is closed and released. Closing a form removes it from view without removing it from memory. IntraBuilder automatically removes forms from memory if they have no references. The system adds one reference to a form while it is open. You can use Form::release() to remove a form from memory without destroying the reference variable. Releasing an open form also closes it. An error occurs if you attempt to close a form after releasing it.
q.rowset.save() ;
}
status.close() ;
status.release() ;
}
The randomString() function uses Math::random() and StringEx::chr() to generate random string values. The ASCII values from 65 to 122 contain uppercase and lowercase letters and the following six symbols: [, \, ], ^, _, and `. The formula 65 + ( Math.random() * 57 returns values from 65 to 122, and passing this to the StringEx::chr() gives you a random character. Line 93 sets up a for loop to grow the random string one character at a time.
function randomString(nLength)
{
var cReturn = "",
sX = new StringEx() ;
for (var i = 0 ; i < nLength ; i++)
{
cReturn += sX.chr( 65 + ( Math.random() * 57 ) ) ;
}
return ( cReturn ) ;
}
After make1000(), the table contains 1,000 rows of unique data. The first 20 rows contain the five original rows with Delphi books. This set is followed by 980 rows of randomness. To grow the table again, this set is copied back to itself nine times to make 10,000 rows. When copying the rows out and back, you need to maintain unique values in the first field. The Paradox table type requires the first field to have unique values for a single field primary key. A primary key must exist before adding secondary indexes.
The dittoBench() function maintains unique values by including the current ditto pass number as part of the field value. The pass number increments each time the table is appended back to the original Bench table. It starts by declaring local variables on line 102. The function works with two temporary tables. Temp1 is an image of the original 1,000-row Bench table. Temp2 is the same image modified with the ditto pass number.
function dittoBench(nCount)
{
var q = new Query(),
u = new UpdateSet(),
status = new Form() ;
status.top = 1 ;
status.left = 1 ;
status.height = 1 ;
status.title = "Copying the source table" ;
status.open() ;
_sys.databases[0].copyTable("bench.dbf","temp1.dbf") ;
A query is used to update the Temp2 table. The UpdateSet object works with two tables. The source property points to the table containing values used to update the destination table.
q.sql = "select * from temp2" ; u.source = "temp2.dbf" ; u.destination = "bench.dbf" ;
The for loop starts by creating a fresh image of the original 1,000-row table called Temp2. A loop works through the Temp2 to update the first field of each row. After updating Temp, Line 125 deactivates the query. If the query has been left active, the next iteration of the loop would run into a problem when trying to overwrite the Temp2 table. The UpdateSet::append() method appends rows from the Temp2 table to the Bench table.
for (var i = 0 ; i < nCount ; i++ )
{
_sys.databases[0].copyTable("temp1.dbf","temp2.dbf");
q.active = true ;
while (! q.rowset.endOfSet )
{
q.rowset.fields[0].value = "" + i + "-" +
q.rowset.fields[0].value ;
q.rowset.save() ;
q.rowset.next() ;
}
q.active = false ;
status.title = "Expanding table - pass " +
(i+1) + " of " + nCount ;
u.append() ;
}
Along with the standard status shutdown, the function ends by deleting the two temporary tables. At this point, the dBASE table is done. The data in the dBASE table will be carried forward through the Paradox and Access tables.
_sys.databases[0].dropTable("temp1.dbf");
_sys.databases[0].dropTable("temp2.dbf");
status.close() ;
status.release() ;
}
The idea of copying the dBASE Bench table over to a Paradox table sounds pretty easy, and UpdateSet::Copy() seems like the perfect solution. Unfortunately, UpdateSet::copy() does not copy indexes when copying the table, and it does not create tables that have a primary key.
You can make secondary indexes using Database::createIndex() with an index object or Database::exectuteSQL() with the SQL CREATE INDEX command. Creating a primary key on a Paradox table is done by creating a new table with Database::executeSQL(). The makeParadox() function jumps through several hoops to create a Paradox Bench table with a primary key. It starts by declaring some string and object variables on line 138. It then shows status information and creates a Paradox version of the Bench table called Temp1.
function makeParadox()
{
var u = new UpdateSet(),
q = new Query(),
d = _sys.databases[0],
cSQL = "";
status = new Form();
status.title = "Creating Paradox bench table" ;
status.top = 1 ;
status.left = 1 ;
status.height = 1 ;
status.open() ;
u.source = "bench.dbf";
u.destination = "temp1.db";
u.copy();
The Database::executeSQL() method enables you to get access to database operations that are unavailable through other methods. One common use of Database::executeSQL() is to create a new table. The database classes do not contain a method specifically designed for creating a new table.
Line 155 calls getCreateStatement() with three parameters to construct the SQL CREATE TABLE command. The first parameter is a reference to a field object. The second parameter is the table name to be created. The third parameter is a SQL dialect flag for BDE and ODBC. Although SQL is a standard language, most vendors extend it with new field types and options. The extended field types for BDE and ODBC differ. The getCreateStatement() function can construct SQL statements for both BDE and SQL.
q.sql = "select * from 'temp1.db'" ; q.active = true ; cSQL = getCreateStatement(q.rowset.fields,"bench.db",false); q.active = false ;
If a Paradox Bench table already exists, line 159 will delete it. You could substitute Database::dropTable() for the DROP TABLE command on line 159. With the path clear and the CREATE TABLE command in hand, the Paradox Bench table is created through Database::executeSQL(). Line 164 uses UpdateSet::append() to copy data from the Temp1 table.
if (d.tableExists("bench.db"))
{
d.executeSQL("DROP TABLE 'bench.db'");
}
d.executeSQL(cSQL);
u.source = "temp1.db";
u.destination = "bench.db";
u.append() ;
Like the dittoBench() function, the cleanup code drops a table and releases the status form. Notice that, once again, a DROP TABLE is used in place of Database::dropTable().
d.executeSQL("DROP TABLE 'temp1.db'");
status.close() ;
status.release() ;
}
The getCreateStatement() function reveals some differences between BDE and ODBC SQL. BDE SQL supports table names that include folder names and file extensions. The table name for a BDE CREATE TABLE command is specified in quotes and the extension determines the table type. The ODBC Access driver does not support quoted table names. Line 175 starts the ODBC SQL with CREATE TABLE bench (. The BDE version starts with CREATE TABLE 'bench.db' (.
function getCreateStatement(fieldObject, cTable, lODBC )
{
var cStatement = "CREATE TABLE "
if (lODBC)
{
cStatement += cTable + " (" ;
}
else
{
cStatement += "'" + cTable + "' (" ;
}
The function builds up a comma-delimited field list. Each field is followed by SQL type information. The function places a comma between each field by creating the first field without a comma. All subsequent fields are preceded by a comma and created within a for loop. Note that because the field array is zero based, starting the loop at one skips past the first field.
cStatement += q.rowset.fields[0].fieldName + " " +
typeSQL( q.rowset.fields[0], lODBC ) ;
for (var i = 1; i < q.rowset.fields.length ; i++)
{
cStatement += ", " + q.rowset.fields[i].fieldName + " " +
typeSQL( q.rowset.fields[i], lODBC ) ;
}
The ending of the SQL command is also dialect dependent. BDE supports creating a primary key through the CREATE TABLE statement. The Access ODBC driver does not support the same syntax.
if ( ! lODBC )
{
cStatement += ", PRIMARY KEY(" +
q.rowset.fields[0].fieldName + "))" ;
}
else
{
cStatement += ")" ;
}
return ( cStatement ) ;
}
Lines 200 through 221 define the typeSQL() function. This function translates Paradox field types into SQL field types for BDE and ODBC. Rather than having an extensive set of if logic, the function uses an associate array to determine the return value. The major difference between associative arrays and standard arrays is that the index of an associative array is a character string instead of a number. Like standard arrays, an associative array is an object created with the new operator. You cannot pass anything to the constructor of an associative array.
function typeSQL( dbField, lODBC )
{
DBTypes = new AssocArray();
To add an element to an associative array, assign a value using an index that you have never used before. Lines 203 through 219 create elements for 17 field types. Some field types differ for ODBC and BDE. The numeric type for an ODBC field does not require any length or precision information, and the BDE numeric definition requires both.
DBTypes["ALPHA"] = "CHAR(" + dbField.length + ")" ;
DBTypes["AUTOINCREMENT"] = "AUTOINC" ;
DBTypes["BCD"] = "DECIMAL(20,0)" ;
DBTypes["BINARY"] = (lODBC ? "BINARY" : "BLOB(0,2)") ;
DBTypes["LOGICAL"] = "BOOLEAN" ;
DBTypes["BYTES"] = "BYTES(0)" ;
DBTypes["DATE"] = "DATE" ;
DBTypes["FMTMEMO"] = (lODBC ? "BINARY" : "BLOB(10,3)") ;
DBTypes["GRAPHIC"] = (lODBC ? "BINARY" : "BLOB(10,5)") ;
DBTypes["LONG"] = "INTEGER" ;
DBTypes["MEMO"] = (lODBC ? "BINARY" : "BLOB(10,1)") ;
DBTypes["MONEY"] = "MONEY" ;
DBTypes["NUMBER"] = "NUMERIC" + ( lODBC ? "" : "(20,0)") ;
DBTypes["OLE"] = "BLOB(10,4)" ;
DBTypes["SHORT"] = "SMALLINT" ;
DBTypes["TIME"] = "TIME" ;
DBTypes["TIMESTAMP"] = "TIMESTAMP" ;
Line 220 is a return with all the logic needed to select the appropriate return string. It uses the type property of the field object as an index to the associative array.
return ( DBTypes[ dbField.type ] ) ; }
The getCreateStatement() function completes by passing back a CREATE TABLE command for a Paradox or Access table. Here are the final results for the two table types.
BDE Paradox:
CREATE TABLE 'bench.db' (TITLE CHAR(60), AUTHOR CHAR(40),
ISBN CHAR(15), PRODUCT CHAR(20), PUBLISHER CHAR(20),
PUBDATE DATE, PRICE NUMERIC(20,0), NOTES BLOB(10,1),
COVER BLOB(10,5), PRIMARY KEY(TITLE))
ODBC Access:
CREATE TABLE bench (TITLE CHAR(60), AUTHOR CHAR(40), ISBN CHAR(15),
PRODUCT CHAR(20), PUBLISHER CHAR(20), PUBDATE DATE,
PRICE NUMERIC, NOTES BINARY, COVER BINARY)
The last two functions create and index the Access table. The makeAccess() function is similar to the makeParadox() function. The main difference is the catch for the DBException error on line 258. A DBException is a runtime error for a database operation. It provides more information than the standard Exception class.
function makeAccess()
{
try
{
var d = new Database("ACCESSBENCH") ;
Passing a BDE alias to the constructor of a Database object is a shortcut to assigning the database name and activating the database. Line 227 could also be written as this:
var d = new Database() ; d.databaseName = "ACCESSBENCH" ; d.active = true ;
If a database error occurs, an alert displays showing the error. Otherwise, the connection to the MDB is established, and normal initialization code begins on line 228.
var q = new Query(),
u = new UpdateSet(),
cSQL = "",
status = new Form() ;
status.title = "Creating Access bench table" ;
status.top = 1 ;
status.left = 1 ;
status.height = 1 ;
status.open() ;
This function uses both the default database and one pointing to the ACCESSBENCH BDE alias. The same query reference is used against both. Line 239 passes the query to getCreateStatement() using the Paradox version of the Bench table.
q.sql = "select * from 'bench.db'" ; q.active = true ; cSQL = getCreateStatement(q.rowset.fields,"bench",true) ; q.active = false ;
In makeParadox(), Database::tableExists() checks for the existence of a file in the current folder. This time the method is linked to an Access database that uses logical tables rather than physical files. With an Access database, all the table, indexes, views, and even source code are stored within a single MDB file. Line 245 passes the CREATE TABLE command to the ODBC driver.
if (d.tableExists("bench"))
{
d.executeSQL("DROP TABLE bench") ;
}
d.executeSQL(cSQL) ;
Queries have a database property that you can set to work with non-default databases. After the database property is set, IntraBuilder looks to the specified database for tables referenced in a SQL command. Line 246 sets the database property to the Access database. When working with BDE files, you can set the source and destination properties to strings containing filenames. Line 246 shows how to substitute a query reference for a physical filename.
q.database = d ; q.sql = "select * from bench" ; q.active = true ; u.destination = q.rowset ; u.source = "bench.db" ; u.append() ; u.null = false ;
The remainder of the function calls a function to create indexes on the new table and has standard cleanup code. The statements for the catch start on line 258.
status.title = "Indexing Access bench table" ;
indexAccess(d,q) ;
status.close() ;
status.release() ;
}
catch (DBException e)
{
alert(e.message) ;
}
}
Lines 264 through 273 define the indexAccess() function. This function creates indexes for the first four fields of the Access version of the Bench table. It uses parameters to select the table and database.
function indexAccess(d,q)
{
var idx = new Index() ;
for (var i = 0 ; i < 4 ; i++ )
{
idx.indexName = q.rowset.fields[i].fieldName ;
idx.fields = q.rowset.fields[i].fieldName ;
d.createIndex("bench",idx) ;
}
}
Run the script, after you feel familiar with what it does. Because
it does many things, you should set up to 30 minutes aside for
the script to complete. Figure 10.9 shows how the title bar of
the status form keeps you informed of the script's progress.
Figure 10.9 : Creating the benchmark tables.
|
Close all non-essential programs before running the Bench0 scripts. These scripts are resource intensive and may fail if virtual memory is already consumed by a large application such as Microsoft Word or Excel. |
When the script completes, open each of the Bench tables to examine the different table types. If the IntraBuilder Explorer is not showing file extensions, you will not be able to visually differentiate between the dBASE and Paradox versions of the Bench table. You can check the option to show extensions from the IntraBuilder Explorer Properties dialog.
Opening the dBASE Bench table reveals that the first 20 rows are the original rows from the Title table. The dBASE table defaults to the physical order in which the rows were generated. The Paradox table opens in logical order. Figure 10.10 shows all three open at once. Notice that the PUBDATE is a date-time field in Access. The first row of the Paradox table contains a Title field prefixed with a zero and a dash. If you select Table|Row Count for any of the tables, the dialog returns 10,000.
Figure 10.10 : Viewing the dBASE, Paradox, and Access versions of the Bench table.
Having three different table types makes it easy to decide that at least one of the variables to check is table type. Here are the others:
| NOTE |
If you modify the benchmark to work with remote SQL-Linked tables, add requestLive to the list of properties to test. The requestLive property determines whether rows can be edited. It can have a dramatic effect on SQL-Link operations while having no effect on the local table performance. |
The script contains four declared functions. Like the Bench0 script, the Bench1 script starts with function calls corresponding to each of the table types. Listing 10.3 shows the complete script with comments.
Listing 10.3. Bench1-The main benchmark script.
1: //
2: // Script: bench1.JS
3: //
4: // Author: Paul Mahar
5: //
6: // Date: 11/02/96
7: //
8: // Version: IntraBuilder 1.01
9: //
10: // Description: Benchmark for comparing Query performance against
11: // dBASE, Paradox and Access tables.
12: //
13: // Access table create requires an installed ODBC driver
14: // with an alias of ACCESSBENCH pointing to an MDB
15: // containing an indexed Bench table.
16: //
17: // Parameters: None
18: //
19: // Tables: Bench.DBF, Bench.DB and Bench.MDB
20: //
21: // Usage: _sys.scripts.run("bench1.JS")
22: //
23: // Reference: Chapter 10
24: //
25: //
26: #DEFINE TABLENAME "bench"
27:
28: _sys.os.delete("bench.txt") ;
29: keyGroups("DBF") ;
30: keyGroups("DB") ;
31: keyGroups("Access") ; // Comment out if not using Access
32:
33: function keyGroups(cType)
34: {
35: if (cType == "Access")
36: {
37: // Keys already exist for MDB
38: d = new Database("ACCESSBENCH") ;
39: benchGroup(d,cType,"Ascend","PRODUCT") ;
40: d.active = false ;
41: d = null ;
42: }
43: else
44: {
45: d = _sys.databases[0]
46: idx = ( cType == "DBF" ? new DbfIndex() : new Index() ) ;
47: idx.indexName = "ISBN" ;
48: idx.expression = "ISBN" ;
49: idx.fields = "ISBN" ;
50: d.createIndex( TABLENAME + "." + cType,idx) ;
51:
52: idx.indexName = "PRODUCT" ;
53: idx.fields = "PRODUCT" ;
54: idx.expression = "PRODUCT" ;
55: d.createIndex( TABLENAME + "." + cType,idx) ;
56: benchGroup(d, cType,"Ascend","PRODUCT") ;
57:
58: d.dropIndex( TABLENAME + "." + cType, "PRODUCT") ;
59: benchGroup(d, cType,"","") ;
60:
61: if (cType == "DBF")
62: {
63: idx.descending = true ;
64: d.createIndex( TABLENAME + "." + cType,idx) ;
65: benchGroup(d, cType,"Descend","PRODUCT") ;
66: }
67: }
68: }
69:
70: function benchGroup(d, cType, cKey, cKeyIdx)
71: {
72: if (cKeyIdx.length > 0)
73: {
74: benchTimer( d, cType, cKey, 0, "") ;
75: }
76: benchTimer( d, cType, cKey, 0, cKeyIdx) ;
77: benchTimer( d, cType, cKey, 0, "ISBN") ;
78: benchTimer( d, cType, cKey, 3, "ISBN") ;
79: }
80:
81: function benchTimer( d, cType, cKey, nFO, cIndex )
82: {
83: var status = new Form(),
84: q = new Query(),
85: start = new Date(),
86: nCount = 0 ;
87: status.top = 1 ;
88: status.left = 1 ;
89: status.height = 1 ;
90: status.title = cType + " " + cKey + " " + nFO + " " + cIndex
91: status.open() ;
92: start = new Date() ;
93: for (var x = 0;x < 5;x++)
94: {
95: q = new Query() ;
96: q.database = d ;
97: q.sql = "select * from " + TABLENAME +
98: ( cType == "Access" ? "" : "." + cType ) ;
99: q.active = true ;
100: q.rowset.filterOptions = nFO ;
101: q.rowset.indexName = cIndex ;
102: q.rowset.filter = "PRODUCT = 'Delphi'" ;
103: for (var y = 0 ; y < 5; y++)
104: {
105: q.rowset.first() ;
106: while (! q.rowset.endOfSet)
107: {
108: nCount++ ;
109: q.rowset.next() ;
110: }
111: }
112: q.active = false ;
113: q = null ;
114: }
115: writeTime(cType, cKey, nFO, cIndex, nCount, start, (new Date())) ;
116: status.close() ;
117: status.release() ;
118: }
119:
120: function writeTime(cType, cKey, nFO, cIndex, nCount, start, end)
121: {
122: var fOut = new File(),
123: elapsed = 0 ;
124: elapsed = ((end.getHours() * 3600) +
125: (end.getMinutes()* 60) +
126: end.getSeconds() )
127: -((start.getHours() * 3600) +
128: (start.getMinutes()* 60) +
129: start.getSeconds()) ;
130: if ( fOut.exists("bench.txt") )
131: {
132: fOut.open("bench.txt","rw") ;
133: fOut.seek(0,2) ; // go to end of file.
134: }
135: else
136: {
137: fOut.create("bench.txt") ;
138: }
139: fOut.puts("***") ;
140: fOut.puts("Table type: " + cType) ;
141: fOut.puts("Search key: " + cKey) ;
142: fOut.puts("filterOptions: " + nFO) ;
143: fOut.puts("indexName: " + cIndex) ;
144: fOut.puts("Count: " + nCount ) ;
145: fOut.puts("Seconds: " + elapsed ) ;
146: fOut.close() ;
147: }
Line 26 defines the benchmark table name. You can change this line to run the benchmark against the original Title table. If you want to see what happens with the Title table, change
"bench" to "title" on line 26 and comment out lines 30 and 31.
#DEFINE TABLENAME "title"
_sys.os.delete("bench.txt") ;
keyGroups("DBF") ;
// keyGroups("DB") ;
// keyGroups("Access") ; // Comment out if not using Access
The keyGroups() function controls what combinations of
properties are used with each table type. Not all combinations
are possible to script. For example, the IntraBuilder 1.01 does
not support scripting the creation of descending indexes on Paradox
tables. The function runs all applicable queries for each table
type. Lines 39, 56, 59, and 65 call benchGroup() with
four parameters as shown in Table 10.3.
| Sample Value | Meaning | Possible Values |
| d | Database handle | n/a |
| cType | Table type | Access, DB, or DBF. |
| "Ascend" | Search key | Ascend, Descend, or blank |
| "PRODUCT" | Search key name | PRODUCT or blank |
After timing the Access table, the function deactivates the database. Line 41 releases the database object. Although you cannot release a variable, you can assign null to the variable to release the referenced object.
function keyGroups(cType)
{
if (cType == "Access")
{
// Keys already exist for MDB
d = new Database("ACCESSBENCH") ;
benchGroup(d,cType,"Ascend","PRODUCT") ;
d.active = false ;
d = null ;
}
For dBASE and Paradox tables, the function creates appropriate
indexes before calling benchGroup(). The first group
of benchmarks requires indexes on the ISBN and Product
fields. The second group requires the absence of an index on the
Product field. If the table type is dBASE, an additional
benchmark is applied with a descending index.
| NOTE |
The DbfIndex and Index classes are undocumented in IntraBuilder 1.01. See Appendix B, "The Undocumented IntraBuilder," for more information about using these and other undocumented classes. |
The DbfIndex and Index classes have slightly different property sets. DbfIndex uses the expression property to determine the index key values. This enables you to use a simple field name or a complex dBASE expression. The Index class uses a fields property in place of an expression property. The fields property accepts a list of one or more fields as the index key. Lines 46 through 49 assign values to both expression and fields.
else
{
d = _sys.databases[0]
idx = ( cType == "DBF" ? new DbfIndex() : new Index() ) ;
idx.indexName = "ISBN" ;
idx.expression = "ISBN" ;
idx.fields = "ISBN" ;
d.createIndex( TABLENAME + "." + cType,idx) ;
idx.indexName = "PRODUCT" ;
idx.fields = "PRODUCT" ;
idx.expression = "PRODUCT" ;
d.createIndex( TABLENAME + "." + cType,idx) ;
benchGroup(d, cType,"Ascend","PRODUCT") ;
d.dropIndex( TABLENAME + "." + cType, "PRODUCT") ;
benchGroup(d, cType,"","") ;
if (cType == "DBF")
{
idx.descending = true ;
d.createIndex( TABLENAME + "." + cType,idx) ;
benchGroup(d, cType,"Descend","PRODUCT") ;
}
}
}
The benchGroup() function passes on the first three parameters
it gets to benchTimer() and adds two new options. This
function starts the benchmark with different filterOptions
and indexName values. Table 10.4 shows the four possible
configurations used by benchGroup().
| indexName | |
| (blank) | |
| (same as search key) | |
| ISBN | |
| ISBN |
Line 72 adds a conditional optimization. If the current search key is blank, it does not run the same set of options twice.
function benchGroup(d, cType, cKey, cKeyIdx)
{
if (cKeyIdx.length > 0)
{
benchTimer( d, cType, cKey, 0, "") ;
}
benchTimer( d, cType, cKey, 0, cKeyIdx) ;
benchTimer( d, cType, cKey, 0, "ISBN") ;
benchTimer( d, cType, cKey, 3, "ISBN") ;
}
The benchTimer() function contains the query loops along with code to capture the start and end times of each benchmark. It starts out by opening a status form on line 91.
function benchTimer( d, cType, cKey, nFO, cIndex )
{
var status = new Form(),
q = new Query(),
start = new Date(),
nCount = 0 ;
status.top = 1 ;
status.left = 1 ;
status.height = 1 ;
status.title = cType + " " + cKey + " " + nFO + " " + cIndex
status.open() ;
Line 92 recaptures the start time to avoid timing any delay caused by the status form. Two nested loops control the query iterations. The outer loop creates a new query based on all the parameters passed to the function. It uses a filter to search for Delphi books.
start = new Date() ;
for (var x = 0;x < 5;x++)
{
q = new Query() ;
q.database = d ;
q.sql = "select * from " + TABLENAME +
( cType == "Access" ? "" : "." + cType ) ;
q.active = true ;
q.rowset.filterOptions = nFO ;
q.rowset.indexName = cIndex ;
q.rowset.filter = "PRODUCT = 'Delphi'" ;
The inner loop passes through the current query five times without resetting any properties. It counts how many books are found as a check sum. Each pass through the query locates 50 books out of the 10,000 rows. This is based on the original five Delphi books from the Title table. After going through the table 25 times for the inner and outer loops, the book count completes at 1,250 successful finds.
for (var y = 0 ; y < 5; y++)
{
q.rowset.first() ;
while (! q.rowset.endOfSet)
{
nCount++ ;
q.rowset.next() ;
}
}
Line 113 deactivates and releases the query to prepare for the next pass. At this point, the variable q still exists but the query is cleared from memory.
q.active = false ; q = null ; }
Line 115 calls another function to write the elapsed time to a file. The end time is passed as the expression (new Date())).
writeTime(cType, cKey, nFO, cIndex, nCount, start, (new Date())) ; status.close() ; status.release() ; }
The writeTime() function records the benchmark results into a text file. The elapsed time is calculated from the start and end date objects. A date object contains both the time and the date. To make calculations on the time, the function converts the time values to seconds using Date::getHours(), Date::getMinutes(), and Date::getSeconds().
function writeTime(cType, cKey, nFO, cIndex, nCount, start, end)
{
var fOut = new File(),
elapsed = 0 ;
elapsed = ((end.getHours() * 3600) +
(end.getMinutes()* 60) +
end.getSeconds() )
-((start.getHours() * 3600) +
(start.getMinutes()* 60) +
start.getSeconds()) ;
| NOTE |
The formula to calculate elapsed time does not account for running the benchmark into a new day. If you plan to run the benchmark near midnight, you might want to add a call to Date::getDate(). |
The rest of the function opens or creates the text file and writes out the results. Line 28 erases the bench text file making results accumulative only to the current run.
if ( fOut.exists("bench.txt") )
{
fOut.open("bench.txt","rw") ;
fOut.seek(0,2) ; // go to end of file.
}
else
{
fOut.create("bench.txt") ;
}
fOut.puts("***") ;
fOut.puts("Table type: " + cType) ;
fOut.puts("Search key: " + cKey) ;
fOut.puts("filterOptions: " + nFO) ;
fOut.puts("indexName: " + cIndex) ;
fOut.puts("Count: " + nCount ) ;
fOut.puts("Seconds: " + elapsed ) ;
fOut.close() ;
}
That does it for Bench1. If you haven't already done so, run the script now. Like the Bench0 script, this can take up to 30 minutes to run. It will provide the most accurate results when all other processes are closed prior to running the script. Avoid task switching during the run.
The benchmark results reveal some key aspects to the IntraBuilder query system. The numbers given here are from my home PC and are only for reference. I encourage you to run the benchmark in your own environment to get a better picture of how performance tradeoffs are likely to affect your applications. Also be sure to get the latest ODBC, BDE, and IntraBuilder patches before running. Microsoft and Borland are continually improving their software, and some enhancements that were unavailable as of this writing may have a significant impact.
The benchmark machine used here was a 60Mhz Dell OmniPlex with 48MB RAM. All times were run under Windows 95. Although this system is adequate for development, I recommend that you deploy applications under Windows NT using a machine of at least 90Mhz.
After running the Bench1 script, you can open the resulting text file from the IntraBuilder Explorer. It appears on the Custom tab after selecting View|Refresh. It contains a long list of results. A portion of the bench.txt file is shown in the following code. The count is provided to double-check the query accuracy. All counts should appear as 1,250.
Table type: DBF filterOptions: 0 indexName: Search key: Ascend Count: 1250 Seconds: 1 *** Table type: DBF filterOptions: 0 indexName: PRODUCT Search key: Ascend Count: 1250 Seconds: 7 *** Table type: DBF filterOptions: 0 indexName: ISBN Search key: Ascend Count: 1250 Seconds: 8
A quick glance through the file is enough to show that the dBASE
table type is faster than a Paradox table, and a Paradox table
is faster than an Access table. For a more complete picture, you
can rearrange the numbers as shown in Table 10.5.
| indexName | Search Key | ||||
| (blank) | Ascend | ||||
| PRODUCT | Ascend | ||||
| ISBN | Ascend | ||||
| ISBN | Ascend | ||||
| (blank) | (none) | ||||
| ISBN | (none) | ||||
| ISBN | (none) | ||||
| (blank) | Descend | ||||
| PRODUCT | Descend | ||||
| ISBN | Descend | ||||
| ISBN | Descend |
The table reveals that, without question, IntraBuilder performs much faster with dBASE tables than with the other two desktop formats. It also shows how unordered queries work faster than when using an index, even when the search key is the current index.
All the differences can be traced back to the database engine. Whenever BDE searches for values, it looks to see whether an index is available for the field being searched. If one is found, the engine reads the index rather than the table. If the search is unordered, the engine can rely exclusively on the index to find rows. When the set is ordered, the engine must bounce between two indexes to perform the search.
The advantage of a dBASE table is also its weakness. The dBASE format uses fixed length fields. All variable length information is stored in a separate DBT file. The fixed length format allows the engine to use simple offsets to navigate through the table. The level 5 dBASE table also contains no data integrity information.
Both the Paradox and Access formats are much more complex. The structures provide for storing template, relation, and other validation information as part of the table definition. The price of this integrity is speed.
Figure 10.11 shows another view of the results. This graph was created by placing the data from Table 10.5 into Excel. Again, the results show significant differences. The conclusion of this benchmark is to remember that the F in DBF is for "Fast."
Figure 10.11 : Graphing the results where shorter bars mean better performance.
Let's get back to the shopping cart application and put some of the performance tricks into practice. To create a Quick search form, you can use the fastest combination of properties and table type. This form needs to list all the products about which the store has books. The product list is in a Product table. One of the main tasks for this form is getting the product list from the table and placing it in a list box.
The list box control does not have a property to populate the control directly from a table. Instead, it has an options property that you can assign to an array or a file list. The Quick form will use the array feature after filling an array with field values. This is a case where you will have to employ the use of JavaScript rather than a property builder.
The form also needs a button that lets shoppers move from the
Quick form to the Results form. The Quick form needs to configure
a query on the Results form before calling it. Today's version
of the Results form is a simple stub form that shows only the
count of how many books are available for a given product.
|
A stub form is a placeholder for an undeveloped form. Stubs are used to test dependencies between forms before all forms are complete. |
Start developing the Quick form by creating a form that ties a select list to the Product table. Like the Help form, this form will use the toolbar custom form class. Follow these steps to create the new Quick form:
| NOTE |
The component palette shows the list box as a select list. Also, the default name for a list box is select1. The discrepancy comes from the different ways that HTML and Windows work. In Windows, the control is a list box and it is independent from a combo box. In an HTML form, both controls are variations of a select control. |
Before adding the Search button to the Quick form, you'll need to create a stub form for the Results form. A stub form allows another form to work with the least amount of effort on your part. That sounds pretty good at the end of a long day of running benchmarks. The stub form eventually will evolve into the Results form. For now, all it needs to include is a query, an HTML object, and one method. Use the following steps:
| NOTE |
The capitalization you enter when saving a new JavaScript form is used for the class definition. If you save the form as RESULTS.JFM, the class name becomes RESULTSForm. The shopping cart application assumes that JavaScript forms are always created with lowercase filenames. Future references to the Results form class name will be resultsForm. You may change the case of an existing JavaScript form by opening the file in the Script Editor and modifying two lines near the top of the script. The first line is the one that creates an instance and contains var f = new <class name>(). The other line to change is the first line of the class definition. It begins with class <class name> extends. |
You just created a form that doesn't work. If you run the form, it calls the htmlCount_onServerLoad() function. That function references a non-existent property called titleCount. There is no need to fix the error within this form, because shoppers will not be able to run this form without running the Quick form or Keyword form first. Both search forms will create the titleCount property before opening the Results form.
It is now time to go back to the Quick form and add the Search button that opens the Results form. All that you need to do in this part is add one button and a linked onServerClick with these steps:
| NOTE |
This rendition of the buttonSearch_onServerClick() method contains some items that you will modify on Day 11. The first is the _sys.scripts.load() statement. This is included so that you can call the Results form directly from the Quick form without going through the Help form. |
You can now run the Quick form without running the Help form. This makes it a little easier to debug and see the interaction between the Quick form and the Results form. If you pick Delphi from the select list and click search, the Results form opens to show you that five books have been found. These are the same five books that the benchmark found 27,500 times if you ran against all three table types.
In its current state the Quick form provides a one-way street to the Results form. After the Results form opens, it is the only open script and every toolbar button returns an error. Figure 10.14 shows the search results along with the error that occurs if you click the Quick toolbar button.
Figure 10.14 : Viewing results and a runtime error.
Today's topics were all focused on queries. The day began by learning about query by form and filter by form. The Table Run window and Form Expert give you fast access to both query by form and filter by form. You learned the strengths and weaknesses of both form searching techniques.
After looking at some of the code behind the query by form and filter by form, you delved into an extensive set of benchmarks. The investigation into query performance started with a detailed analysis of a JavaScript script that generates 10,000 row tables for dBASE, Paradox, and Access tables. The generation script revealed many differences between the table types and how the database classes interact with them.
The table generation process consumed more than half the day, but you finally reached the benchmark script. The analysis of the benchmark script focused more on the properties that impact query performance than on differences between table types. Both scripts showed how IntraBuilder can work as a procedural data-processing engine in addition to an event-driven Web form server.
The results from the benchmark showed some extreme differences between table types and property settings. The fastest query beat the slowest by a factor of 170. Although table type had the biggest impact on performance, the indexName and filterOption values were also shown to be significant.
The end of the day brought you back to the shopping cart application. You started to develop the Quick form and Results form. You learned how to populate a list box and how to use a selection from the list box as part of a query filter. The day concluded with a working prototype of the Quick form and a stub for the results form. That is where you will start tomorrow when you finish up these forms and tie them into the Help form.
| Q: | If I use an Oracle server, can I get better performance than is possible with dBASE tables? |
| A: | It depends on what you're doing. The dBASE tables are likely to out-perform Oracle when the data sets are small and the searches are simple. SQL servers, such as Microsoft SQL Server, Oracle, Sybase, and InterBase, have the performance edge when it comes to extremely large data sets and complex queries. They also have the advantage of running on machines other than the one running the IntraBuilder Server. Unlike client/server connections, the BDE and ODBC processes must share CPU time with IntraBuilder. |
| Q: | I tried to run the benchmark, but I ran into many problems when trying to create the Access table. What is going wrong? |
| A: | IntraBuilder is certified only with specific versions of the Access ODBC driver. Please check the readme.txt file that comes with IntraBuilder for a list of supported driver versions. |
| Q: | I checked in the readme.txt file and found the supported ODBC driver versions, but I still can't tell if I'm using the right ones. How do I get the driver version from an ODBC driver? |
| A: | From the Windows Control Panel, open the 32-bit ODBC administration tool. Click the Drivers button, select the Access driver, and click About. The About box lists the driver filename, date, and version information. |
| Q: | Is the search key optimization done in IntraBuilder similar to FoxPro's Rushmore technology? |
| A: | The BDE equivalent to Rushmore is SpeedFilters. Part of the technology involves using indexes for searches in addition to the more traditional use of table ordering. SQL servers also use this technique in addition to other optimizations. Other parts of the Rushmore and SpeedFilter technologies involve Xbase expression optimization and are not applicable to IntraBuilder. |
The Workshop section provides questions and exercises to help you get a better feel for the material you learned today. Try to answer the questions and at least think about the exercises before moving on to tomorrow's lesson. You'll find the answers to the questions in Appendix A, "Answers to Quiz Questions."
