Toad® Data Point Workbook is a new way of using Toad Data Point that focuses on simplifying the Query-to-Analysis workflow.

Each Toad Data Point Workbook is made up of workflows. Each workflow starts with a SQL query followed by a variety of data analysis steps.

Is there a limit to how much data Toad Workbook supports? Simply put: yes. All applications have a threshold where they no longer work optimally. Toad Workbook has been tested and efficiently supports queries up to 2 million rows. But, what if you want to use more than 2 million rows?

If you need to learn basics on getting started with Toad Data Point Workbook, see this video series on using Toad Data Point Workbook

6 ways to optimize Toad Workbook to work with large result sets

Option 1: Sampling

The first step of every workflow is the result of a query. When writing and testing your query use the sampling option to only pull back the first N rows (1000 by default). The screenshot below shows where to set your sampling values.

Geek tip:Pulling millions of rows from the database to your computer can take a lot of time. Each correction to the query requires a new execution and validation check. This process of tweaking the query and validating the results will take less time when limiting the rows. When you are satisfied with the sampling result you can change the row limit to retrieve all rows.

Option 2: Limit columns

When writing a SQL query, you might not know which columns you will need for analysis, so it feels easier to select all columns in your query. But this is where optimization begins. Only select the columns you know you need. Using sampling (see option 1) you can add steps to your workflow, such as pivot grids, to determine the exact columns you need. Below is an example of a table with too many columns selected vs the same table with using fewer columns.

Geek tip: Computer efficiency is mainly about use of RAM and your disk. A result set takes up RAM and disk space. The size is determined by the row length times number of rows. The row length is determined by the number of columns and length of space of the field definition. The type of content does not matter, the field definition does. For example, a result set containing 1 row and 100 columns defined as VarChar(2000) will take up more space than a result set of 100 rows of 1 column defined as a number.

Limit Columns

Option 3: Filter

Adding a filter to your SQL where condition will limit the number of rows returned. The Query Builder has the most common date ranges that you can use already built in. Bind variables are also available for reuse of the same query. The screenshot below shows the Quest Builder and the build-in date range filters.

Geek tip: Using bind variables, optimize the SQL in an Oracle database. When Oracle prepares a query for execution it first looks in its cache to see if it has executed it before. If it has, it will use the cached explain plan and execute right away. Bind variables do not change the SQL as the value being bound is done after checking the cache and just prior to execution. This saves time.

Select name from departments where dept = :a <– Here you can change the bind value on every execution without changing the SQL.

Select name from departments where dept = 10; <– These SQL are different and Oracle has to generate different execution plans for each.

Select name from departments where dept = 11;

Filter

Option 4: Save rows to file

When you save your Toad Workbook file, you have the option to save the rows. If you choose not to include the rows, your file will be smaller and it is best when you know you must always refresh your result set.

Choosing to save your rows to the file allows you to pick up where you left off without running the query again.

You can set your preferences in Toad Options to: always save, save when rows are fewer than N, never or ask every time.

Below is the row option page for Toad Workbook which is accessible on the Workbook toolbar.

Geek tip:The rows saved to file are encrypted and secure. Saving your rows to file captures your last work and makes it easy to add new steps such as pivot grid, export to Excel, etc.

Save rows to file

Option 5: Snapshots

A snapshot is a table that is linked to a query. When refreshed, the content of the table is dropped and reloaded by executing the query. Snapshots can be manually refreshed or scheduled to automatically refresh on set intervals. Executing queries against large production tables can take a long time just to get a small subset of rows. With a snapshot you can schedule a table to be refreshed in your local storage database or Toad Intelligence Central. Your Toad Workbook can query the snapshot directly.

The screenshots below show saving to local storage and publishing to Toad Intelligence Central.

Geek tip:Creating a snapshot in local storage or Toad Intelligence Central can only be done in Toad Data Point classic layout. Both Toad Data Point classic layout and Toad Workbook can be run at the same time if you set the option Environment | Allow multiple copies of Toad to run. Scheduling a snapshot to refresh can only be done by Toad Intelligence Central. Snapshots in local storage can be refreshed manually or with a Toad automation script.

snapshotLS

Snapshot TIC

Option 6: Toad views

If you use the same query again and again or use that query to join to other tables, try using a Toad View instead. A Toad View is a SQL query plus database connection that is stored in Toad just like a database view.

Toad Views are available from the Toad View docked window as shown below.

Geek tip:If you do not have privileges to create objects in your database you can use a Toad View. The Toad View definition is stored in a file in your AppData directory so it is limited to your own personal use. Using Toad Views is faster and easier than using a Query Builder or editor file and is accessible from the Toad View docked window. They can be dragged into the Query Builder or SQL Editor just like a database object.

toad view

Summary

Toad Data Point Workbook has been certified to support up to 2 million rows. For more rows use the optimization options above.

Geek tip:For large results we recommend to run Toad with 16 GB RAM and ample disk space (for example: 10 GB free space) on an SSD disk.

How to get the most out of Toad Data Point Workbook

Workbook is a feature in Toad Data Point. Toad Workbook helps you simplify your report-building process, build reusable workflows and run them all using a single button and output to several locations at once. Whether you are currently a Toad Data Point customer or just getting started with our free 30-day trial, learn more and access the Toad Data Point 5.0x User Guide.

If Toad Data Point is helping you access and prepare data for faster business insights, buy it now or contact a sales representative.

I hope this blog is useful to you. If you have any questions, please post questions to the Toad Data Point forum on Toad World.