Dec/090
Oracle Performance Tuning Tactics Part 2: Using Indexes
To recap, the “weapons” available in your “arsenal” for performance tuning are as follows:- Full table scans ; Indexes; Joins; Views; De-normalization; Oracle Stored Procedures; Sub-queries.
2. Indexes
There are several types of indexes available with Oracle databases:
* normal/B-Tree (balanced/ binary tree) indexes;
* bitmap indexes (which literally use a map of bits to represent the whole index and are
* function-based indexes (which are based on the value of an expression instead of the value of a column);
* partitioned indexes (tables and indexes may be partitioned separately)
* domain indexes (application-specific indexes created on complex data types such as spatial data).
This article only discusses bitmap and normal (b-tree) indexes.
2.1 B-Tree Indexes
B-Tree Indexes are most appropriate when retrieving a small amount of data from a very large table. The bigger the table and the lower the number of rows that you want to retrieve then the more effective the index.
If you think of the printed material in the real world, this makes perfect sense, you wouldn’t bother to create an index for a short article, but you would certainly want one for a lengthy reference book. The reasons are analogous too. When reading a short article it would take longer to find the areas of interest using the index then it would to read the whole article. It’s the same when you’re querying the database – for a small table with Oracle’s multi-block read feature of Oracle, the whole table would be read in one go, so by going to the index first, the database would have to read more data overall.
Indexes may improve read access to data and update operations (including insert and delete) using sub-queries. However update performance will be impaired because every time a row is inserted into, deleted from or updated (if the indexed column(s) is/are updated) in the database, the indexes have to be adjusted accordingly, thereby increasing the amount of i/o that has to performed.
Therefore having many indexes is ideal when the database is mostly read only, but if there are a high proportion of inserts and deletes and only a few read operations, then you would find that adding more indexes would degrade rather than improve performance. You also need to ensure that the column(s) you are proposing to index is/are used frequently as the limiting conditions for a query, if not you will waste a lot of space and degrade database performance.
Another factor to consider with b-tree indexes in Oracle databases, is how specific they are – their cardinality. The ideal b-tree index is one that refers to only one row in the table, as a primary key does by definition. This reduces considerably the amount of i/o required to retrieve the data, but if the index value relates to many rows because it is not selective, then a lot more data will be retrieved resulting in unnecessary i/o.
Nov/090
Optimizing Mysql Tables
Just like you need to defrag and check your file system, it’s important to do the same thing with MySQL tables. If you don’t, you might end up with slow and corrupted database tables. Follow this easy tutorial to set up an automatic optimization for your db.
Speed in MySQL is all about how fast the database can find the information you request. To help MySQL clean up and get all the best possibilities to gather your information, you can perform the command “Optimize” on your tables.
Want to speed up your MySQL performance in a simple but effective way?
Because you probably will add and delete tables from time to time, you want a solution that works no matter how your database looks like. For this I will give you a PHP script that finds all your tables, and then perform Optimize on every single one. A good idea can be to do this every night (or whenever your server is least accessed) with “cron” because you don’t want to delay your surfers to much.
Here is the script (really simple actually):
Make sure you modify the four variables at the top of the script so they match your configuration.
// Change these four variables to match your configuration.
// You will probably only need to change “user”, “pwd” and “dbName”.
$server = ”localhost”;
$user = ”mysql_user”;
$pwd = ”mysql_password”;
$dbName = ”mysql_dbName”;
$link = mysql_connect($server, $user, $pwd);
if (!$link) {
die(’Could not connect: ‘ . mysql_error());
}
$db_selected = mysql_select_db($dbName, $link);
if (!$db_selected) {
die (’Can\’t use $dbName : ‘ . mysql_error());
}
// Find the names of all tables in the selected db.
$alltables = mysql_query(”SHOW TABLES”);
// Go through all tables.
while ($table = mysql_fetch_assoc($alltables))
{
foreach ($table as $db => $tablename)
{
// Perform the magic “OPTIMIZE” on every table in the db.
mysql_query(”OPTIMIZE TABLE ‘”.$tablename.”‘”)
or die(mysql_error());
}
}
Oct/090
very basic sql optimization in mysql
It’s important to know how to write your queries fast. And there are also ways to do your queries faster Inside mysql, called Indexes. They help mysql do the work faster, but you need to tell how…
When you start getting large tables you can sometimes end up with slow running queries.
It’s important to optimize your queries and tables from the beginning, so you don’t end up with slow queries one year later when your tables have increased.
Badly created tables and queries is hard to find when you only have limited data in your tables. But there are tools to use to get a hint on if you will have problems later…
First the basics behind the scen in databases. Let’s say you are combining three tables with a join. Then mysql has to look through all three tables and then find a way to combine them in the correct way.
Take this sql query that will find the person “donald duck” and information connected to him in three tables:
SELECT a.id
FROM a, b, c
WHERE a.id = b.a_id
AND b.id = c.b_id
AND c.name = “donald duck”
Let’s say there are 2 rows in each table. What mysql does now is to start looking through all tables from the top down to the bottom and try to find matches.
The important thing to know about this is some math. Either you understand it or you just trust me that it is correct…
To combine all three tables, mysql needs to go through 2*2*2 combinations. This is 8 combinations to find the correct one.
Doesn’t sound that much of a deal, but let’s say we have 500 rows in the tables each. This gives our beloved database 500*500*500 = 1 250 000 different combinations to go through.
Luckily there are some smart people that have found a way to deal with this
It’s called “Indexes” and they help the database to cut down on the number of rows it has to go through to find the match.
The first thing you should ALWAYS do is to make the id in your tables to a PRIMARY key. Then you get that column indexed.
Look at indexes as a way to decrease the number of rows to look through to find the match. You put an index on a seperate column and you should only put it on columns that need it, otherwise your queries will go slower instead.
A basic rule that works very well is that you should put indexes on columns that your “search on” and combine in JOIN’s.
So if we look at the query above, what columns might be interesting to put indexes on?
First of all, make a.id, b.id, c.id PRIMARY keys and they get indexed automaticly.
After that I would set b.a_id and c.b_id as indexes. That is because they are used to combine the tables. If we can decrease the number of rows that needs to be looked through there, the queries will be faster.
The last column that might be interesting is c.name.
A summary:
- Make the id in each table PRIMARY
- Make columns that are “foreign keys” Indexed.
- If you have any other columns that are use in the WHERE clause, make this Indexed aswell.
This is a simplified guide on how to fasten your sql queries, but very good to know the basics.
I
will soon write a tutorial on how to check if your queries are slow or fast, and what to do about it…
Jul/090
What is a Database
Definitions:
A database is a collection of information organized into interrelated tables of data and specifications of data objects.
A table in a relational database is a predefined format of rows and columns that define an entity.
Database tables are composed of individual columns corresponding to the attributes of the object.
In a relational database, a row consists of one set of attributes (or one tuple) corresponding to one instance of the entity that a table schema describes.
Also Known As: Record
A single data item related to a database object. The database schema associates one or more attributes with each database entity.
Also Known As: field, column
A database record consists of one set of tuples for a given relational table. In a relational database, records correspond to rows in each table.
Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables. If you’re familiar with spreadsheets like Microsoft Excel, you’re probably already accustomed to storing data in tabular form. It’s not much of a stretch to make the leap from spreadsheets to databases. Let’s take a look.
Database Tables
Just like Excel tables, database tables consist of columns and rows. Each column contains a different type of attribute and each row corresponds to a single record. For example, imagine that we were building a database table that contained names and telephone numbers. We’d probably set up columns named “FirstName”, “LastName” and “TelephoneNumber.” Then we’d simply start adding rows underneath those columns that contained the data we’re planning to store.
If we were building a table of contact information for our business that has 50 employees, we’d wind up with a table that contains 50 rows.
Databases and Spreadsheets
At this point, you’re probably asking yourself an obvious question – if a database is so much like a spreadsheet, why can’t I just use a spreadsheet? Databases are actually much more powerful than spreadsheets in the way you’re able to manipulate data. Here are just a few of the actions that you can perform on a database that would be difficult if not impossible to perform on a spreadsheet:
* Retrieve all records that match certain criteria
* Update records in bulk
* Cross-reference records in different tables
* Perform complex aggregate calculations
Jun/090
Advanced SQL – Sub-Queries
This advanced SQL tutorial focuses on the design of more complex SQL statements and the strategies available for implementing them, it concentrates on sub queries and joins because they are often inter-changeable, and views because these are often used to hide the complexity of queries involving sub-queries and joins.
As this is an advanced tutorial there is some consideration of performance issues, but this aspect is more thoroughly explored in our series on Oracle performance tuning.
This article talks about the following:
- Introduction to Sub-queries
- Use of sub-queries.
- Nested sub queries
- Correlated sub-queries.
Introduction to Sub-queries
Sub queries are also known as nested queries and are used to answer multi-part questions. Sub queries and joins are often interchangeable and in fact the Oracle optimiser may well treat a query containing a sub-query exactly as if it were a join.
Let’s use a trivial example of finding the names of everybody who works in the same department as a person called Jones to illustrate this point. The SQL could be written using a sub query as follows:
SELECT name FROM emp WHERE dept_no =
(SELECT dept_no FROM emp WHERE name = ‘JONES’)
or as a join statement, like this:-
SELECT e1.name FROM emp e1,emp e2
WHERE e1.dept_no = e2.dept_no AND e2name = ‘JONES’
With a trivial example like this there would probably be very little difference in terms of performance of the SQL for such a simple query, but with more complex queries there could well be performance implications. For this reason it is always worth trying a few variations of the SQL for a query and examining the execution plans before deciding on a particular approach, unless they’re very simple queries.
Learn more about Oracle performance tuning here.
Non Correlated Sub-Queries
There are, in fact, two types of sub query: correlated and non-correlated. The example shown above is a non-correlated sub query. The difference between them is that a correlated sub query refers to a column from a table in the parent query, whereas a non-correlated sub query doesn’t. This means that a non-correlated sub query is executed just once for the whole SQL statement, whereas correlated sub queries are executed once per row in the parent query.
Uses of Sub Queries
The most common use of sub queries is in the WHERE clause of queries to define the limiting condition for the rows returned (i.e. what value(s) the rows must have to be of interest), as in the previous example. However, they can also be used in other parts of the query. Specifically, sub queries can be used:
* to define the limiting conditions for SELECT, UPDATE and DELETE statements in the following clauses:-
- WHERE
- HAVING
- START WITH
Instead of a table name in
- INSERT statements
- UPDATE statements
- DELETE
- statements the FROM clause of SELECT statements
* To define the set of rows to be created in the target table of a CREATE TABLE AS or INSERT INTO sql statement.
* To define the set of rows to be included by a view or a snapshot in a CREATE VIEW or CREATE SNAPSHOT statement.
* To provide the new values for the specified columns in an UPDATE statement
The first example of sub query in SQL shown above, used a simple equality expression as we were interested in only one row, but we can also use the sub query to provide a set of rows.
For example, to find the names of all employees in the same departments as Smith and Jones, we could use the following SQL statement :-
SELECT name FROM emp WHERE dept_no IN
(SELECT dept_no FROM emp WHERE name = ‘JONES’ OR name = ‘SMITH’)
In fact, the original example could also return more than one row from the sub query if there were two or more people that were called Jones working in different departments. In the first example a run-time SQL error would be generated in that case, because the first example, by using ‘=’, specified that the sub query should produce no more than one row (it is perfectly legitimate for a sub query to return no rows).
We can reverse the question to ask for the names of all the employees that are NOT in the same department as Jones, To do this, the sense of the sub query just has to be reversed by prefixing it with ‘NOT’ or ‘!’. Again depending on whether there might be more than one Jones, we would either use ‘IN’ or ‘=’
SELECT name FROM emp WHERE dept_no NOT IN
( SELECT dept_no FROM emp WHERE name = ‘JONES’)
Or
SELECT name FROM emp WHERE dept_no !=
( SELECT dept_no FROM emp WHERE name = ‘JONES’)
Nested Sub-Queries
The SQL syntax allows queries to be nested, meaning that a sub query itself can contain a sub query, enabling very complex queries to be built as there is no syntacttical limit to the level of besting. However, very complex queries should be avoided as they are difficult to understand and to maintain and may not perform that well either.
For example, the SQL statement to find the departments that have employees with a salary higher than the average employee salary could be written as:
SELECT name FROM dept
WHERE id IN
(
SELECT dept_id FROM emp
WHERE sal >
(SELECT avg(sal)FROM emp)
)
Any of the other comparison operators instead of ‘=’ or ‘IN’ such as ‘>’, or ‘<’ can also be used with a sub query.
Sub Queries In The From Clause
The examples so far in this advanced SQL tutorial all had sub queries in the where clause, but sub queries can also be used in the from clause instead of a table name. In these circumstances the sub query acts as if it had been predefined as a view.
For example, the following SQL statement returns the amount of used space, the free space and the total allocated space for all tablespaces in a database.
SELECT ts.tablespace_name
,ROUND(fs.mbytes,2) “Free (Mbytes)”
FROM dba_tablespaces ts
,(
SELECT tablespace_name
,SUM(bytes)/1024/1024 mbytes
FROM dba_free_space
GROUP BY tablespace_name
) fs
WHERE ts.tablespace_name = fs.tablespace_name
Note that the sub query is given an alias so that results can be used in the main body of the query.
Sub Queries That Return No Rows
Up until now the queries shown have all been expected to produce a result, but when creating tables, it can be very useful to write the SQL to use a sub query which will not return any rows – when just the table structure is required and not any of the data.
In the following example we create a copy of the policy table with no rows:
CREATE TABLE new_policy AS
SELECT * from policy WHERE 1=0;
The sub query returns no data but does return the column names and data types to the ‘create table’ statement.
Correlated Sub-Queries
As we’ve seen already, there are two types of sub query: correlated and non-correlated. We’ve already looked at non-correlated sub queries (see advanced SQL tutorial part 1). All of the examples of sub queries up until now have been non-correlated sub queries.
Just like non-correlated sub queries, correlated sub queries are used to answer multi-part questions, but they are most often used to check for existence or absence of matching records in the parent table and the related table in the sub query.
A correlated sub query refers to a column from a table in the parent query. As mentioned in part 1 sub-queries (both correlated and non-correlated) and joins are usually interchangeable. However the SQL may be significantly faster when a correlated sub-query is used as correlated sub queries refer to a column from their parent queries, they are executed once per row in the parent query whereas non-correlated sub queries are executed once for the whole statement.
For example, using the emp and dept tables from before, to find out which departments have no employees assigned to them, we can write the SQL statement in 3 different ways – as a non-correlated sub query, as an outer join, or as a correlated sub-query.
Example 1 – non-correlated sub query
SELECT dept.name FROM dept
WHERE dept.id NOT IN
(
SELECT dept_id
FROM emp
WHERE dept_id IS NOT NULL
)
Example 2- outer join
SELECT dept.name FROM dept,emp
WHERE emp.dept_id (+) = dept.id
Example 3 – correlated sub query
SELECT dept.name FROM dept
WHERE NOT EXISTS (SELECT dept_id
FROM emp
WHERE emp.dept_id = dept.id)
The second example is an outer join SQL statement which may produce differnt reults to the other 2 queries as it returns both matching rows and the non-matching rows on one side of the join. In this case the query would return the names of departments which have no employees assigned to them plus the names of those departments that do have employees assigned to them.
The first and the third SQL statements would produce exactly the same results, but the first would probably be slower than the third if the dept_id column in the emp table were indexed (depending on the sizes of the tables).
The first SQL statement can not use any indexes – the where clause of the sub query is just checking for NOT NULL rows – so a full table scan would be performed. Also the sub query would be executed once for each row in the dept table.
On the other hand, the sub query in the third example can use the index and since only the dept_id is returned by the sub query, there is no need for any subsequent table access. For these reasons, the third query would normally perform better than the first.
As you can see there are nearly always several ways in which the SQL for a query may be written, and it is therefore best to try alternative SQL statements particularly for complex queries before deciding on the preferred one.
Jun/090
Methods of Transferring Databases from Microsoft Access to MySQL
In general, to migrate information from Access to MySQL, you first copy the contents of your tables from an Access database to the MySQL server. (To perform the operation of transferring the tables to MySQL, you can choose from several methods, described below.) If you plan to continue using Access for the interface to your data, the next step after transferring the tables is to replace them with links: Delete the tables stored in your Access database, establish an ODBC connection from Access to the MySQL server, and recreate the tables as links to the MySQL tables. (Naturally, before you delete anything, it’s prudent to make a backup first, just in case something goes wrong.) If you don’t plan to continue using Access, you need not create any links.
Some transfer methods require making an ODBC connection to the MySQL server. For this you can use MySQL Connector/ODBC, the MySQL-specific ODBC driver.
Telling Microsoft Access to Export Its Own Tables
One approach to migrating data from Access to MySQL is to use the export feature provided by Access itself to write out the contents of each table as a text file. Each file then can be loaded into MySQL using a LOAD DATA statement or the mysqlimport command-line utility. Suppose you export a table mytable into a file mytable.txt using CSV (comma separated values) format, and you want to import it into a table named mytable in a MySQL database named mydb. You can invoke the mysql program, then issue a LOAD DATA statement to import the file like this:
C:\> mysql mydb
mysql> LOAD DATA LOCAL INFILE ‘mytable.txt’
-> INTO TABLE mytable
-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
-> LINES TERMINATED BY ‘\r\n’;
Alternatively, use mysqlimport from the command line (type the command all on one line):
C:\> mysqlimport –local –fields-terminated-by=,
–fields-enclosed-by=’”‘
–lines-terminated-by=’\r\n’
mydb mytable.txt
If you need to provide connection parameters such as the hostname, user name, or password, list them on the mysql or mysqlimport command line before the database name:
C:\> mysqlimport –local –fields-terminated-by=,
–fields-enclosed-by=’”‘
–lines-terminated-by=’\r\n’
-h some_host -p -u some_user
mydb mytable.txt
The advantage of this approach is that it requires no special conversion tools. It can be used to produce data files even on machines that have no MySQL support. (If you don’t have the MySQL client programs installed on your Access machine, create the data files, then copy them to another machine where the MySQL programs are installed and load the files into MySQL from there.) The disadvantage is that the MySQL tables must already exist before you can load data into them, so you must issue the appropriate CREATE TABLE statements yourself. For the example just shown, that means you must already have created the table mytable in the mydb database before using either LOAD DATA or mysqlimport.
Converters That Generate Intermediate Files
A second approach to data transfer is to use a converter that reads an Access table and produces from it one or more files containing SQL statements that create the table for you and load data into it. Then you execute the intermediate SQL file or files using the mysql program. Several free converters that work like this are available, each of which takes the form of an Access module:
* exportsql.txt
Works with Access95, Access97, Access2000. Exports all tables in a database, producing one file containing DROP TABLE statements (in case you want to remove MySQL tables created during an earlier data transfer exercise) and another file containing CREATE TABLE and INSERT statements for creating and loading the tables. The files are written to the C:\TEMP directory.
* access_to_mysql.txt
Exports all tables in a database into a file C:\TEMP\mysqldump.txt containing DROP TABLE, CREATE TABLE, and INSERT statements to drop any existing MySQL tables and recreate them. Less sophisticated than exportsql.txt in terms of type conversion and handling of special characters.
* mdb2sql.bas
Access97 only. Exports selected tables to files in a directory of your choosing. Writes a data file for each selected table, plus one SQL script containing CREATE TABLE statements for creating the tables and LOAD DATA statements for importing the data files into them.
Near the beginning of the source code for each of these converters, you’ll find instructions that you should read, because the details of the process for generating the SQL and data files are converter-specific. Also, be sure to note any prerequisites that must be satisfied before using the converters. These include the following:
* Both exportsql.txt and access_to_mysql.txt expect to write files to the C:\TEMP directory, so you must create that directory if it doesn’t exist:
C:\> mkdir C:\TEMP
Alternatively, you can modify the module source so that it writes files to another existing directory.
* If you want to use exportsql.txt to convert Access2000 tables, you need to enable support for the DAO (Data Access Objects) interface. From Access, go into the Visual Basic editor, select the Tools >> References menu option, then enable the “Microsoft DAO 3.6 Object Library” option in the window that comes up.
* mdb2sql.bas requires that you have Advanced Wizards installed, because it uses the Documenter function included in that Wizard set.
After following the export procedure for a converter that generates intermediate SQL files from Access tables, you’ll end up with one or more files that need to be executed with the mysql program, as follows. Assuming that you want to create tables in a database named mydb, you can execute a SQL file file.sql like this:
C:\> mysql mydb < file.sql
If you need to provide connection parameters, list them on the command line before the database name:
C:\> mysql -h some_host -p -u some_user mydb < file.sql
Converters That Perform Direct Data Transfer
Some conversion tools can transfer data directly from an Access database into MySQL. That is, they create the MySQL tables for you and load the information into them as well. This avoids the need for any intermediate files. On the other hand, such tools require that you be able to connect to the MySQL server from the machine on which your Access information is stored. (This requirement is easily satisfied if you install MySQL on your Access machine.)
Tools that can perform direct data transfer are:
* MyAccess
$30 shareware. (Non-registered copies are fully functional, but an annoyance dialog that must be dismissed pops up every five minutes.) Works with Access97, Access2000. MyAccess is an Access add-in that allows direct transfer when you connect from Access to MySQL over an ODBC connection.
* DBTools
Free. Works with Access97, Access2000. DBTools actually is intended primarily as an application for administering MySQL, but it includes data import capabilities that can be used to read Access databases for transfer to MySQL. (It can also read data from other sources such as Excel spreadsheets, making it particularly useful for transferring to MySQL information that is stored in a variety of formats.) Because DBTools reads Access databases directly, you can use it to migrate Access tables even if you don’t have Access installed locally, as long as you have the database files containing the tables to be transferred. DBTools does not require ODBC.
* MySQLFront
Free. MySQLFront is similar in many ways to DBTools. It can read Access97 and Access2000 files directly. If ODBC is installed, MySQLFront can import information into MySQL from ODBC data sources over the network. (Unfortunately, MySQLFront development has ceased and it is no longer distributed by its author. However, you may be able to find it on alternate download sites by using a search engine.)
As an example how one of these tools works, here’s how you’d use DBTools to perform data transfer from Access to MySQL. Begin by visiting the DBTools download page at http://www.dbtools.com.br/, transferring the installer (a program named setup.exe), and running it. This will install DBTools on your machine.
If you want to transfer Access2000 databases, you need to enable DAO. (If you don’t, DBTools will crash whenever you try to open an Access2000 database.) To turn on DAO, launch DBTools (it will tell you there is no server profile; that’s normal), select the Options >> Preferences menu item, and select the DAO 3.6 option. Then quit and relaunch DBTools, because DAO isn’t actually activated until the next launch after you enable it.
With DBTools running, establish a connection to your MySQL server. (Click the Server icon in the toolbar or use the Server >> Add Server menu item to define a profile for the MySQL server you want to connect to.) You must be connected to the server before you can transfer information; many of the menu items and icons in the tool bar are disabled until you establish a connection, including those related to importing data.
After connecting to MySQL, use the Import Data Wizard to select the Access database file containing the tables you want to transfer. One of the dialogs presented during this process asks you to select the file type for the kind of database you want to use. Select the Access97 type for either Access97 or Access2000 databases.
If you intend to continue using Access after transferring the tables, open the database from Access, delete the tables that you just transferred to MySQL, connect to the MySQL server, and set up links to the tables.
Jun/090
Reasons to Migrate from Microsoft Access to MySQL
Use of MySQL as a storage manager for Access offers several benefits. One is that you can use your information in additional ways when it’s not locked into Access. Other differences pertain more specifically to the case where you intend to continue using Access as the user interface to your information.
Deployment of information. When your information resides in MySQL, you’re free to continue using it from Access if you wish, but a number of other possibilities open up as well. Any kind of MySQL client can use the information, not just Access. This allows your data to be exploited more fully in more contexts, and by more people. For example, other people can use the data through the standard MySQL client programs or from GUI-based applications. Your database also becomes more accessible over the Web. Access now provides some capabilities for making a database available on the Web, but if MySQL manages the database, you have a wider range of options. MySQL integrates easily with Web servers like Apache through any of a number of languages, such as Perl, PHP, Python, Java, and Ruby. This allows you to provide a Web interface to your database with the language of your choice. In addition, the interface can be accessed by browsers on many types of machines, providing a platform-independent entryway to your information. All of these components can be obtained for free–MySQL, Apache, and the languages just mentioned have been released as Open Source. You can also obtain them in packages that include support.
Multiple-user access. Although Access provides some data sharing capabilities, that is not really its strength. It has the feel of a single-user data manager designed for local use. MySQL, on the other hand, easily handles many simultaneous users. It was designed from the ground up to run in a networked environment and to be a multiple-user system that is capable of servicing large numbers of clients.
Management of large databases. MySQL can manage hundreds of megabytes of data, and more. Care to try that with Access?
Security. When Access tables are stored locally, anyone can walk up to your Windows machine, launch Access, and gain access to your tables. It’s possible to assign a database a password, but many people routinely neglect to do so. When your tables are stored in MySQL, the MySQL server manages security. Anyone attempting to access your data must know the proper user name and password for connecting to MySQL.
Backup management. If you work in an organization that supports many Access users, migrating data to MySQL provides a benefit for backups and data integrity. With Access databases centralized in MySQL, they’re all backed up using the regular MySQL backup procedures that already exist at your site. If individual Access users each store their data locally, backup can be more complicated: 50 users means 50 database backups. While some sites address this problem through the use of network backups, others deal with it by making backups the responsibility of individual machine owners–which unfortunately sometimes means no backups at all.
Local disk storage requirements. Local Access database files become smaller, because the contents of tables are not stored internally, they’re stored as links to the MySQL server where the tables reside. This results in reduced local disk usage. And, should you wish to distribute a database, less information need be copied. (Of course, anyone you distribute the database to also must have access to the MySQL server.)
Cost. MySQL can be obtained for free. Access cannot. Providing other means of using your database (such as through a Web interface) can reduce your dependence on proprietary software and lower your software acquisition and licensing costs.
Hardware choices. MySQL runs on several platforms; Access is a single-platform application. If you want to use Access, your choice of hardware is determined for you.
Jun/090
A Few Hints And Tips On Optimizing Your SQL
This was intended to be a list of hints and tips that you might find useful when using SQ,L but a mere list of tips would be of little benefit without the knowledge to make use of them, so we’ve expanded the list to include the explanations to increasing its usefulness and to make it a proper tutorial.
One important point to remember is that Oracle caches the compiled form of SQL and is therefore able to re-use queries which are the same as previously executed queries. This saves the time and resources required to parse the statement and determine the execution plan. How can you do this ?
1. Use Views
Views are a good way to ensure the same query is re-used as much as possible.
Remember that even just changing the case and spacing of the words could prevent a query from being reused. A view is merely a pre-defined query, the text of which is stored in the database. Therefore by using views you are using exactly the same queries and eliminating the re-parsing overhead. As the load on the database increases this re-parsing overhead becomes more and more significant. Materialized views take the concept one stage further by actually running the query and storing the results in a table
2. Use Stored Procedures
Another way is to use stored procedures which are program units that contain both SQL and logic statements and are stored in the database. Oracle allows the use of PL/SQL and Java stored procedures. Stored procedures and views also have the advantage that the queries in the views/stored procedures have to be tuned only once, not in every place where they’re used. Like views, stored procedures also eliminate the overhead of sending the queries from the client to the server as the queries are already on the server.
3. Use Bind Variables
The use of bind variables in queries makes them generic and therefore re-usable.
For example, instead of writing a query like :-
SELECT name,addr FROM custs WHERE id = 12345;
Change it to:-
SELECT name,addr FROM custs WHERE id = <cust_id>;
The first query will only be re-used when you request the details for customer number 12345, whereas the second query will be re-used for any other customer.
4. Use Selective Indexes
Ensure that tables are accessed via selective indexes, unless the table is very small or very large, in which case it may be better not to use the indexes.
If the table were very small it could be cached completely, or all the columns could be indexed which means only the index would have to be read to satisfy any query.
Also make sure that you’re not disabling the use of an index by:-
* using an operator on the column (eg. <indexed_col> + 1);
* the use of hints, if you’re running Oracle(only applies if you’re using the cost based optimizer);
* using NULL and not equal checks. (eg. <indexed_col> <> 12345 ; or <indexed_col> IS NULL)
5. Use Full-Table Scans
If the table is very large, depending on how many blocks are read, using an index may remove everything else from the buffer cache and degrade the performance of all other queries. In which case a full-table scan is better – only the last few blocks read are kept in the buffer cache.
6. Optimize Joins
* All other things being equal, the driving table is the one listed LAST in the FROM clause, when using the rule-based optimizer. Changing the order of the columns in the join condition does not change which table is used as the driving table. Choose the driving table carefully to ensure the minimum number of rows are returned.
When using the cost-based optimizer, ensure that all the tables in the join have been analyzed (ask your dba), if they haven’t, this may well cause poor performance. The most usual way to optimize queries when using the cost-based optimizer is to use hints, which instruct the parser as to which indexes should or should not be used, or which tables should be scanned in full. You can also experiment with the order of the tables in the join.
* Indexes – these can still be used even if the where clause contains a “like” condition but not if there is a “not like” condition.
* Outer joins – the correct syntax for outer joins using Oracle syntax is:
tab1.col1(+)= tab2.col1
or tab1.col1 = tab2.col1(+)
The bracketed plus sign follows the column of the table which has/may have the missing row.
An alternative is to use the ANSI standard outer join format which has the advantage that it enables you to perform a full outer join in one statement:
tab1.col1 left outer join tab2.col1 (return all rows from tab1)
tab1.col1 right outer join tab2.col1 (return all rows from tab2)
tab1.col1 full outer join tab2.col1 (return all rows from tab1 and tab2)
The final tip for this short tutorial is:
7. Name The Columns In A Query
There are three good reasons why it is better to name the columns in a query rather than to use “select * from …”.
1. Network traffic is reduced. This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column or have in-line clob or blob columns (all of which can be up to 2 Gigabytes in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.
2. The code is easier to understand, which means you need fewer comments!
3. It could save the need for changes in the future. If you are using views, not only might columns be added to or removed from the view, but the order of the columns could well change – in which case using “SELECT *” at best would fetch the wrong data and at worst would fail with an Oracle error which might take a long while to understand.