Post reply

Database testing

  • December 1, 2008 at 12:32 am

    #196748

    We have a database where few tables contain millions of records. Now, we have got the job to make changes to the SQL SP's for optimized performance.

    My question is, how we will ensure that the procedures are returning same resultset before and after tuning?

    Sample testing or only rowcount may not give the full proof result.

    Thanks in advance.

  • December 1, 2008 at 4:28 am

    #905476

    Keep a copy of the old proc, under a different name, and you can run them both to check.

    I've often used Excel to compare two resultsets. Put each resultset into one sheet and then use a third sheet with a formula in each cell that check whether or not the cells in the other two sheets are the same or not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2008 at 4:52 am

    #905490

    Thanks GilaMonster for ur valuable advice.

    One query again, is there any tool present for this type of testing?

  • December 1, 2008 at 4:56 am

    #905491

    Because if I have near about 1 million of rows, its difficult to manage by Excel also. Then we need to axe the resultset and test the result snippets seperately ( Matter of time as well as patience :w00t:

  • December 1, 2008 at 5:08 am

    #905505

    I've never used a 3rd party tool. Wouldn't know if one exists.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2008 at 5:14 am

    #905510

    I wouldn't know if a 3rd party tool exists. I've never used one.

    I've done all my testing with either excel or with the checksum/checksum_agg functions

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 1, 2008 at 5:15 am

    #905511

    The only way to can be really sure that the results are the same through-out the dev process , is to create a test-harness and use a product like NUnit / DBUnit to perform unit tests on the procedures, these can be continuously monitored to make sure any changes or integrations continue to pass the tests.

    Though this can take a while to initially setup, it will pay off if you are doing a lot of development in your organisation.

  • December 1, 2008 at 6:45 am

    #905573

    The way I've done it previously is to output the procedure to XML and store it on the side. Then as you make adjustements to the query, you can compare the output over & over. There are any number of tools that will take in XML and run a compare, just look around a bit. Actually, if you installed PowerShell, I'll bet there's a way to automate it through there. Hmm... something to look into if I ever get spare time.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 1, 2008 at 8:35 pm

    #906011

    I store the output in two tables and do a Full Outer Join on their key columns to compare.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 2, 2008 at 12:39 am

    #906055

    Many thanks to all of you. Its really greatful to me.

  • December 2, 2008 at 8:24 am

    #906259

    Maybe I'm way off base, however, stick to what you know...

    What about using "except"? That's what I use to ensure that my results are matching, usually by putting the results into two temp tables (or actual tables) and then running select's on both.

  • December 2, 2008 at 6:07 pm

    #906592

    Yep... that'll work, too! Haven't tested it for performance, though.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 3, 2008 at 2:19 am

    #906686

    Hi Waslay,

    can u give me some details about "EXCEPT"? How r u using it?

  • December 3, 2008 at 11:44 am

    #907062

    Sure thing. When I set it up it usually looks something like this:

    SELECT col1,col2,col3 FROM table1

    EXCEPT

    SELECT col1,col2,col3 FROM table2

    If I'm not mistaken this will give you all the rows in table1 that are not in table2.

    I will then just reverse the two to get what is in one and not the other.

    Hopefully this link will help out a little...

    http://msdn.microsoft.com/en-us/library/ms188055.aspx

  • December 4, 2008 at 12:49 am

    #907394

    Hi wesley,

    Really a great help.

    MAny thanks to u.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply