Testing for Performance
From SQLServerPedia
|
See Also: Main_Page - Code Management - Testing & Debugging In general, code development or testing should never be done on the production server. The reason for this is simple - since there is no "bug-free" code, your program might adversely affect all users currently connected to the production server. However, to get an idea of how your code will perform in the production environment, you might want to test it with some realistic data. Suppose you have written a stored procedure that you are very proud of and it performs great on the test server. Then one day you deploy your application and a few weeks down the road, users aren't as proud of you as you had expected. What happened? As soon as the database accumulated a moderate quantity of data, the queries started to slow down. Some performance decrease is to be expected on a server overloaded with user connections and the multitude of applications that use it. However, there are ways to make your testing more rigorous with a larger amount of sample data than a handful of rows in each table. Granted, you don't have time to collect 200,000 rows of real data, unless it is already in the source systems you're going to use. However, you can populate your test server tables with some fake data to make the performance tests more realistic. Suppose you wanted to deploy the Pubs database and have thousands of authors, publishers, titles and hopefully a lot of sales. Indeed, if you open such an application to the Internet, you better be ready to tune your application performance. The following script will populate the Authors table with the test data. This data won't be pretty, but will give you an idea of how your queries perform in the production environment: DECLARE @counter INT, @random_no INT, @au_id VARCHAR(11), @phone VARCHAR(12), @last_name VARCHAR(40) SET @counter = 0 WHILE @counter < 10000 BEGIN SELECT @random_no = rand(@counter)*1000000000 SELECT @counter = @counter + 1 SELECT @au_id = SUBSTRING(CAST(@random_no AS VARCHAR(11)), 1, 3) + '-' + SUBSTRING(CAST(@random_no AS VARCHAR(11)), 4, 2) + '-' + SUBSTRING(CAST(@random_no AS VARCHAR(11)), 6, 4) SELECT @phone = SUBSTRING(CAST(@random_no AS VARCHAR(11)), 1, 3) + ' ' + SUBSTRING(CAST(@random_no AS VARCHAR(11)), 4, 2) + '1'+ '-' + SUBSTRING(CAST(@random_no AS VARCHAR(11)), 6, 4) SELECT @last_name = 'lastname' + CAST(@counter AS VARCHAR(5)) INSERT authors (au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES (@au_id, @last_name, 'shortfirstname', @phone, '1234 Main Street', 'jacksonville', 'fl', '12345', 1) END This test data won't be very helpful if you wish to have different names, cities and states, etc. It only gives you an advantage of having a larger data volume to test your queries against. |