Testing stored procedure for accuracy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Testing stored procedure for accuracy

Author

Topic

xoail
Starting Member

9 Posts

Posted - 2008-05-14 : 15:01:46

I am given a task to test a complex stored procedure that has been recently modified for performance. The stored procedure runs 2 processes based on different parameters provided to it in real time. The procedure has been modified to incresease its performance from 22hours to just 1 minute. One of the two processes that this SP handels have been tested and is thus very accurate. The testing of 2nd process is given to me and I am looking for some great help in how to go about testing it step by step. I can provide more info on the SP and how we increased its performance, if you are interested.
I know its a lot to ask but I really need some help. I have limited knowledge in SQL.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 15:15:47

I don't understand what part you need help on. Didn't they show you how to execute the stored procedure in Management Studio? Do you know what parameters to pass it? Do you know what the expected output is based upon the inputs?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

xoail
Starting Member

9 Posts

Posted - 2008-05-14 : 16:32:03

Well, I guess I wasnt clear in my first post. They did give me all the details. But at the end, this SP creates a reporting table that hold more than a billion rows. To verify the data returned in this report is valid and doesnt include unwanted records is very difficult unless there is a mean to it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 16:40:35

If you can get the old version of the stored procedure to output the data into a table and then run the new version but save the data to a different name, you could then query for any differences using T-SQL. There are several threads here and I believe blogs that show how to compare two tables row by row and column by column. It would be very slow to compare due to the number of rows in your table, but at least you'd know if they were identical.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

xoail
Starting Member

9 Posts

Posted - 2008-05-14 : 16:58:21

Thats a very good idea!

I wonder if there is anything fast or easy that I am missing?

pootle_flump

1064 Posts

Posted - 2008-05-15 : 05:52:39

You need to compare the two outputs. There is no faster or easier way.

A real QAD query could be:

SELECT COUNT(*) 
FROM	table_1

SELECT COUNT(*)
FROM
	(
		SELECT * 
		FROM	table_1
		UNION
		SELECT *
		FROM	table_2
	) AS union_der_t

If the two numbers match then the outputs are the same. If they do not then you need to use more sophisticated SQL to first find what does not match and then find what is causing the problem in the stored procedure.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-15 : 06:40:04

quote:
Originally posted by pootle_flump
If the two numbers match then the outputs are the same.

I'm sorry man ut this is just too broad an assumption to be left uncommented. I've done this exersise numerous times and allthough a count in both tables can be a good indicator, more tests are needed. What I have done is to create some different checksums from the data in your tables, i.e if you have a few int-columns in you tables just sum them up and see if the sum matches, or do an average or something, and you need to test more than one column. These tests are usually pretty fast and will give you a good indication if they are identical...

--
Lumbago

pootle_flump

1064 Posts

Posted - 2008-05-15 : 09:26:42

quote:
Originally posted by Lumbago

quote:
Originally posted by pootle_flump
If the two numbers match then the outputs are the same.
I'm sorry man ut this is just too broad an assumption to be left uncommented. I've done this exersise numerous times and allthough a count in both tables can be a good indicator, more tests are needed.

Lumbago - did you read the code? I didn't say count the number of rows in the two tables and compare them. I said count the number of rows in one table then count the number of rows in a UNION of the two tables. For these two numbers to match the contents of the two tables must be identical, not just the number of rows.

pootle_flump

1064 Posts

Posted - 2008-05-15 : 11:49:06

Actually there is an error. It's not that likely but possible. I was trying to simplify something I use whilst accounting for this:

quote:
Originally posted by xoail

I have limited knowledge in SQL.

and cut out some crucial logic.
It can be extended though:

SELECT COUNT(*) 
FROM	table_1

SELECT COUNT(*) 
FROM	table_2

SELECT COUNT(*)
FROM
	(
		SELECT * 
		FROM	table_1
		UNION
		SELECT *
		FROM	table_2
	) AS union_der_t

Compare the three counts and if they all match the tables are identical

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-16 : 02:49:31

You're right, I didn't read the code as thurough (is that how it spells??) as I should have. Running a UNION on two billion-row tables is gonne take some time I presume but that's another discussion

Your method will definetly be a good way to compare two tables that should be the same.

--
Lumbago

pootle_flump

1064 Posts

Posted - 2008-05-16 : 03:44:10

quote:
Originally posted by pootle_flump

A real QAD query

quote:
Originally posted by Lumbago

Running a UNION on two billion-row tables is gonne take some time I presume but that's another discussion Your method will definetly be a good way to compare two tables that should be the same.

I never claimed efficiency just simplicity

. If the tables are really narrow and have clustered indexes it might not be too slow.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources