testingReflections.com - databases & SQL

A Performance Engineering Story with Database Monitoring

Sun, 24 Jan 2010 19:55:45 +0000

I put my CMG'09 presenatation A Performance Engineering Story with Database Monitoring on my site.

By the way, CMG'10 call for papers and workshops are available, next year it would be a separate subject area for Load Testing.

Testing SCDs

Wed, 01 Apr 2009 16:14:15 +0100

An SCD is a slowly changing dimension in a data warehouse. There are three different forms of SCDs. SCD1 overwrites data, SCD2 maintains historical data and SCD3 maintains some of the data.

SCDs and testing in a data warehouse is likely a topic that you’re either entirely interested in or it’s a tech area off your radar. Over the past few months I’ve been doing some work in a data warehouse again and have at times looked through blogs and forums for information and experiences, and in most cases found little information written from a testing perspective. I’m going to try to share some of my experiences being of course, mindful of client confidentially and steering very wide from any specific data – I’ll make up data if I need to. Testing SCD2s is one topic I’ve looked to hear experiences from other testers but have found little to no shared experiences.

The ripple effects of cascade delete

Mon, 01 Sep 2008 18:59:44 +0100

The topic of a cascade delete came up on a project the other day and I was surprised to find one of the project members wasn’t familiar with the term or the concept. I thought I would share a short lesson and reflect on testing associated with cascade deletes.

A cascade delete is when an on object is deleted in an application and on the backend in a relational database, the delete cascades through the associated tables in the database removing associated data. (And hopefully both removing the appropriate information and leaving the other data.) The concept isn’t difficult to imagine although there are ripples of what takes place, what can go wrong, what could be tested, and how a failed delete can be found.

Linguistic heuristics

Sun, 22 Jun 2008 12:18:35 +0100

Searching. I’m currently testing a product that has a search feature. I’ve tested search functionality before but not a multilingual search engine that utilizes two different search engines based on the language selected. Nor have I previously worked with a search engine that uses stemming and stop words.

I almost didn’t want to write about this – figured I would wait until I resolved my challenges before writing about it – but it occurred to me why? It’s not as though in software testing I haven’t learned that first understanding the complexities of a problem is an essential starting point.

CMG Opens Its Content to the Public

Tue, 12 Feb 2008 19:30:21 +0000

The Computer Measurement Group (CMG) is making its conference proceedings from 1997 through 2005 available to the public. I believe that CMG holds the best practical conference in performance analysis, capacity planning, and related areas. In addition to the areas listed below, I'd definitely add performance testing. Here is the official mail CMG sent:

Reading Schemas

Thu, 28 Feb 2008 15:10:49 +0000

Often the first request I make when starting work on a new application, is to request the database schema. I thought (somehow) that this was a fairly typical tester’s first step but recently working with other testers I came to realize this might not be the case.

I thought I would quickly outline some of the things I learn from reading a database schema from a tester’s perspective.

CMG 2007 Call for Paper

Sun, 11 Mar 2007 15:58:19 +0000

This year performance (load, stress) testing will be a focus track at the CMG 2007 conference. It is the best conference, by my opinion, about performance-related topics. Below is the official text:

The Computer Measurement Group (CMG) calls for papers and presentations for the 33rd International Conference to be held in San Diego, California, December 2nd through 7th, 2007. The 2007 CMG conference will cover load and stress testing, benchmarking, performance optimization, software performance engineering, resource management, capacity analysis, simulation and analytic modeling, and cost management with special emphasis on Virtualization, System Oriented Architecture (SOA), IT Service Management and IT Infrastructure Library (ITIL), and the technology implications of globalization.

connectionstrings.com

Tue, 23 Jan 2007 21:38:38 +0000

connectionstrings.com

If you didn't know about connectionstrings.com, you need to know about connectionstrings.com. Formats for every connection string under the sun apparently...

Using Ruby Multithreading Capabilities for Database Testing

Wed, 20 Jul 2005 12:19:15 +0100

Using Multithreading and ADODB in Ruby

Ruby Scripting Language is emerging as a tester’s language for test automation. Ruby is a very feature reach and powerful language. We had a situation where we need to connect to different databases and fire a SELECT query at same point of time. I tried this various other alternatives, but no results.

Then I saw multithreading support in Ruby and implemented following code using it. While doing this I wanted to access databases using ADO and I could not found help on how to use ADO in Ruby, after trial and error I got the following code working. I am sharing this code so others can use this as a reference for using ADO in Ruby.

Toplink-in-memory: run database tests in memory

Thu, 07 Jul 2005 21:11:14 +0100

Java library to speed up the running of automated tests for projects that use toplink to access databases. It allows you to run the application in memory, rather than against a database.

Thycotic DatabaseScripter: database rollback tool

Mon, 04 Jul 2005 06:49:38 +0100

Thycotic.DatabaseScripter is a simple utility that generates a stored procedure called "SETUP" which will remove all data from your database and then reinsert it in the correct order. It uses the system tables in the database to identify foreign key dependencies and make sure data is manipulated int he appropriate manner.

Simplify Data Layer Unit Testing using Enterprise Services

Thu, 19 May 2005 06:38:26 +0100

Despite all the hype surrounding unit testing and Test Driven Development (TDD) many developers don't see them as useful processes that are applicable to the production of real-world applications. One reason is that once you get down to the intricacies of application development, all of the easy testing you've heard so much about can vanish. You may find yourself writing unit tests that are hard to implement against real code. This is often the case in database testing, where TDD can quickly lose its appeal, and force you to revert your processes to the previous real-world methodologies your team employed. Here, I'll present some techniques to make your testing process easier, even when developing real-world applications, using unit testing.

Extracting Column Names for a Table in SQL as a CSV String

Sun, 20 Feb 2005 22:22:49 +0000

I had a need (creating CSV BCP files) that required knowing the column names of a MS SQL table in the order they were created. Some inspiration from Mark Clerget and a little fooling around with SQL Query Analyzer resulted in the following.

Dynamic SQL INSERT Generator Unleashed!

Thu, 10 Feb 2005 05:59:48 +0000

Mark Clerget has another excellent post on using SQL to generate SQL. This time Mark demonstrates how to generate a series of INSERT statements from an arbitrary table.

So you are probably saying, "Great, so you can generate a CREATE TABLE statement...Whoopee!". Well, if I could help you fill that table or tables using a script that dynamically generates INSERT statements based on the contents of a table, would that be worth something to you? Great! Read further and lets really impress those SQL jockey friends of yours. [Mark Clerget's Blog]

Keeping Databases In-Synch With Your Source Control

Sat, 29 Jan 2005 10:30:04 +0000

Jake Lawlor has a great post on keeping cruft out of your database.

Benefits of Approach: By doing this you get a versioned history of changes to your database. You also get versioned stage directories for dev, test and prod environments that have all the schema and re-runnable database objects at that point in time. These versioned stage directories correlate to a label in VSS. By automating the application of the schema and the dropping and recreating of the re-runnable objects your database will reflect your source control.