Overview
Being a long timer RDBMS user, comparing alternative ways of working with records always fascinates me. This post compares performance of Microsoft SQLServer and Python Pandas dataframes for a certain use case.
The use case
The system consists of two datasets, one dataset contain list of child items that should exist and the other dataset contains list of child items that actually exist. Both datasets record the parent item name and the child items names belonging to each parent.
The raw dataset
| ParentName | ChildName | EntryDate | ExpectedChildrenCount | ActionType |
|---|---|---|---|---|
| Parent0 | 2025-09-06 22:48:00.256 | 1000 | FirstEntry | |
| Parent0 | Parent0-child0 | 2025-09-06 22:48:00.256 | Child | |
| Parent0 | Parent0-child1 | 2025-09-06 22:48:00.256 | , | Child |
There are 5 million rows for child entries and 5000 rows for parent entries in this file (generate_raw/rawdata.csv).
The processed dataset
| ParentName | ChildName | EntryDate |
|---|---|---|
| Parent0 | Parent0-child0 | 2025-09-06 22:48:00.256 |
| Parent0 | Parent0-child1 | 2025-09-06 22:48:00.256 |
There are 5 million rows in this file (generate_processed/processed.csv).
Using Pandas
The tools/pandas_processor.py executes the following:
- Loading the csv datasets. This step loads the rawdata.csv (312 MBs) & rawdata.csv (277 MBs).
- Filtering the raw records for ActionType = "First" & extract the "ParentName" and "ExpectedChildrenCount" fields.
- Grouping records in the processes csv by ParentName and counting the number children.
- Joining '2' and '3'.
- Listing raw records where ActionType = "Child" which do not exist in the processed csv records.
Executing all the steps took 5.4 seconds & consumed 1.600 GB of memory.
Using SQL Server
The following steps are involved in getting the results from SQL Server:
- Loading the rawdata.csv file into RawData table using BCP. 5005000 records were copied in 13.172 seconds.
- Loading the processed_data.csv file into the ProcessedData.csv table using BCP. 5000000 rows copied in 10.031 seconds.
- Query for extracting the "ParentName" & "ExpectedChildrenCount" fields from RawData table where ActionType = 'FirstEntry' along with joining with the query which groups the records in ProcessedData table by ParentName took 1 sec.
- Query for selecting those records in RawData where ActionType='Child' but not exist in the ProcessedRecord table took 2 seconds to execute.
Total time taken including data loading from the two csv was: 26.20 seconds and Task Manager reported SQLSVR consuming 16 GB of memory.
Summary
The result are summarized as follows:
| Test | Time taken (sec) | Memory consumed (GBs) |
|---|---|---|
| Pandas | 5.4 | 1.6 |
| SQL Server | 26.20 | 16 |
Conclusion
For simple daily aggregations and reconciliations, using an RDBMs seems to be an overkill. It's slower and takes up a lot more resources. On the positive side, RDBMs' provide seamless transaction support and programming interface from multiple languages which may be necessary for creating reports in web interfaces. An efficient approach could be to store the aggregations into an RDBMs for reporting use cases.