Last updated by .

11 Nov

UVA 893 – Y3K Problem: Handling Python years above 9999

I was working on a problem in UVA online judge where I needed to do a simple data addition. However, the catch was year can go beyond 9999 (which is the limit in python). Below code is the python solution for this problem. I simply divided the date-delta with (1200 years, i.e. 438291 days) and added it separately after the computation. If you have unclear areas, let me know in the comments.

Read More
19 Jul

NetworkX visualization with Graphviz (Example)

NetworkX visualization with Graphviz (Example)

If you are trying to visualize a nice graph with NetworkX, you should be exhausted by now. After all, NetworkX only provides basic functionality for graph visualization. The main goal of NetworkX is to enable graph analysis. For everything other than basic visualization, it’s advisable to use a separate specialized library. In my case, I choose Graphviz. It’s simplistic to get an attractive visualization of a NetworkX graph with Graphviz. I’m taking a gradual start, but you may skip to “NetworkX with Graphviz” directly.

Read More
27 Jan

What-if Analysis with SQL server (Hypothetical Indexes) – Using python

What-if Analysis with SQL server (Hypothetical Indexes) – Using python

If you are a Database administrator or a developer working with a transaction database, you might have come across this problem

“Is it worthy to build that index?”

Exact answer for that question is only known once you build it. However, luckily SQL server provides you with functionality to check the workload performance under hypothetical indexes (without actually creating them)

You can find more information about hypothetical indexes here.

I will just provide you with a simple python code that will help you with the hypothetical index creation. Example code will compose of 3 parts

  1. Index creation
  2. Enabling the index (unlike the normal indexes you need to enable them before using)
  3. Executing the query under the hypothetical index

Index creation

def hyp_create_index_v2(connection, schema_name, tbl_name, col_names, idx_name, include_cols=()):
    """
    Create an hypothetical index on the given table

    :param connection: sql_connection
    :param schema_name: name of the database schema
    :param tbl_name: name of the database table
    :param col_names: string list of column names
    :param idx_name: name of the index
    :param include_cols: columns that needed to be added as includes
    """
    query = f"CREATE NONCLUSTERED INDEX {idx_name} ON {schema_name}.{tbl_name} ({', '.join(col_names)}) " \
            f"INCLUDE ({', '.join(include_cols)}) WITH STATISTICS_ONLY = -1"
    cursor = connection.cursor()
    cursor.execute(query)
    connection.commit()
    logging.info(f"Added HYP: {idx_name}")

Enabling the indexes

def hyp_enable_index(connection):
    """
    This enables the hypothetical indexes for the given connection. This will be enabled for a given connection and all
    hypothetical queries must be executed via the same connection
    :param connection: connection for which hypothetical indexes will be enabled
    """
    query = f'''SELECT dbid = Db_id(),
                    objectid = object_id,
                    indid = index_id
                FROM   sys.indexes
                WHERE  is_hypothetical = 1;'''
    cursor = connection.cursor()
    cursor.execute(query)
    result_rows = cursor.fetchall()
    for result_row in result_rows:
        query_2 = f"DBCC AUTOPILOT(0, {result_row[0]}, {result_row[1]}, {result_row[2]})"
        cursor.execute(query_2)

Executing the query

def hyp_execute_query(connection, query):
    """
    This hypothetically executes the given query and return the estimated sub tree cost. If required we can add the
    operation cost as well. However, most of the cases operation cost at the top level is 0.

    :param connection: sql_connection
    :param query: query that need to be executed
    :return: estimated sub tree cost
    """
    hyp_enable_index(connection)
    cursor = connection.cursor()
    cursor.execute("SET AUTOPILOT ON")
    cursor.execute(query)
    stat_xml = cursor.fetchone()[0]
    cursor.execute("SET AUTOPILOT OFF")
    query_plan = QueryPlan(stat_xml)
    return query_plan.estimated_sub_tree_cost, query_plan.index_seeks