Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Aug 9, 2001

Beginning SQL Programming: Pt. 4 - Page 3

By DatabaseJournal.com Staff

When to Use SQL

My students frequently ask, "When should I be using SQL?" They may have created a database in a lower level desktop system and are considering switching to a DBMS that will require SQL. SQL is a language, not a software product, so the real question is best phrased as "When do I need to use a heavy-duty DBMS, which would then require me to start communicating in SQL?" There are several factors to consider which we'll look at in turn in this section.

Scaling

Many small sites or applications start in desk top systems such as Access. As they grow, a number of problems become apparent with the Access JET database engine. The primary problem is that it was never designed to support many users at once. Database software designed for the desktop generally fails when more than a few people try to use the data at the same time. Microsoft Access will perform well if five users are on a LAN. When you get to ten or twenty concurrent users, problems begin. Obviously a desktop database cannot handle the problems introduced by large numbers of concurrent hits from a web site.

Second, the Access file-based system becomes fragile when the amount of data starts to climb into the gigabyte range. Lower scale systems like Access also lack the security that more powerful systems offer. Access databases, for example, are quite easy to copy and walk away with. The individual installations can also be readily accessed.

So for these reasons companies move from Access type desk top systems to a more robust DBMS. Almost all of these heavy duty DBMS rely on SQL as the main form of communication. In summary, although there is nothing about SQL itself that concerns scaling, there is a need to use SQL in order to communicate with DBMS that scale well.

Speed

When SQL-enabled DBMS first appeared they were slower than previous DBMS. However, as they have taken over the market during the last ten years, SQL data engines have been the focus of an intense effort to improve performance. There are heated contests between the major vendors for bragging rights to the fastest machine with the lowest transaction price. But the intense competition drives the vendors to produce faster, more robust DBMS that work at lower and lower costs per transaction.

When you consider your quest for improved speed, consider that pure speed problems will be evident at all times, not just at peak usage. They are usually the result of more complex queries, particularly with the advanced techniques we study later like multiple joins on data that cannot be indexed. Although SQL itself does not cure speed problems, the implementation of faster DBMS does, and those faster DBMS will probably require communications in SQL.

Price

It is more expensive to run a server-centric DBMS (that only speaks SQL) than it is to use a desktop system such as Paradox or Access (for which you do not necessarily need to use SQL). First, the software is more expensive. Second, in most cases you have to run more expensive operating systems in order to support the DBMS. Third, you have to tune the OS differently to optimize for a DBMS than for other applications, so you generally need a server dedicated to the DBMS. Last, you will need personnel with more expensive qualifications.

However, the price of hardware and software is frequently the smallest item in an IT budget. As your data center grows, at some point the reliability, performance and standardization benefits of a DBMS that uses SQL will outweigh the cost.

A note on price –  you can get started on a SQL-centric DBMS for almost nothing. In fact, we have included the 120-day trial version of Microsoft SQL Server 2000 with this book. You can download other trial versions such as Oracle Personal Edition. But remember, that these are trial versions; when you are ready to build your business on a SQL-centric DBMS you will be paying a considerable amount more than you did for Access.

Universality

An alternative to using SQL statements is to write code in a procedural language like C++. The problem with this approach is that you are then closely tied to the procedural language, the metadata and the specific DBMS. If there is a change in the structure of the tables the code must change. If a new DBMS is installed, most of the code must be revised to mesh with the new DBMS system of pointers, recordset definitions, etc. But by using SQL statements almost all changes are handled by the DBMS, behind the scenes from the programmer.

Analytic Capabilities

An advanced feature of SQL (which is beyond the scope of this book) is the emergence of analytical tools that allow managers to extract business knowledge from large amounts of data. These tasks frequently require the constructions of multi-dimensional aggregates of data. These tools are referred to by the generic names of Online Analytical Processing (OLAP), Decision Support Systems (DSS) and Executive Information Systems (EIS). Each vendor then has proprietary trademarks for their version. All of these tools are only available in full-scale DBMS that require SQL for their means of communication.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM