Enqurious LogoTM

Use coupon code 'ENQSPARKS25' to get 100 credits for FREE

0
0
Days
0
0
Hours
0
0
Minutes
0
0
Seconds
Go back

Optimizing Analytics Queries for FoodWagon

2 Scenarios
2 Hours
project poster
Industry
e-commerce
Skills
data-storage
data-modelling
data-wrangling
Tools
sql

Learning Objectives

Use database monitoring tools to identify high-cost queries.
Analyze execution plans to identify slow operations like table scans, nested loops, and expensive sorts.
Implement indexing strategies to minimize scan operations and improve query performance.
Utilize partitioning techniques to reduce the scope of data scans for large tables.
Rewrite inefficient SQL queries to simplify execution plans.
Leverage Azure SQL Database tools like Query Performance Insights and Dynamic Management Views (DMVs) to monitor and optimize real-time query execution.

Overview

FoodWagon, a flourishing food ordering, and delivery platform, has witnessed remarkable growth, particularly among the bustling millennial population. This surge in popularity has translated to a tenfold increase in order volumes and restaurant partnerships.

Thier current data is being gathered in Azure SQL that includes customers, partners, restaurants, food, orders, and orders_details tables, which are queried frequently for generating insights into delivery performance, customer behavior, and vendor efficiency. However, due to poor query optimization, operations like joins, aggregations, and filtering lead to high execution costs and slow response times.

This project aims to overcome these bottlenecks by implementing advanced query optimization techniques. You will work on improving the performance of analytical queries by restructuring queries, leveraging indexes, using partitioning, and applying best practices in query optimization.

The goal is to ensure that key business reports run efficiently, even for large datasets, enabling real-time insights and decision-making.

Prerequisites

  • Ability to write intermediate to advanced SQL queries, including complex joins, aggregations, CTE & Sub Query
  • Understanding of execution plans and query performance metrics.
  • Basic knowledge of indexing and partitioning concepts.
  • Familiarity with tools like Azure Data Studio or SSMS for database management.
  • Familiarity with relational database design & Modelling