Option 1: One VLDB with large tables self contained

Architecture Overview

With this solution, one table — potentially very large — resides within one database on one ASE server. Users access this one table directly to satisfy queries. Database administrators perform maintenance to the large database and large tables as a whole. For very large APL locked tables, reorganization of the tables must be performed with a drop and recreate of the clustered index. In order to accomplish a drop and recreate of the clustered index on the large table, upward of 200% of the table’s size must be available as free space within the database to recreate the clustered index and thus eliminate any fragmentation. For DOL locked tables, reorg rebuild operations must also maintain up to 200% of large table size to complete reorg rebuild operations. dbccs must be performed on the single database or table as a whole. Update statistics must be performed, targeting the specific indexes or columns, table wide. In many instances, the update statistics operations and the reorganization of fragmented data is not possible with large amounts of data due to the maintenance window requirements to perform these operations.

Option 2: Primary database and an archive database for the largest tables

Architecture Overview

With this solution, two databases are created. One database contains a table that holds the data that is used to provide results for a period of time that is considered “current.” In this use case the contents of that table provide results for 85% of the application queries. The second database contains a large table — potentially very large — containing data that is maintained for historical purposes, such as contract requirement, government requirement, etc. Database administrators perform maintenance on the two databases and the two tables as a whole. For the smaller database/table, the response time of the application is of major concern as is any impact by database maintenance activities.

For a very large APL locked table in the larger database, reorganization of the table must be performed with a drop and recreate of the clustered index. In order to accomplish a drop and recreate of the clustered index on the large table, upward of 200% of the table’s size must be available as free space within the database to recreate the clustered index and thus eliminate any fragmentation. Even though the smaller database/table requires the same amount of extra space for recreating the clustered index, the fact that the entire table is locked during the index rebuild will affect the uptime of the application.

For DOL locked tables, reorg rebuild operations must also maintain up to 200% of large table size to complete. dbccs must be performed on both databases and tables as a whole. Update statistics must be performed targeting the specific indexes or columns, table wide. In many instances, the update statistics operations and the reorganization of fragmented data is often not possible with large amounts of data due to the maintenance window requirements to perform these operations.

The performance on the current data database will be affected while the data is copied to the historical database and then dropped from the current database/table.

For queries that need to access data from both the current and the historical tables, the data can be unioned. For those queries where the data is from either one or the other database, a mechanism has to be developed and maintained that tracks where the date ranges occur. This mechanism has to be accessed to determine the database to use to resolve the query each time data is requested. In some rare cases, the request may have to be broken down into two queries to be resolved, particular if the data is being archived at the time of the request.

Option 3: Several small databases, each holding a portion (none overlapping) of the data from the very large tables

Architecture Overview

In this option, smaller tables are created in several smaller databases to handle the data from an otherwise very large table. As the data is loaded into the appropriate table in a monthly database, the other tables are not affected. Only queries accessing the data in that month are blocked from accessing the new data during the load process. As in the previous option, a mechanism has to be developed and maintained in order for the application to determine where to find the requested data. As data is aged and no additional data is added to the older tables, limited maintenance will be necessary against the older static tables and it may be possible to set the older databases as read only. Clustered indexes will only need to be rebuilt against the current data. This reduces the maintenance windows since only one table or database requires maintenance to be performed against it.

One last issue is that the next database/table has to be created by the DBA prior to when it is needed. If the DBA fails to create the database or table, the data load process will fail.

Option 4: One VLDB with a large table, partitioned semantically

Architecture Overview

With ASE 15, this option is now possible. Although the data will be stored in one large table, partitioning allows for maintenance to be performed on only the necessary partitions. Also, the application does not need to be aware of where the data is located — on which database and in which table. The optimizer can eliminate unnecessary access to partitions where the data is not needed to satisfy the request. Since the partition key only needs to know which month is being stored, a materialized computed column can be created that uses only the month part.

Option 1

If database storage isn’t an issue and only a few elements within the XML documents are needed to retrieve the documents, then the easiest way to accomplish the above goal is to create a table with a text datatype to store the XML documents and computed columns based on elements within the XML documents. For example:

create table xml (xml text,
   ID int compute xmlextract('//ns0:geographyID/text()', xml)
      materialized)

By making those columns materialized, they can have functional indexes added on them. This speeds up the ability to find the correct XML documents without having the overhead of indexed “parsed” XML documents.

Option 2

If the number of elements needed to find the XML documents becomes great, the number of materialized columns can be limited to one or two. Then the xmlextract function can be used within the where clause to find the XML documents. For example:

where ID = 100 and
   xmlextract('//ns0:geographyID/text(), xml) = 200

This might create a performance issue if the XML documents are too large. To increase performance, the XML documents would need to be indexed or “parsed” using the xmlparse function.

Option 3

If database storage becomes an issue, an option would be to store the XML documents in a compressed form in an image datatype. This can only be done if the needed elements can be extracted from the XML documents outside of the database and then inserted into separated columns within the table. This gives the same ability to retrieve the XML documents as Option 1, but places the work to extract the elements within the XML documents on the application outside of the database. It also forces the application to compress and uncompress the XML documents.

Option 4

Another way to solve a database sizing issue is to store the XML documents into a file system directory and map the XML documents to a logical proxy table. A view can then be created to eliminate the unnecessary columns from the proxy table and to add additional columns based on the XML document file name and/or data within the XML document.

With this option, users cannot simultaneously insert additional columns for data not contained within the XML document. All the other options would allow descriptive data to be added within the same row as the XML document. This data could then be used to query for the XML document. Within this option, there is no physical table within the database containing the XML document; therefore, descriptive data would have to be contained within its own table and related to the XML document via some ID common to both. The appearance of the XML document through the proxy table and the inserting of this descriptive data cannot be wrapped within a database transaction.

The following is an example of creating a proxy table and view:

create proxy_table _xmlDirectory external directory at
   "/remotebu/disk_backups/xmlDirectory"
create view xmlDirectory
      (ID, code, xml) as
         select convert(integer, substring(filename, 6, 3)),
         convert(varchar(10), xmlextract('//ns0:geographyID/text()',
         content)), content
from _xmlDirectory

Background on Current Architecture

Company Z runs an order processing system. Due to limitations of their physical production facilities, a request was created by management to indefinitely postpone 50 customer orders, beginning with the 50th order beyond the current order. Management believes this arbitrary delay of 50 orders will allow for the facility to catch up with current orders. To identify and prevent the production of the delayed orders, the customer_order date will be future dated to “12/31/3000.” Because the orders table acts as a queue for the processing of orders, as the 50 orders with a future start date are obtained, production at the facility will begin to slow as the new orders cannot begin to process due to the future dated start date associated with the order.

Proposed Solutions

The IT developer assigned to this project at Company Z considers three alternatives in order to accommodate this request. The first is the use of #temp tables in order to isolate the 50 rows that need their order dates updated. With this method, rows are extracted from one or more user tables and from one or more databases into a temporary table in the tempdb database. Within the tempdb database, a user process continues by isolating the 50th through 100th rows, with “set” logic and “select into” along with the creation of additional #temporary tables. Once the 50th through the 100th rows are isolated, updates are applied to the base table(s).

In the second example, the IT developer considers the use of standard pre-ASE 15 cursors. Using this methodology, a process reads 1,000 or more rows into the nonscrollable cursor. Since the developer is only interested in the 50th through the 100th rows of the cursor, additional processing is necessary. In order to target the 50th row, the cursor must fetch and discard the 1st through the 49th cursor rows before obtaining the needed 50th row. Once the 50th row of the cursor is obtained, processing proceeds by updating the base table where the current cursor row relates to the rows in the base table(s).

The third option is only possible with ASE 15.0 and greater. The third option employs the use of a scrollable cursor to directly position the cursor pointer at the 50th row in the cursor. From the 50th row forward, the cursor can traverse forward until all necessary rows are accessed and the corresponding base table is updated.

Option 1

To facilitate this scenario, a temporary table called #order_temp is generated from a select into statement:

-- build the temp table with all orders that have not started
   processing.
select * into #orders_temp
from orders
where order_date > (select max(order_date)
   from orders
   where processed = "Y")
and processed = "N"

Next, the first 50 rows would need to be deleted from the temporary table:

set rowcount 50
delete #orders_temp
go

Next, move the 50 target rows to a new temporary table called #order_temp2:

-- set rowcount remains at 50
select * into #order_temp2
    from #order_temp
go

set rowcount 0     -- rowcount of 50 no longer needed
go

delete #order_temp -- table no longer needed, the 50
go                 -- rows needed are now in table
                   -- #order_temp2

Using the same interim set, update the 50th to 100th rows in the orders table:

update Accounts..orders
set order_date = "12/31/3000"
from orders c,
    #order_temp2 t
where t.order_id = c.order_id

Processing is complete; remove the #temporary table with drop table:

drop table #order_temp2
go

Option 2

For Options 2 and 3, the following diagram represents the rows needed by the cursor.

Cursor CSR1

Declare and open the cursor to process the rows:

declare CSR1 cursor for
        select order_id, order_date
from orders
where order_date > (select max(order_date)
    from orders
    where processed = "Y")
and processed = "N"

open CSR1

Set the cursor rows to 50 in order to fetch the first 50 rows, and discard them:

set cursor rows 50 for CSR1

fetch CSR1

Reset cursor rows to 1 since the rows to process are next:

set cursor rows 1 for CSR1

Fetch the cursor rows into local variables, then use the variables to update the base table within a while loop to perform the update 50 times:

declare @order_id int,
        @order_date datetime,
        @counter int

select @counter = 50

while (@counter > 0)
begin

fetch CSR1
into @order_id, @order_date

update Accounts..orders
set order_date = "12/31/3000"
where order_id = @order_id

select @counter = @counter -1
end

Verify the correct rows were updated:

select * from orders where order_date = "12/31/3000"

Close and deallocate the cursor:

close CSR1
deallocate CSR1

Option 3

Use a scrollable cursor to set the cursor position directly at the 50th row in the result set.

Declare and open the cursor to process the rows:

declare CSR1 scroll cursor for
    select order_id, order_date
from orders
where order_date > (select max(order_date)
    from orders
    where processed = "Y")
and processed = "N"

open CSR1

Fetch the cursor rows into local variables, then use the variables to update the base table within a while loop to perform the update 50 times. Use the fetch orientation of absolute to skip to the 50th cursor row and to control the cursor position.

declare @order_id int,
        @order_date datetime, 
        @counter int
select @counter = 50

while (@counter <= 100)
begin

fetch absolute @counter CSR1
into @order_id, @order_date

update orders
set order_date = "12/31/3000"
where order_id = @order_id

select @counter = @counter + 1
end

Verify the correct rows were updated:

select * from orders where order_date = "12/31/3000"

Close and deallocate the cursor:

close CSR1
deallocate CSR1