|
|
Once your data model is in a benchmark environment, you need to load sample data to test performance. You can use either of two methods to accomplish this. You can spend money to purchase a good product such as TestBase to load the data. Products like this work wonders on loading any given amount of records in a database. You can also use the free method of generating a CROSS JOIN query, which requires slightly more work, but offers essentially the same result.
As a simple example, I've taken a list of cartoon characters, for which I can create a list of every combination of their first and last names by using a CROSS JOIN. The following query by Neil Boyle (http://www.sqlservercentral.com/columnists/nboyle/) generates a list of the Flintstone cartoon characters:
SELECT * FROM ( SELECT 'Fred' as fName union SELECT 'Wilma' union SELECT 'Barney' union SELECT 'Betty' ) as flintstones_1 CROSS JOIN ( SELECT 'Flintstone' as lName union SELECT 'Rubble' ) as flintstones_2
Although some of the data is bogus, the result looks like this:
fName lName ------ ---------- Barney Flintstone Betty Flintstone Fred Flintstone Wilma Flintstone Barney Rubble Betty Rubble Fred Rubble Wilma Rubble (8 row(s) affected)
To create additional combinations, simply add more names to the first or last name list. If you add an additional last name, you have 12 results.
For a more practical example, I can load some sample data into the authors table of the Pubs database. The following query inserts 150 combinations into the authors table:
INSERT authors SELECT au_id1 + '-' + au_id2 as au_ud, fName, lName, au_id1 + ' 5' + au_id2 as phone, 'Test address for ' + fName + ' ' + lName, 'London', 'UK', '12345', 1 FROM ( SELECT '009' as au_id1, 'Fred' as fName union SELECT '010', 'Wilma' union SELECT '012', 'Barney' union SELECT '013', 'Betty' union SELECT '014', 'Al' union SELECT '015', 'Peggy' union SELECT '016', 'Frasier' union SELECT '017', 'Niles' union SELECT '018', 'Homer' union SELECT '019', 'Marge' union SELECT '020', 'Hawkeye' union SELECT '021', 'Bob' union SELECT '024', 'Sam' union SELECT '025', 'Diane' union SELECT '026', 'Rebecca' ) as test_authors_part_1 CROSS JOIN (SELECT '55-0010' as au_id2, 'Flintstone' as lName union SELECT '55-0021', 'Rubble' union SELECT '55-0022', 'Bundy' union SELECT '55-0023', 'Crane' union SELECT '55-0024', 'Simpson' union SELECT '55-0025', 'Pierce' union SELECT '55-0026', 'Bush' union SELECT '55-0028', 'Malone' union SELECT '55-0029', 'Chambers' union SELECT '55-0030', 'Howe' ) as test_authors_part_2 (1)Finding Duplicate Rows
When you're trying to debug why records won't load because of a primary key constraint, you have to determine where your data is being duplicated. To do this, you can use a very easy query that uses the HAVING clause. Simply include the columns you'd like to check for duplication in your SELECT and GROUP BY statements, as shown here:
SELECT <columns to check> FROM <table name> GROUP BY <columns to check> HAVING count(*) > 1
To check for duplication in the Headline and Lead columns of the Articles table, use the following syntax:
SELECT Headline, Lead FROM Articles GROUP BY Headline, Lead HAVING count(*) > 1
This returns all the duplicate records in your table, returning each duplicated record only one time. You can also figure out how many times the row has been repeated by using the following syntax:
SELECT Headline, Lead, count(*) as Occurrences FROM Articles GROUP BY Headline, Lead HAVING count(*) > 1
If you want to figure out which records are repeated more than once, increase the number 1 to the number of occurrences that is appropriate.
Note | The above query works on SELECT statements that don't contain text, ntext, image, or bit columns, since these items can't be grouped. |
|
|