Database Types
 |  |  |  |
 |
| |||||||||
 |
| ||
Do you remember the last time you bought a car? If not, just think of one that you own or a friend owns. For a moment, we’ll use car terminology so that we can explain a new database concept. Let’s say you were buying a Honda Civic sedan. Just from that name, you know at least three pieces of information: the make, the model, and the type. You actually know more than that, though. You know that you are buying a durable, reliable car from which you expect certain performance characteristics. The type combined with the make and model allows you to deduce this information. Now let’s apply this theme to databases.
When you work with a Microsoft Access database, you know the make (Microsoft) and the model (Access). The type of database is said to be a file-based database or a PC type, meaning it runs on a standalone personal computer or a set of connected workgroup computers. Based on the type, you also know that you are using a database from which you expect low-end performance characteristics. Sound familiar?
What if you’re working with an Oracle 8i database or a Microsoft SQL Server database? Again, you know the make (Oracle or Microsoft) and the model (8i or SQL Server). The type for these databases is client/server. Based on this information, you know that you are using a database that has high-end performance characteristics.
So the distinction being drawn here is that there are generally two types of databases:
-
PC databases
-
Client/server databases
Like a car, the model of database used determines data retrieval performance. Depending on which type of database you use, you can expect different performance levels and behavior in Crystal Reports.
PC Databases
A PC database is generally one that is stored in a single operating system file and sits on a personal computer for use by one person at a time. Examples of this are Microsoft Access, Btrieve, and Clipper DBF formats. There are certainly exceptions to the single operating system file. For example, DBF files each store exactly one table so when you’re working with a database, or collection of tables, you’re working with several operating system files. There are also exceptions to the single-user guideline. The point is that PC databases tend to handle low-volume and low-transaction data requests and are not scalable for use by large numbers of simultaneous users.
When Crystal Reports interacts with a PC database, it uses built-in utilities to open and retrieve data for a report; this can make the access to the data very fast. On the other hand, all of the processing takes place from within the confines of Crystal Reports, which may have the effect of slowing down the processing. Figure 11.1 demonstrates that the processing steps all take place in Crystal’s memory: requesting data, finding the data, and retrieving the data from the files.
Figure 11.1. Standalone PC database
All of this processing happens when Crystal opens a report and needs to refresh its data supply. A performance delay, therefore, is apparent to the person opening the report. Will you always experience a delay? Not necessarily. Imagine the difference in how long it would take to retrieve 100 records of data from a database versus 100,000 records. The performance impact will more readily be felt as the number of rows being retrieved increases. This is because all the data has to be pulled into Crystal Reports and then processed in terms of filtering, sorting, and grouping. The performance hit is also directly related to the amount of memory the personal computer has available to process the data.
Another variation on the PC type of database is the use of a set of personal computers that act as a workgroup in a local area network. In this scenario, a group of computers are connected together and can share information in a peer-to-peer manner. One of the computers is often designated as a file server, meaning that it holds all the important files that multiple users want to access. This centralized file server is generally the only one on which a nightly backup routine is run. This type of computer environment is often found in small-to-midsize office organizations. Figure 11.2 demonstrates what happens when Crystal opens a report and retrieves data from a nearby computer.
Figure 11.2. Workgroup PC database
The net effect of using PC databases is that all of the data processing is being handled by Crystal Reports. How do you know when you’re using PC databases in Crystal Reports? You choose Database Files in the Database Expert! Figure 11.3 shows the Database Files option below the Create New Connection folder in the Database Expert. Whenever you connect to a database using the Database Files folder, you are working with a PC database.
Figure 11.3. Database Files
| Note | PC databases are also known as direct access database files in Crystal Reports. |
Client/Server Databases
In contrast to PC databases, client/server databases tend to involve multiple operating system files and tend to be used by large groups of users simultaneously. The heart of a client/server database is a database server as opposed to a file server or personal computer. A database server is a much more sophisticated piece of software than a database file and it takes into account high-volume transactions and multiple simultaneous users. It often sits on a physical server that is dedicated to running just that piece of database software; it has been loaded with the maximum amount of memory, hard drive space, and processing units.
| Note | The use of the word server in the computer world has two meanings depending on its context. One meaning refers to the physical computer hardware and its operating system, usually in a network environment. The other meaning refers to a software application that runs on top of the operating system of a physical server. A database server is the latter of these types. |
When Crystal Reports interacts with a client/server database, Crystal is the client. The client makes a request for data to the server. The server then processes the request in its entirety and sends a result back to the client. In essence, the client delegates a task to the server and sits and waits for the finished result.
Can you remember the last time you delegated a task to someone? The experience goes best if the person you delegated to doesn’t come back and ask you a hundred interim questions before giving you a result. In a client/server environment, the client asks for something and doesn’t do any work on the report until the server hands back the requested data. During this time, the client computer can be doing other tasks, thus managing its workload more effectively. Figure 11.4 depicts this situation.
Figure 11.4. Client/server databases
The database server’s memory and processor are put to use processing the data. The memory and the processor on the Crystal Reports computer are responsible for building the report on the set of data that was returned by the server.
SQL-Capable Databases
To interact with a client/server database, software as well as people generally use a SQL approach. This means that you can use SQL commands to interact with the database for the purpose of retrieving data, updating data, deleting data, and even defining the tables and columns. PC databases, on
the other hand, are accessed directly by Crystal Reports using built-in utilities with vendor-specific, proprietary syntax. This is how they get their reputation for fast access; they use a proprietary access language optimized for that specific database.
SQL Standards
There are hundreds of different database products available today as third-party products, shareware, and freeware. While each of them may support the use of SQL statements, it is not a requirement that they each support the same set of SQL statements. Each database vendor tends to have its own dialect that conforms to but is not identical to the standards specified by the two groups which formalize such standards in the United States and internationally: American National Standards Institute (ANSI) and International Organization for Standardization (ISO). The most recent ANSI and ISO SQL standard is SQL3, also known as SQL99. Prior SQL standards were set in 1986, 1989, and 1992. This means that if you’re working on more than one type of database, there’s a good chance that among them they’re using a different set of SQL standards and subset of the SQL language.
Beyond the different ANSI and ISO standards, database vendors typically create extensions in their language to handle interactions that are specific to their own product. Each of the major database manufacturers has its own SQL dialect, including Oracle’s PL/SQL, Microsoft SQL Server’s Transact-SQL, and IBM’s DB2 SQL, to name a few. Figure 11.5 gives you a feel for the concept of supersets and subsets of standard SQL; ODBC, IBM, Microsoft, and Oracle all implement the SQL-99 Standard, and indeed all go beyond to varying degrees with extended or extra capabilities.
Figure 11.5. SQL dialects
Vendor-specific extensions create differences in the SQL dialects used in the database products. This is the purpose that the database driver (usually a .DLL file) fulfills when Crystal Reports needs to interact with a database: using the correct dialect to interact with the database.
ODBC
Notice also in Figure 11.5 that ODBC is listed. While not a vendor-specific SQL, it does provide a standard way to interact with multiple databases that are being treated as ODBC data sources. In the last chapter, ODBC (RDO) was discussed as a method to connect to a data source. We showed that you could connect to the same database multiple ways. This is true of PC databases as well. As an example, if you choose to connect to a Microsoft Access database as a database file, there’s a built-in driver to accommodate that. Or you can choose to connect to a Microsoft Access database as an ODBC data source.
There is a trade-off involved, depending on your decision. When you access a PC database directly as a direct access database file, you have only the vender-specific DLL between you and the data source. When you use ODBC, you have several layers between Crystal and the data source. So the advantage of the PC database, which we mentioned earlier, is optimum speed. The disadvantage is the lack of SQL support from within Crystal Reports.
The result of using an ODBC access mechanism with a PC-style database is that the world of SQL is now open to you while you’re using that data source from Crystal Reports. This includes being able to push some of the processing workload off Crystal Reports and onto the PC database in terms of sorting, filtering, and grouping.
| ||
 | |
| Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement | |
| Conello © 2000-2003 Feedback | |
EAN: 2147483647
Pages: 217