Performance Gain using Tally Tables- An Alternative to Loops and Cursors in SQL


At some point in our work we use them because they are a basic programming structure. But in SQL queries loops and cursors are not always the best option, these could slow our process. In scenarios where there is few data, everything runs smoothly. But when we have to deal with high volumes of data these can seriously affect the performance.

This post is about a less known alternative of loops in SQL- the TALLY TABLE.

The Tally table are tables that have a single column. In this column numbers are entered in an orderly manner from 0 or 1, to a number that is useful to us. For example: If you work with a routine, you need to go all the count of a column values and the max value of the column is 500 than you should fill 1 to 500, which is the maximum number of values in that column.

 Sample data table 

CREATE TABLE #TallyExampleData
      Name VARCHAR(50) ,
      Location VARCHAR(50) ,
      Hits INT
 Data in example #TallyExampleData

Sql Tally Table


Now lets create the tally table and cross join with #TallyExampleData table which will give us the multiple rows for the number of hits. We could have also used a function like- 

SELECT  *  FROM  #TallyExampleData  CROSS APPLY  dbo.fnMultiRows(Hits) X 

But this function again requires a while loop which I wanted to avoid. 

Solution –

       SELECT   @count = MAX(Hits)
       FROM     #TallyExampleData
       SELECT TOP ( @count )
                N = IDENTITY( INT, 1, 1)
       INTO     #tally
       FROM     master.dbo.syscolumns a
                CROSS JOIN master.dbo.syscolumns b;         
       SELECT   *
       FROM     #TallyExampleData TA
                CROSS JOIN #tally tl
       WHERE    tl.N <= TA.Hits

Tally table Result
In terms of performance prospective tally tables are blazing fast, with another query which I used for some data migration. It took me just 2 m 3 sec to process and insert more than 11 million records where the hits count was near about 500 hits .

Do you have any application for a TALLY TABLE? Put it in the comments and share with others.

You might also like