# SQL Query Performance Optimization
---
## Table (in Excel Sheet format)
| FruitName | Market | weight | size | timestamp |
|-----------|--------|--------|------|-----------|
| Apple | East | 100 | M | 100 |
| Banana | West | 200 | L | 100 |
Note: Capital Size Leter is key
---
### version 1
sheet version:
| **sheet_version** | **timestamp** |
|-------------------|---------------|
| v1 | 100 |
dbKey:
| id | **FruitName** | **Market** |
|----|---------------|------------|
| 1 | Apple | East |
| 2 | Banana | West |
dbData:
| id | key_id | weight | size | timestamp |
|----|--------|--------|------|-----------|
| 10 | 1 | 100 | M | 100 |
| 11 | 2 | 200 | L | 100 |
joined data of version 1:
| id | FruitName | Market | id | key_id | weight | size | timestamp |
|----|-----------|--------|----|--------|--------|------|-----------|
| 1 | Apple | East | 10 | 1 | 100 | M | 100 |
| 2 | Banana | West | 11 | 2 | 200 | L | 100 |
----
### version 2
sheet_version
| **sheet_version** | **timestamp** |
|-------------------|---------------|
| v1 | 100 |
| v2 | 200 |
data table (update Apple.size to `L`)
| id | key_id | weight | size | timestamp |
|----|--------|--------|------|-----------|
| 10 | 1 | 100 | M | 100 |
| 11 | 1 | 100 | L | 200 |
| 12 | 2 | 200 | L | 100 |
joined table:
| id | FruitName | Market | id | key_id | weight | size | timestamp |
|----|-----------|--------|----|--------|--------|------|-----------|
| 1 | Apple | East | 12 | 1 | 100 | L | 200 |
| 2 | Banana | West | 11 | 2 | 200 | L | 100 |
---
Original Query
```sql!
SELECT
dbKey.*, finalDBData.*
FROM
dbKey,
(
SELECT
*,
rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
FROM
dbData where "timestamp" <= 101
) finalDBData
where
rank =1
and finalDBData.key_id = dbKey.id;
```
---
Refactored Query
v2_map:
| **key_id** | **timestamp** |
|------------|---------------|
| 1 | 100 |
| 2 | 200 |
```sql!
WITH map AS (
SELECT
DISTINCT(key_id),
timestamp
FROM (
SELECT
key_id,
timestamp,
rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
FROM
dbData
-- filtering stuff depends on business logic
where key_id in (1,2)
) sub WHERE rank = 1
)
SELECT
dbKey.*, dbData.*
FROM
dbKey
INNER JOIN map m ON m.key_id = dbKey.id
INNER JOIN dbData ON dbData.key_id = m.key_id AND m.timestamp = dbData.timestamp;
```
{"title":"SQL Query","description":"SQL Query","contributors":"[{\"id\":\"7bff8b8c-2096-4393-908e-9d3f7b516e17\",\"add\":3955,\"del\":1157}]"}