Menu

Maximizing SQLAlchemy: Database Query Performance

Category Database, Product engineering

A general enterprise data-driven application will have to do a lot of querying and read operation on the database. And if you are making queries through your python app then SQLAlchemy is a great tool for this purpose. A good read on how to improve the performance of SQL code is given here. In this post, we will look at those considerations and assumptions and try to find the best possible balance between the two. Also, we will learn the ways and methods to weigh the various options at our disposal.

Creating a database for the benchmarks

We will be running the measurements on a MySQL/MariaDB database. So, first of al, let’s create a database `testing` for our testing purposes.

 ➜ mysql -u root -p
 Enter password:
 Welcome to the MariaDB monitor. Commands end with ; or \g.
 Your MariaDB connection id is 5
 Server version: 10.1.24-MariaDB MariaDB Server
  
 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
  
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
 MariaDB [(none)]>
 MariaDB [(none)]> create database testing;
 Query OK, 1 row affected (0.00 sec)
  
 MariaDB [(none)]> use testing;
 Database changed
 MariaDB [testing]>
 MariaDB [testing]>
view rawcreate_database_mysql.bash hosted with ❤ by GitHub

Next, we will create a transactions table where we will have some dummy columns. We will populate those columns with some dummy data.

 CREATE TABLE transactions (
 id int NOT NULL PRIMARY KEY,
 name varchar(255),
 description varchar(255),
 country_name varchar(255),
 city_name varchar(255),
 cost varchar(255),
 currency varchar(255),
 created_at DATE,
 billing_type varchar(255),
 language varchar(255),
 operating_system varchar(255)
 );
view rawcreate_table.sql hosted with ❤ by GitHub

This will create a database with the required columns. We will now fill the rows and columns with some dummy data. To generate the dummy data we are going to use faker. Using faker we will generate fake data for the database which will be representative of real datasets.
The code that is used for this purpose is shown below. First, we will call all the dependencies in the script.

 from datetime import datetime
 import random
  
 import sqlalchemy
 from sqlalchemy import create_engine
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy import Column, Integer, String, DateTime
 from sqlalchemy import Sequence
 from sqlalchemy.orm import sessionmaker
 from elizabeth import Personal
 from faker import Faker
view rawcreating_tables1.py hosted with ❤ by GitHub

Then we can define the base class that will be used to interface with the database.

 user = 'root'
 pwd = ''
 host = 'localhost'
 db = 'testing'
 mysql_engine = create_engine('mysql://{0}:{1}@{2}/{3}'.format(user, pwd, host, db))
  
 Base = declarative_base()
  
 class Transactions(Base):
 __tablename__ = 'transactions'
  
 id = Column(Integer, primary_key=True)
 name = Column(String)
 description = Column(String)
 country_name = Column(String)
 city_name = Column(String)
 cost = Column(String)
 currency = Column(String)
 created_at = Column(DateTime)
 billing_type = Column(String)
 language = Column(String)
 operating_system = Column(String)
  
 def __repr__(self):
 """How the class Transactions is shown.
  
  :returns: return the string with the id and the name.
  
  """
 pass
view rawcreating_tables2.py hosted with ❤ by GitHub

And then we will leverage faker and create 50000 rows of some fake data.

 myFactory = Faker()
  
  
 Session = sessionmaker(bind=mysql_engine)
 session = Session()
  
 billing_type_list = ['cheque', 'cash', 'credit', 'debit', 'e-wallet']
 language = ['English', 'Bengali', 'Kannada']
 operating_system = 'linux'
  
 for i in range(60000, 1000000):
  
 transaction = Transactions(
 id = int(i),
 name = myFactory.name(),
 description = myFactory.text(),
 country_name = myFactory.country(),
 city_name = myFactory.city(),
 cost = str(myFactory.random_digit_not_null()),
 currency = myFactory.currency_code(),
 created_at = myFactory.date_time_between(start_date="-30y", end_date="now", tzinfo=None),
 billing_type = random.choice(billing_type_list),
 language = random.choice(language),
 operating_system = operating_system
 )
  
 session.add(transaction)
  
 session.commit()
view rawcreating_tables3.py hosted with ❤ by GitHub

If you want the whole script, you can click on the link here. Running this code will create a database of 50000 rows in your database.
Below are small samples of the database which has been reduced for reading purposes.

https://gist.githubusercontent.com/infinite-Joy/04dde9149aa82f4188a71c821dcaec0a

As we will be focusing on the date, we will have the database show us some sample datetimes as well.

 MariaDB [testing]> select name, billing_type, created_at from transactions limit 3;
 +------------------+--------------+------------+
 | name | billing_type | created_at |
 +------------------+--------------+------------+
 | Henry Smith | cash | 2015-05-11 |
 | Brandon Williams | debit | 2000-04-14 |
 | Natalie Smith | debit | 2007-09-02 |
 +------------------+--------------+------------+
 3 rows in set (0.00 sec)
view rawdb2.bash hosted with ❤ by GitHub

Querying the database using SQLAlchemy

Now we will bring SQLAlchemy into the picture, query the DB and filter it according to the date. The code to do this is below. The profiled function is taken from SQLAlchemy documentation with some modifications for python3.

 import cProfile
 from io import StringIO
 import pstats
 import contextlib
 import time
  
 import sqlalchemy
 from sqlalchemy import create_engine
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy import Column, Integer, String, DateTime
 from sqlalchemy import Date, cast
 from sqlalchemy.orm import sessionmaker
  
  
 @contextlib.contextmanager
 def profiled():
 pr = cProfile.Profile()
 pr.enable()
 yield
 pr.disable()
 s = StringIO()
 ps = pstats.Stats(pr, stream=s).sort_stats('cumulative')
 ps.print_stats()
 # uncomment this to see who's calling what
 # ps.print_callers()
 print(s.getvalue())
  
  
 user = 'root'
 pwd = ''
 host = 'localhost'
 db = 'testing'
  
 Base = declarative_base()
  
 class Transactions(Base):
 __tablename__ = 'transactions'
  
 id = Column(Integer, primary_key=True)
 name = Column(String)
 description = Column(String)
 country_name = Column(String)
 city_name = Column(String)
 cost = Column(String)
 currency = Column(String)
 created_at = Column(DateTime)
 billing_type = Column(String)
 language = Column(String)
 operating_system = Column(String)
  
 def __repr__(self):
 """How the class Transactions is shown.
  
  :returns: return the string with the id and the name.
  
  """
 return "Transaction(id=%s, name=%s)" % (self.id, self.name)
  
  
 def transactions_filtered_by_querying():
 with profiled():
 mysql_engine = create_engine('mysql://{0}:{1}@{2}/{3}'.format(user, pwd, host, db))
 Session = sessionmaker(bind=mysql_engine)
 session = Session()
  
 transactions = (
 session.query(Transactions)
 .filter(
 cast(Transactions.created_at, Date) == '2007-09-02'
 )
 .all()
 )
 return [(t.id, t.name, t.billing_type) for t in transactions]
  
  
 transactions_filtered_by_querying()
view rawquerying1.py hosted with ❤ by GitHub

This is basically passing the query to the database and letting the database do the filtering for us. Running the above code gives us the following result.

➜ python test_on_different_methods.py
8092 function calls (7813 primitive calls) in 0.078 seconds

So the query completes in 0.078 seconds. What if we try to do the same thing by querying everything and then filtering the result in the code. Common sense tells us that this should take time as databases are generally optimized for querying and filtering. Also passing a lot of data over the network will take a lot of time as opposed to having the database do the operations and passing only the relevant data over the network. Let’s take a look at the code that will be needed to make this test.

 def transactions_filtered_in_python_code():
 with profiled():
 mysql_engine = create_engine('mysql://{0}:{1}@{2}/{3}'.format(user, pwd, host, db))
 Session = sessionmaker(bind=mysql_engine)
 session = Session()
  
 transactions = session.query(Transactions).all()
 return [(t.id, t.name, t.billing_type) for t in transactions if t.created_at == '2015-05-11' ]
  
  
 transactions_filtered_in_python_code()
view rawquerying2.py hosted with ❤ by GitHub

Executing the above code will give us the following result.

➜ python test_on_different_methods.py
1624078 function calls (1623417 primitive calls) in 1.880 seconds

As you can see this took a lot more time as expected(1.880 secs as compared to 0.078 secs).
So this was expected. Now, do you sometimes doubt that this was just one call so we should obviously be deferring the filtering to the database? What if we have to make multiple filtering on the same data. Surely it would be better to make just one call, get all of the data and then do all the filtering in the code. Let’s check that hypothesis. To do this, we will take the help of the code that is there below.

 def timing_function(some_function):
  
 """
  Outputs the time a function takes
  to execute.
  """
  
 def wrapper(*args, **kwargs):
 t1 = time.time()
 some_function(*args, **kwargs)
 t2 = time.time()
 print("Time it took to run the function: " + str((t2 - t1)) + "\n")
 return wrapper
  
  
 @timing_function
 def transactions_filtered_by_querying_multiple_calls(calls):
 for _ in range(calls):
 mysql_engine = create_engine('mysql://{0}:{1}@{2}/{3}'.format(user, pwd, host, db))
 Session = sessionmaker(bind=mysql_engine)
 session = Session()
  
 transactions = (
 session.query(Transactions)
 .filter(
 cast(Transactions.created_at, Date) == '2007-09-02'
 )
 .all()
 )
 [(t.id, t.name, t.billing_type) for t in transactions]
  
  
 @timing_function
 def transactions_filtered_in_python_code_multiple_calls(calls):
 mysql_engine = create_engine('mysql://{0}:{1}@{2}/{3}'.format(user, pwd, host, db))
 Session = sessionmaker(bind=mysql_engine)
 session = Session()
  
 transactions = session.query(Transactions).all()
  
 for _ in range(calls):
 [(t.id, t.name, t.billing_type) for t in transactions if t.created_at == '2015-05-11' ]
view rawquerying3.py hosted with ❤ by GitHub

So running the above code on different amount of calls gives us the following numbers. The numbers are in absolution time in seconds.

The whole code can be checked out here. We can see that the results in both the cases are quite close (except the first result) and they are quite linear. The last result for the `transactions_filtered_by_querying_multiple_calls` failed with connection error showing that this is the upper bound with the number of connections that we can have with the SQL database. So we can see that for a small number of queries SQLAlchemy wins hands down. While for a large number of queries they are almost the same. Only when we reach the need for having around 400 connections, then we need to give this a second thought. Probably then the database should be changed and an in-memory database like Redis should be used.
So the conclusion that we should try to make the number of calls as less as possible and then defer the general processing like filtering, sorting to the database. This should help us in getting a high amount of performance from the code.

Recommended Reading

Explore our insightful articles, whitepapers, and case studies that delve deeper into the latest industry trends, best practices, and success stories. Gain valuable knowledge and stay informed about the ever-evolving landscape of digital transformation.

Navigating The Two Major Data Trends in 2024

As the data landscape continues to evolve rapidly, businesses are compelled to stay abreast of emerging trends to maintain competitiveness. In the year 2024, two prominent trends are poised to redefine data analytics: the proliferation of Generative AI and the adoption of modern data contracts. These trends not only reshape how organizations utilize data but also underscore the importance of ethical considerations and robust governance in data management. This article explores these trends in-depth, providing insights into effective strategies for implementation and the implications for businesses navigating the data landscape.Trend #1: The Ascendancy of Generative AIGenerative AI, characterized by its ability to create new content autonomously, has gained significant traction across industries. The advent of large language models (LLMs) has propelled Generative AI into the mainstream, with tech giants like Microsoft, Google, and Meta integrating Generative AI capabilities into their products. As businesses increasingly rely on AI-driven insights, Generative AI is poised to become an indispensable tool for enhancing productivity and driving innovation.Strategy for Effective Implementation:To leverage Generative AI effectively, businesses must develop a comprehensive strategy tailored to their specific needs and objectives. This strategy should encompass several key components:Identifying suitable use cases:Organizations should identify areas where Generative AI can augment existing processes and generate tangible value. Whether it’s automating content creation, personalizing customer experiences, employee training, or optimizing business operations, identifying the right use cases is essential for maximizing ROI.Comprehensive employee training:Implementing Generative AI requires upskilling employees to ensure they can effectively utilize AI tools while adhering to ethical guidelines and best practices. Training programs should cover topics such as data privacy, bias mitigation, and ethical AI usage to foster a culture of responsible AI adoption.Strong data governance:Robust data governance is critical for ensuring the accuracy, security, and ethical usage of AI-generated insights. Organizations must establish clear guidelines and protocols for data collection, storage, and usage to mitigate risks associated with data misuse or bias.Managing costs and licensing:While Generative AI offers immense potential, it also comes with significant costs, both in terms of technology investments and licensing fees. Organizations must develop a cost-effective strategy for scaling AI initiatives while ensuring compliance with budgetary constraints.Balancing automation and human judgment:While AI-driven insights can enhance decision-making processes, it’s essential to strike a balance between automation and human judgment. Human oversight is crucial for interpreting AI-generated insights, identifying biases, and ensuring ethical decision-making.Ethical considerations:As AI becomes increasingly integrated into business operations, organizations must prioritize ethical considerations and accountability. This includes addressing issues related to data privacy, algorithmic bias, and the potential societal impact of AI-driven decisions.Trend #2: Adoption of Modern Data ContractsModern data contracts have emerged as a solution to streamline data usage and sharing, effectively addressing the challenges associated with broken data integrations and communication gaps between application and analytics teams.Structured Data Interactions:Modern data contracts represent a paradigm shift in how organizations manage data interactions. Unlike traditional contracts, which are static and cumbersome to maintain, modern data contracts are dynamic agreements that evolve with changing data requirements and business needs.Integration into workflows:By integrating data contracts into existing workflows and development processes, organizations can ensure seamless data interactions across disparate systems and applications. This integration enables teams to collaborate more effectively, reducing friction and improving data quality and consistency.Implementation Strategies:Implementing modern data contracts requires a strategic approach focused on collaboration, standardization, and automation. Key strategies include:Developing clear standards:Organizations should establish clear standards and guidelines for data contracts, outlining key parameters such as data formats, schemas, and validation rules. These standards help ensure consistency and interoperability across data systems and applications.Instituting change controls:Change management processes are essential for managing versioning and ensuring smooth transitions between data contract iterations. By implementing robust change controls, organizations can minimize disruptions and maintain data integrity throughout the contract lifecycle.Training and tools:Equipping teams with the necessary training and tools is crucial for successful data contract implementation. Training programs should cover topics such as contract management, data governance, and compliance, while tools such as data modeling platforms and contract management software can streamline the contract development and deployment process.As businesses navigate the complexities of the data landscape in 2024, adapting to the rise of Generative AI and modern data contracts is essential for driving innovation and maintaining competitiveness. By developing comprehensive strategies for AI adoption and data governance, organizations can harness the transformative power of Generative AI while ensuring ethical and responsible data usage. Likewise, embracing modern data contracts enables organizations to streamline data interactions, improve collaboration, and enhance data quality and consistency. By embracing these trends and implementing best practices, businesses can unlock new opportunities for growth and success in the digital age.

Learn More >

The Journey from System Admin to DevOps Superstar

According to a report by the Economic Times, when organizations cultivate a better work environment, the overall experience improves exponentially. They find true meaning in their jobs by prioritizing employees’ mettle, exceeding expectations, and work allocation.Employees seek exposure and opportunities in their jobs. By building productivity and customer satisfaction they enhance their portfolio.Radhakrishnan one of our DevOps superstars, has contributed with his service and time for over 8 years. To commemorate this everlasting relationship we got into a candid conversation with him. Here’s what he had to say about his journey before and with Nineleaps.Radhakrishnan is originally from a small town near Bengaluru, Hosur. After completing his MBA, his interest developed in computers and networking. He successfully gained appropriate knowledge by undertaking network courses and embarked on a journey to becoming a system admin. He enjoyed working for various companies as a system admin.Then came Nineleaps which gave new horizons of opportunities to his mettle. When we asked him about his transition from a system admin to a DevOps engineer, he fondly remembered a quote given to him by our CEO on the day of his selection.“You are on the flight now, just fly,” — Divy Shrivastava.And, so he did.Divy’s words of confidence boosted his resolve. The walk towards DevOps became a sprint, as multiple iterations of knowledge and experience suffused him. The arena of his work leaped and much to his admiration, he realized DevOps to be his passion and soul.Right from the get-go, an intensive training regimen, honing his skills, immersing himself in countless hours of study, and shadowing esteemed senior members of our organization he grasped the crucial importance of comprehending tasks and prioritizing them effectively. Driven by an unwavering desire to learn and prove his mettle, his transition from a system admin to a DevOps maestro was seamless. Multiple training sessions helped him get a deeper understanding of internal and external projects as well as the product, giving him never-to-dull confidence.Learning and development, knowledge transfers, and peer learning are certainly at the core of Nineleaps which helped him become the super engineer he is today. These trainings were both from the client’s side as well as in-house learning at Nineleaps.“In my opinion what sets Nineleaps apart is our dynamic and flexible approach to projects, with extensive focus on Agile methodology we are trained and nourished to build quality solutions for our clients, and also are facilitated with high-tech exposure by working with industry giants and rewarded with the utmost respect and growth opportunities.”To understand more closely we asked him about the challenges he faced at times, and according to him, documentation was a challenge. He feels all the work that the employee is doing must be documented and organized in a proper way as it will help them in the future. He also informed about instances where a person working on a specific problem might face similar challenges later in the same week and not be able to recall what the solution was properly, in such cases documenting everything became important. The organization’s culture was very open and asking questions or requesting help was never an issue which facilitated collaboration in resolving such challenges.Nineleaps became the crucible to test his mettle and with each strike of the hammer, a superstar was born.

Learn More >

Performance Testing Trends: Future of Software Optimization

Performance testing is an integral part of the software development lifecycle as it helps determine the scalability, stability, speed, and responsiveness of an application as compared to the workload given. It is not a standalone process and should be run throughout the software development process.It serves the purpose of assessing various aspects of an application’s performance, such as application output, processing speed, data transfer velocity, network bandwidth usage, maximum concurrent users, memory utilization, workload efficiency, and command response times. By evaluating these metrics, performance testers can gain valuable insights into the application’s capabilities and identify any areas that require improvementUsing AI to automate testing:Performance testing encompasses various stages, each posing unique challenges throughout the testing lifecycle. These challenges include test preparation, execution, identifying performance bottlenecks, pinpointing root causes, and implementing effective solutions. AI can help reduce or even eliminate these differences. AI-powered systems can handle the mountains of data collected during performance testing and be able to produce efficient and accurate analyses. AI can also identify the sources of performance slowdowns in complex systems, which can otherwise be tedious to pinpoint. With AI-driven automation, performance testers can streamline the testing process, ultimately saving time and resources while ensuring reliable results.Open Architecture:Performance testing, which evaluates how well a system performs, is undergoing a significant shift away from relying solely on browser-based evaluations. Instead, internet protocols like TCP/IP are being adopted for comprehensive performance monitoring. This approach emphasizes the need for system components to work together harmoniously while assessing their performance individually. The integration of cloud-based environments has become crucial, as cloud computing is an integral part of modern technology infrastructure. Cloud-based environments provide a flexible and reliable platform that enables seamless integration and coordination of various components, ultimately leading to enhanced system performance. It is crucial to prioritize comprehensive performance testing, which involves evaluating individual component performance, managing loads, monitoring in real-time, and debugging, to ensure optimal system performance.Self Service:When adopting the aforementioned trends, it’s essential to consider practical implementation tips for successful outcomes. For instance, performance engineers can use AI-powered tools to analyze performance data more effectively, leading to more accurate and actionable insights. Integrating cloud-based solutions can provide the flexibility and scalability required for modern performance testing demands. As stakeholders implement these trends, the collaboration between development, testing, and IT operations teams becomes crucial for successful integration and improved application performance.SaaS-based Tools:Testers can now easily set up and execute tests at cloud scale within minutes, thanks to the convergence of self-service, cloud-based testing, SaaS, and open architecture. Unlike older desktop-based tools that demand extensive setup, the emerging tools simplify the process with just a few clicks. Furthermore, these modern technologies offer seamless interoperability, significantly enhancing performance capabilities.Changing Requirements:In classic app testing, testers had to make educated guesses about the software’s use and create requirements and service-level agreements accordingly. However, in DevOps-oriented environments, performance requirements are seen as dynamic and evolving. Traditional requirements are now driven by complex use cases, accommodating different user experiences across various devices and locations. Performance engineering plays a critical role in continuously monitoring systems and proactively identifying and resolving issues before they can negatively impact customer retention or sales.Sentiment analysis:Monitoring production provides insight into server response times but does not capture the true customer experience. Synthetic transactions, on the other hand, simulate real user actions in production continuously. They can range from basic interactions like logging into an e-commerce site and adding products to a cart, to more complex transactions that track performance end to end without actually completing real orders or charging credit cards. Tracking the actual user experience is crucial for identifying bottlenecks, delays, and errors in real-time, as some issues may go unreported by users. Sentiment analysis is a powerful technology that evaluates customer responses based on emotions, providing valuable insights from customers’ reactions expressed in plain text and assigning numerical sentiment scores.Chaos Testing:Chaos testing is a disciplined methodology that proactively simulates and identifies failures in a system to prevent unplanned downtime and ensure a positive user experience. By understanding how the application responds to failures in various parts of the architecture, chaos testing helps uncover uncertainties in the production environment. The main objective is to assess the system’s behavior in the event of failures and identify potential issues. For instance, if one web service experiences downtime, chaos testing ensures that the entire infrastructure does not collapse. This approach helps identify system weaknesses and addresses them before reaching the production stage.Conclusion:As software development continues to evolve, performance testing must keep pace with emerging trends and technologies. By leveraging AI-driven automation, open architecture with cloud integration, and practical implementation tips, stakeholders can optimize their performance testing processes to deliver high-performing and responsive software applications. Real-world examples and a focus on key performance metrics ensure that these trends are not only understood but effectively implemented to achieve the desired outcomes. Embracing these trends empowers software development teams to elevate the user experience, enhance customer satisfaction, and drive business success.

Learn More >

Ready to embark on a transformative journey? Connect with our experts and fuel your growth today!

Explore Now