# Data & Integrations

**Why It Matters:**\
Performance affects every user’s perception of your application. Slow load times, unresponsive interfaces, and long data-fetching delays can lead to user frustration, higher bounce rates, and system inefficiencies. Optimizing app performance improves usability, encourages adoption, and reduces infrastructure costs—especially as the user base scales.

**Efficient Data Management**

**Optimize database queries:**

1. Avoid large joins or nested subqueries—especially in real-time reports.
2. Use pagination and limit clauses for large datasets.

{% tabs %}
{% tab title="✅ More Efficient: JOIN Method" %} <mark style="background-color:blue;">**Finding Top-Performing Employees**</mark>

Let’s solve the following query problem:

*Retrieve all employees who have completed at least one project with a budget over $50,000*

```sql
SELECT e.emp_name
FROM employees e
JOIN projects p ON e.emp_id = p.project_lead_id
WHERE p.project_budget > 50000;
```

**Advantages:**

* Executes in a single, optimized operation.
* Scales better with larger datasets by processing sets instead of rows individually.

**Why this is the better choice:**

JOIN operations allow the database to combine and filter data in one go, significantly reducing overhead. Instead of checking conditions row-by-row like a subquery, the optimizer uses joins to streamline execution.
{% endtab %}

{% tab title="❌ Less Efficient: Subquery Method" %}

```sql
SELECT emp_name
FROM employees
WHERE emp_id IN (
    SELECT project_lead_id
    FROM projects
    WHERE project_budget > 50000
);
```

**Drawbacks:**

* The inner query is evaluated repeatedly for each row in `employees`.
* May lead to unnecessary full scans of the `projects` table.

**Why this is problematic:**

When the `projects` table holds hundreds of thousands of entries, this subquery could strain resources—slowing execution due to repetitive lookups and poor cache utilization.
{% endtab %}
{% endtabs %}

**Optimize API Performance**

1. **Batch requests** instead of making multiple network calls.
2. Use filtered REST endpoints to return only what’s necessary.
3. Monitor latency and throughput using tools like (for pro-dev users):
   * Postman Monitor
   * API Gateway dashboards (AWS/GCP)

{% tabs %}
{% tab title="✅ Optimized (Batching & Filtering)" %}

```javascript
// Single batch endpoint returns required combined data
fetch('/api/user/123/overview?include=activity,notifications');
```

**Backend returns only needed data:**

```json
{
  "user": { "id": 123, "name": "Jane" },
  "activity": [...],
  "notifications": [...]
}
```

#### Tips Applied:

1. **Batch requests**: Combined user, activity, and notifications into one call.
2. **Filtered response**: Only included relevant fields via query param `?include=...`.
   {% endtab %}

{% tab title="❌ Inefficient (Multiple Calls)" %}

```javascript
// Called individually — creates 3 network requests
fetch('/api/user/123');
fetch('/api/user/123/activity');
fetch('/api/user/123/notifications');
```

**Why it's bad:**

* Causes **more round trips** to the server.
* Slower page load times due to **sequential or parallel wait**.
* Increases **server load** and **risk of timeouts**.
  {% endtab %}
  {% endtabs %}

**Background Jobs**

Offload heavy or non-blocking operations (like PDF generation or data export) to background.

{% hint style="success" %}
*Practical Use:* Schedule overnight data aggregation or preloading of reports rather than doing it in real-time
{% endhint %}

**Real-Time Monitoring**

Track key metrics like:

* Response time
* Memory usage
* API failure rates
* Load time by user geography

{% hint style="success" %}
Monitor frontend and backend separately to localize bottlenecks
{% endhint %}

**Scalability**

**Choose the right database for the right workload:**

* Relational DBs (PostgreSQL, MySQL) for structured data and transactions.
* NoSQL DBs (MongoDB, DynamoDB) for flexible schema and high write throughput.
* Time-series DBs (InfluxDB, TimescaleDB) for metrics and logs.

{% tabs %}
{% tab title="Use Relational DB " %}

### E-commerce Application

**E.g.,&#x20;*****PostgreSQL or MySQL***

**Scenario:** Managing orders and payments

* You need to ensure data consistency, enforce relationships between tables (like `users`, `orders`, `products`), and perform complex SQL queries.
* Transactions, foreign keys, and structured schemas are critical for reliable financial records.

**Table Example:**

```sql
Orders Table:
+----------+------------+------------+
| Order ID | User ID    | Total      |
+----------+------------+------------+
| 12345    | 1001       | $249.99    |
+----------+------------+------------+
```

{% hint style="success" %}
When the data need structure and relationships → Use Relational DB
{% endhint %}
{% endtab %}

{% tab title="Use NoSQL DB" %}

#### E.g., *MongoDB or DynamoDB*

**Scenario:** Storing real-time product reviews or user activity logs

* Data varies in structure and changes frequently.
* Requires fast writes, flexible schema, and horizontal scaling.

**Document Example in MongoDB:**

```json
{
  "product_id": "A123",
  "reviews": [
    { "user": "Tom", "rating": 5, "comment": "Great product!" },
    { "user": "Anna", "rating": 4 }
  ]
}
```

{% hint style="success" %}
When the data need speed, flexibility, and scale → Use NoSQL DB
{% endhint %}
{% endtab %}
{% endtabs %}

**Archival strategies:**

* By archiving old data, you can maintain an organized and clutter-free system, making it easier to access and manage the most current and important information.
* Avoid loading historical data unless explicitly requested.

{% tabs %}
{% tab title="Good Approach (Optimized)" %}
**Scenario: A Sales Dashboard App**

Your app shows sales data to regional managers.

By default, the app only loads **the latest 30 days** of sales data.\
If the user selects a filter like **"View past year"**, then and only then the app fetches historical data from the database.
{% endtab %}

{% tab title="Bad Approach (Inefficient)" %}
When a manager opens the dashboard, the app loads **all sales data from the past 5 years**, including data the user may never need.\
Result: The app becomes slow, the backend works harder, and the user just wanted **last month's** performance.
{% endtab %}
{% endtabs %}

**Geographic Scalability**

* Support multi-region and multi-lingual support for users.
* Implement localization support (timezone, currency) if expanding internationally.