SQLite vs Pandas: Performance Benchmarks

This technical article was written for The Data Incubator by Paul Paczuski, a Fellow of our 2016 Spring cohort in New York City who landed a job with our hiring partner, Genentech as a Clinical Data Scientist.

As a data scientist, we all know that unglamorous data manipulation is 90% of the work. Two of the most common data manipulation tools are SQL and pandas. In this blog, we’ll compare the performance of pandas and SQLite, a simple form of SQL favored by Data Scientists.

Let’s find out the tasks at which each of these excel. Below, we compare Python’s pandas to sqlite for some common data analysis operations: sort, select, load, join, filter, and group by.

Note that the axis is logarithmic, so that raw differences are more pronounced.

Analysis details

For the analysis, we ran the six tasks 10 times each, for 5 different sample sizes, for each of 3 programs: pandassqlite, and memory-sqlite (where database is in memory instead of on disk). See below for the definitions of each task.

Our sample data was randomly generated. Here’s what it looks like:

sql_vs_pandas$ head -n 5 data/sample.100.csv


This consists of a random string of 8 characters, a random single character (for the filtering operation), a random integer simulating a year (1900-2000), and a uniform random float value between 10000 and 100000.


sqlite or memory-sqlite is faster for the following tasks:

  • select two columns from data (<.1 millisecond for any data size for sqlitepandas scales with the data, up to just under 0.5 seconds for 10 million records)
  • filter data (>10x-50x faster with sqlite. The difference is more pronounced as data grows in size)
  • sort by single column: pandas is always a bit slower, but this was the closest

pandas is faster for the following tasks:

  • groupby computation of a mean and sum (significantly better for large data, only 2x faster for <10k records)
  • load data from disk (5x faster for >10k records, even better for smaller data)
  • join data (2-5x faster, but slower for smallest dataset of 1000 rows)

Comparing memory-sqlite vs. sqlite, there was no meaningful difference, especially as data size increased.

There is no significant speedup from loading sqlite in its own shell vs. via pandas.

Overall, joining and loading data is the slowest whereas select and filter are generally the fastest. Further, pandas seems to be optimized for group-by operations, where it performs really well (group-by is pandas‘ second fastest operation for larger data).

Note that this analysis assumes you are equally proficient in writing code with both! But these results could encourage you to learn the tool that you are less familiar with, if the performance gains are significant.


All code is on our GitHub page.

Below are the definitions of our six tasks: sort, select, load, join, filter, and group by (see driver/sqlite_driver.py or driver/pandas_driver.py).

sqlite is first, followed by pandas:


def sort(self):
  self._cursor.execute('SELECT * FROM employee ORDER BY name ASC;')

def sort(self):


def select(self):
  self._cursor.execute('SELECT name, dept FROM employee;')

def select(self):
  self.df_employee[["name", "dept"]]


def load(self):
  self._cursor.execute('CREATE TABLE employee (name varchar(255), dept char(1), birth int, salary double);')
  df = pd.read_csv(self.employee_file)
  df.columns = employee_columns
  df.to_sql('employee', self._conn, if_exists='replace')

  self._cursor.execute('CREATE TABLE bonus (name varchar(255), bonus double);')
  df_bonus = pd.read_csv(self.bonus_file)
  df_bonus.columns = bonus_columns
  df_bonus.to_sql('bonus', self._conn, if_exists='replace')

def load(self):
  self.df_employee = pd.read_csv(self.employee_file)
  self.df_employee.columns = employee_columns

  self.df_bonus = pd.read_csv(self.bonus_file)
  self.df_bonus.columns = bonus_columns


def join(self):
  self._cursor.execute('SELECT employee.name, employee.salary + bonus.bonus '
                       'FROM employee INNER JOIN bonus ON employee.name = bonus.name')

def join(self):
  joined = self.df_employee.merge(self.df_bonus, on='name')
  joined['total'] = joined['bonus'] + joined['salary']


def filter(self):
  self._cursor.execute('SELECT * FROM employee WHERE dept = "a";')

def filter(self):
  self.df_employee[self.df_employee['dept'] == 'a']

group by

def groupby(self):
  self._cursor.execute('SELECT avg(birth), sum(salary) FROM employee GROUP BY dept;')

def groupby(self):
  self.df_employee.groupby("dept").agg({'birth': np.mean, 'salary': np.sum})


We used pandas version 0.19.1 and sqlite version 3.13.0. All tests were ran on Digital Ocean Ubuntu 14.04 with 16GB memory and 8 core CPU.


For resource on becoming a data scientist, check out our blog, particular this article on preparing for our free data science fellowship and this one on data manipulating data like a professional data scientist

To learn more how sqlite works, check out this awesome blog series.

Here is a syntax comparison between pandas and sql.


Related Blog Posts

Moving From Mechanical Engineering to Data Science

Moving From Mechanical Engineering to Data Science

Mechanical engineering and data science may appear vastly different on the surface. Mechanical engineers create physical machines, while data scientists deal with abstract concepts like algorithms and machine learning. Nonetheless, transitioning from mechanical engineering to data science is a feasible path, as explained in this blog.

Read More »
Data Engineering Project

What Does a Data Engineering Project Look Like?

It’s time to talk about the different data engineering projects you might work on as you enter the exciting world of data. You can add these projects to your portfolio and show the best ones to future employers. Remember, the world’s most successful engineers all started where you are now.

Read More »
open ai

AI Prompt Examples for Data Scientists to Use in 2023

Artificial intelligence (AI) isn’t going to steal your data scientist job! Instead, AI tools like ChatGPT can automate some of the more mundane tasks in your future career, saving you time and energy. To make life easier, here are some data science prompts to get you started.

Read More »