Materialized Views Sample
This sample demonstrates how materialized views can be used to significantly improve performance when calculating expensive queries. The sample executes a complex query twice: the first time without using a materialized view and the second time using one. The query execution will be much faster the second time because the optimizer takes advantage of the materialized view.
For more information about materialized views, refer to the online documentation: SQL Anywhere User's Guide | Working with Database Objects | Working with views | Working with materialized views.
- SQL Anywhere 10.0
- 1 GB of free disk space for the sample database
You can run this sample under Windows or Linux.
- Shut down any SQL Anywhere databases that are running on your computer.
- Open a command prompt and create a backup of the SQL Anywhere 10 demo database by executing the following:
dbbackup -c "dbf=%SQLANYSAMP10%\demo.db;uid=dba;pwd=sql;eng=demo" "%SQLANYSAMP10%\demobak" -y
dbbackup -c "dbf=$SQLANYSAMP10/demo.db;uid=dba;pwd=sql;eng=demo" "$SQLANYSAMP10/demobak" -y
- Start Interactive SQL by executing the following command:
- When Interactive SQL starts, you are prompted to connect to a database. Select ODBC Data Source name and browse to SQL Anywhere 10 Demo.
- Click OK. The demo database starts.
- We use a stored procedure to insert sample data. Copy the following code and paste it into Interactive SQL.
CREATE PROCEDURE "DBA"."populate"(IN rowinsert INT)
DECLARE i INT;
DECLARE curdate DATE;
DECLARE salesrep INT;
DECLARE j INT;
DECLARE customer INT;
DECLARE lastid INT;
DECLARE line INT;
DECLARE k INT;
DECLARE dummyid INT;
DECLARE rowsin INT;
SET rowsin = rowinsert;
SET customer = 101;
SET salesrep = 9999;
SET i = 1;
SET j= 1;
SET line = 1;
SET curdate = '2001-07-01';
INSERT INTO EMPLOYEES VALUES (salesrep, 902, 'Smith', 'John', 200, '123 West Court Place', 'Waterloo', 'ON','Canada','L3S 345',6472001899,'A','123456789',10000.00,'2006-01-01',null,'1985-05-16',1,1,1,'M');
SET dummyid = 5000;
INSERT INTO EMPLOYEES VALUES (dummyid, 902, 'DUMMY', 'RANDOM', 200, 'RANDOM STREET', 'IA', 'AZ','Canada','L3S 345',1111112222,'A','333333333',10000.00,'2006-01-01',null,'1982-05-16',1,1,1,'M');
WHILE YEAR(curdate) <= 2008 LOOP
WHILE i <= 12 LOOP
WHILE j <= 4 LOOP
INSERT INTO salesorders VALUES (default,customer,curdate,'r1','Eastern',salesrep);
SELECT @@identity INTO lastid;
INSERT INTO salesorderitems VALUES (lastid,1,300,12,curdate);
INSERT INTO salesorderitems VALUES (lastid,2,400,29,curdate);
INSERT INTO salesorderitems VALUES (lastid,3,500,8,curdate);
SET j = j + 1;
SET i = i + j;
SET j = 1;
SET customer = customer + 1;
SET i = 1;
SET customer = 101;
SET curdate = DATEADD (month,1,curdate);
SET k = 1;
WHILE k <= rowsin LOOP
INSERT INTO salesorders VALUES (default,101,'2001-01-01','r1','Central', dummyid);
SELECT @@identity INTO lastid;
INSERT INTO salesorderitems VALUES (lastid,1,700,15,'2001-01-01');
SET k = k + 1;
IF mod( k, 1000 ) = 0 THEN
- Execute the SQL statements by pressing F5. This creates the stored procedure.
- Let's add 500,00 rows using the newly created stored procedure. Execute the following statement (this step may take a few minutes to complete):
- When the statement successfully completes, create a materialized view by executing the following statements (this step may take a few minutes to complete):
CREATE MATERIALIZED VIEW demo AS
SELECT employees.surname, employees.givenname,
SUM(products.unitprice * salesorderitems.quantity) AS TotalSales, SUM(salesorderitems.quantity) AS NumberOfSales
FROM (((salesorders JOIN employees ON
employees.employeeid ) JOIN salesorderitems ON salesorders.id = salesorderitems.id) JOIN products ON products.id = salesorderitems.productid)
GROUP BY employees.surname,
REFRESH MATERIALIZED VIEW demo;
ALTER MATERIALIZED VIEW demo DISABLE USE IN OPTIMIZATION;
- Note that the previous step defined the materialized view, but disabled its use. We will enable its use later on, after we run a query without using the materialized view to see the performance gain.
- Execute the following SQL statement:
SELECT employees.surname, employees.givenname, salesorders.orderdate,
SUM(products.unitprice * salesorderitems.quantity) AS TotalSales,
SUM(salesorderitems.quantity) AS NumberOfSales
FROM (((salesorders JOIN employees ON salesorders.salesrepresentative = employees.employeeid)
JOIN salesorderitems ON salesorders.id = salesorderitems.id)
JOIN products ON products.id = salesorderitems.productid)
WHERE YEAR(orderdate) = 2001 AND MONTH(orderdate) = 7
GROUP BY employees.surname, employees.givenname, salesorders.orderdate;
- This SQL command displays the monthly sales summaries of all the sales people of a particular year. The summaries require information from several base tables and require the use of aggregates. It is an expensive query and may take a while to finish.
- After the command is executed, click the Plan tab at the bottom of Interactive SQL.
Note the Run Time and the graphical plan. The graphical plan shows the use of a lot of tables in executing the query. The original query requires a lot of work by the optimizer because it must build a temporary table using disk space and it must calculate the sums. The results of all these operations are not stored in the database cache and will need to be re-calculated each time the query is executed.
- Let's enable the use of the materialized view we created earlier to enhance performance. Execute the following SQL command:
ALTER MATERIALIZED VIEW demo ENABLE USE IN OPTIMIZATION;
- Execute the SQL command from step 11 again in Interactive SQL and examine the execution plan.
- Note the Run Time; it is much faster compared to the original query. In the graphical plan, the database optimizer went to the materialized view to obtain the information instead of joining all the tables, resulting in faster execution. Even though the query did no look directly for the materialized view from the FROM clause, the optimizer knows that the materialized view contains a more efficient way of obtaining the results, which improves performance.
- This concludes the sample. Shut down Interactive SQL.
To revert the SQL Anywhere 10 demo database back to its original state, perform the following steps:
- Open a command prompt and change to the directory where you installed SQL Anywhere 10 samples (C:\Documents and Settings\All Users\Documents\SQL Anywhere 10\Samples by default). Issue the following command:
dberase -y demo.db
- Copy the database and log files from the demobak directory to the current directory.
- You may now remove the demobak folder and all its contents.