Step 7: Test the Optimized Design
This topic is shared between Admin Guide and Getting Started Guide (tutorial step 6).
Check query execution times to test your optimized design:
-
Use the vsql
\timing
meta-command to enable the display of query execution time in milliseconds.Execute a SQL sample query script to test your schema and load scripts for errors.
Note: Include a sample of queries your users are likely to run against the database. If you don't have any real queries, just write simple SQL that collects counts on each of your tables. Alternatively, you can skip this step.
-
Execute several ad hoc queries
- Run Administration Tools and select Connect to Database.
-
Use the \i meta-command to execute the query script; for example:
vmartdb=>
\i vmart_query_03.sql
customer_name | annual_income ------------------+--------------- James M. McNulty | 999979 Emily G. Vogel | 999998 (2 rows) Time: First fetch (2 rows): 58.411 ms. All rows formatted: 58.448 ms vmartdb=> \i vmart_query_06.sql store_key | order_number | date_ordered -----------+--------------+-------------- 45 | 202416 | 2004-01-04 113 | 66017 | 2004-01-04 121 | 251417 | 2004-01-04 24 | 250295 | 2004-01-04 9 | 188567 | 2004-01-04 166 | 36008 | 2004-01-04 27 | 150241 | 2004-01-04 148 | 182207 | 2004-01-04 198 | 75716 | 2004-01-04 (9 rows) Time: First fetch (9 rows): 25.342 ms. All rows formatted: 25.383 ms
Once the database is optimized, it should run queries efficiently. If you discover queries that you want to optimize, you can modify and update the design. See Incremental Design in the Administrator's Guide.
Proceed to the optional Step 8.