There are many techniques for doing quick, full loads of data with Microsoft SQL Server Integration Services (SSIS) for a reporting environment. This one is the quick change artist of the bunch. Our goal is to create a mechanism for doing a full data table load with little to no interruption to production reports that may rely on that table’s data. We’re going to quick load 30,000 or more records of data in the blink of an eye using a very simple, if commonly overlooked, technique. (continue reading…)
Tag: Database
Designing a Reporting Database Environment
As your production data grows, you will ultimately discover a need for a reporting database environment – that is, a database separate from production that allows ad hoc queries and reports to be generated from production data. The benefits of such a database are numerous and should not be overlooked. If your reports are currently sourced directly from a production database, then you should seriously consider this information. This article explores the need for such databases and design considerations for such an environment.
Improved fnSplit Function for SQL Server
Yesterday, I posted a relatively well-known table-value function called fnSplit() for Microsoft SQL Server that splits delimited data from a single field into multiple data rows. Today, we will improve on that function by including an index number column and retaining null entries within the array. (continue reading…)
Improve HP Service Manager Performance with Array Tables
One of the biggest issues with HP Service Manager is the format and storage of arrays as large data types such as CLOB (Oracle) or TEXT (SQL Server) fields. These data types are not indexed and are slow to query. If you want, for example, to locate all records where Frank Jones is a pending change approver, the application needs to examine every pending change request, parse apart the current.pending.groups field, and search for Frank Jones. It might not take a long time to do it once or twice, but your system may contain thousands or more of such requests per day. Configuration Item relationships and the default Approval Inbox data are stored this way. [Note: These last two are resolved in a fresh installation of HP Service Manager 9.20.]
Fortunately for us, this data can easily be relocated out of large data fields and moved into a real, relational table in a form that can be indexed for rapid query. This article explains how.
