When SQL Server Nonclustered Indexes Are Faster Than Clustered Indexes

# -*- coding: utf-8 -*-
"""
@author: TimothyAWiseman

Not using timeit here since we want to clear out the cache before 
every single run.  Given the need to clear out the cache each time
and the desire to not include the time to clear the cache in the measurements
timeit is less than optimal.  Therefore using a timing wrapper instead.

There is more on clearing the cache for SQL Server at:
    http://www.mssqltips.com/sqlservertip/1360/clearing-cache-for-sql-server-performance-testing/
    
Not all of these tests made it into the tip.

"""

#import useful modules
import pyodbc #to connect to SQL Server
import matplotlib.pyplot as plt #graph the results
import numpy as np
import time

##########################################################
#Establish parameters that will be used throughout the running of the script
#These are, in effect, settings.  If this were meant as a complete program
#I would shut these off into a configuration file or make them user 
#selectable.  As a custom, test script it makes sense to just include them here.


#Must configure the conn string for your instance of SQL Server
sqlConnStr = (#Insert your SQL Conn Str Here ) 

#Leaving off the schema from the table names for ease of
#labeling graphs.

tableNames = ['noIndex', 'pkIndex', 'nonclusteredIdx', 'bothIdx']

#########################################################
#Create the utility functions

def time_wrapper(func):
    def wrapper(*arg, **kw):
        start = time.time()
        result = func(*arg, **kw)
        end = time.time()
        return (end - start), result
    return wrapper
    
def clearCache(curs):
    curs.execute('checkpoint')
    curs.execute('dbcc dropcleanbuffers')
    
def getRowCount(curs, tableName):
    """Gets the rowcount of the table named by tableName."""
    sql = """select count(*) 
            from {}""".format(tableName)
    curs.execute(sql)
    return curs.fetchone()
    
def getInfoFromIndex(curs, tableName):
    """Gets information from that is entirely contained within the index
    key.
    
    The where clause is included to reduce the amount of raw data returned to
    keep the execution times reasonable and to prevent the network trips
    from swamping out the execution time of the query on the server.
    """
    sql = """select n, singlechar
            from {}
            where n%1000 = 0""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()
    
def getInfoOutsideIndex(curs, tableName):
    """This pulls in a column that requires information that is not in the 
    index."""
    
    sql = """select n, singleChar, bigIntData, decimalData
            from {}
            where n%1000 = 0""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()
    
def getTop100(curs, tableName):
    """This pulls in a column that requires information that is not in the 
    index."""
    
    sql = """select top 100 *
            from {}""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()
    
def getTop100Order(curs, tableName):
    """This pulls in a column that requires information that is not in the 
    index."""
    
    sql = """select top 100 *
            from {}
            order by n""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()
    
    
def getTop100OrderDesc(curs, tableName):
    """This pulls in a column that requires information that is not in the 
    index."""
    
    sql = """select top 100 *
            from {}
            order by n desc""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()
    
    
#Originally I tried to do a lookup for just one value, but the results
#were essentially random as other factors swamped out the time for SQL to 
#execute, and most results were essentially 0.  This worked better,
#but I found I needed to do more reps to get anything reliable.   Also,
#this comes across best if the no index table is left out since it is
#so dramatically slower than any of the others. 
def specificValueLookups(curs, tableName):
    """Looks for just one row"""
    sql = """select n, singleChar, stringData, bigIntData, decimalData
            from {}
            where n in (1234, 456, 789, 1, 101112, 2, 131415, 5)""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()
    
def getRangeIndexOnly(curs, tableName):
    """Gets a range looking for only values that are part of the index.
    Expect this to favor the nonclustered index."""
    sql = """select n, singleChar
            from {}
            where n between 100 and 20000
            and singleChar between 'A' and 'Z'""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()    
    
def getRangeAllValues(curs, tableName):
    """Gets a range looking for only values that are part of the index.
    Expect this to favor the nonclustered index."""
    sql = """select *
            from {}
            where n between 100 and 20000
            and singleChar between 'A' and 'Z'""".format(tableName)
    curs.execute(sql)
    return curs.fetchall()  
    
    
def makeGraphForTimes(funcToTime, curs, tableNames, graphFileName, graphTitle='', numReps = 6):
    """Times the repeated iteration of the function and makes a graph out of it.
    The function needs to take the paramaters (cursor, tableName) or this will generate an error."""
    timedFunc = time_wrapper(funcToTime)
    resultsDict = {}
    for tableName in tableNames: 
        resultsDict[tableName] = []
        for i in range(numReps):
            clearCache(curs)
            thisIterTime, result = timedFunc(curs, tableName)
            resultsDict[tableName].append(thisIterTime)
    #print resultsDict #used for testing
    avgs = {x: np.mean(resultsDict[x]) for x in resultsDict}
    plt.figure()
    width = .6
    plt.bar(np.arange(len(avgs)), avgs.values(), align = 'center')
    plt.xticks(np.arange(len(avgs) + width/2), avgs.keys(), rotation = 17, size = 'small')
    plt.ylabel('Time in Seconds')
    plt.title(graphTitle)
    plt.savefig(graphFileName)
    plt.close('all')
    
    
            


########################################################
#Execute the main script

if __name__ == '__main__':
    #autocommit 
    sqlConn = pyodbc.connect(sqlConnStr, autocommit = True)
    curs = sqlConn.cursor()
    makeGraphForTimes(getRowCount, curs, tableNames, 'RowCountTest.jpg', 'Row Count Execution Time')
    makeGraphForTimes(getInfoFromIndex, curs, tableNames, 'DataFromIndex.jpg', 'Query Covered by Index')
    makeGraphForTimes(getInfoOutsideIndex, curs, tableNames, 'DataOutOfIndex.jpg', 'Query not covered by Index')
    
    #Removing the noIndex for some of these because it makes the graph hard to read
    tnMinusNoIndex = [x for x in tableNames if x != 'noIndex']
    
    
    makeGraphForTimes(getTop100, curs, tnMinusNoIndex, 'Top100.Jpg', 'Top 100 No Conditions', numReps = 20)
    makeGraphForTimes(getTop100Order, curs, tnMinusNoIndex, 'Top100Order.Jpg', 'Top 100 Ordered By', numReps = 20)
    makeGraphForTimes(getTop100OrderDesc, curs, tnMinusNoIndex, 'Top100OrderDesc.Jpg', 'Top 100 Descending', numReps = 20)
    
    makeGraphForTimes(specificValueLookups, curs, tnMinusNoIndex, 'SpecificValueLookup.Jpg', 'Lookup One Row', numReps = 500)    
    makeGraphForTimes(getRangeIndexOnly, curs, tnMinusNoIndex, 'RangeIdxOnly.Jpg', 'Lookup Range for Values in Index', numReps = 50)
    makeGraphForTimes(getRangeAllValues, curs, tnMinusNoIndex, 'RangeAllValues.Jpg', 'Lookup Range for All Values', numReps = 100)