Django应用中Ajax+JSON实现CRUD操作缓慢的优化咨询（7000条记录）

Hey there, let's break down your problem step by step—1.5 minutes for CRUD operations on just 7k records is way slower than it should be, so let's tackle your questions and actionable fixes:

1. Is using dict() causing issues? #

Using dict() itself isn't inherently the problem, but how you're converting data to dictionaries might be a bottleneck:

• If you're looping through model instances and manually building dicts (e.g., { 'id': obj.id, 'name': obj.name } for every record), this adds unnecessary overhead from model instantiation and repetitive field mapping.
• Instead, use your ORM's built-in methods to fetch data directly as dictionaries. For example, in Django:

  # Fast: Directly returns list of dicts without instantiating models
  records = MyModel.objects.filter(...).values()
  

• Avoid using model_instance.__dict__—it includes internal ORM attributes (like _state) that you don't need, adding extra data processing time.

2. Should you switch to REST or another API pattern? #

REST isn't a silver bullet, but a REST API designed for batch operations will drastically cut down on network latency (the biggest culprit for remote database slowness):

• If your current setup sends a separate request for every single create/update/delete, you're paying the cost of network round-trips 7k times. Instead, build endpoints that accept batches of records:
  • POST /api/records/bulk-create (accepts an array of record data)
  • PUT /api/records/bulk-update (accepts array of {id, updated_fields})
  • DELETE /api/records/bulk-delete (accepts array of IDs)
• This reduces 7k HTTP requests to just 1-3, eliminating most of the network delay between your app and the remote DB.

3. Key Optimizations for Views.py #

Batch Database Operations #

Stop executing single-record queries in loops—use your ORM's bulk methods:

# Django example: Bulk create 100 records in one DB call
MyModel.objects.bulk_create([
    MyModel(field1=val1, field2=val2) for val1, val2 in batch_data
])

# Bulk update existing records
MyModel.objects.bulk_update(updated_instances, fields=['field1', 'field2'])

If you're using raw SQL, write batch statements (e.g., INSERT INTO table (col1, col2) VALUES (val1, val2), (val3, val4), ...) instead of multiple INSERT calls.

Fix Database Indexes #

Slow updates/deletes often come from missing indexes on fields used in WHERE clauses. Run EXPLAIN on your update/delete queries to check if the DB is doing a full table scan:

EXPLAIN UPDATE my_table SET status = 'active' WHERE category_id = 5;

If you see type: ALL in the result, add an index on category_id to speed up the query.

Optimize Database Connections #

Remote DBs suffer from connection setup overhead—use a connection pool to reuse existing connections instead of creating a new one per request. Most ORMs support this out of the box (e.g., Django's CONN_MAX_AGE setting, SQLAlchemy's connection pool).

Avoid N+1 Queries #

If your CRUD operations involve related models, use select_related() (for foreign keys) or prefetch_related() (for many-to-many) to fetch all needed data in one query instead of looping and querying for each record.

4. Optimizations for plugin.js #

Batch Requests #

Don't send an AJAX request for every single record change. Collect multiple operations (e.g., all selected records to delete, all edited rows) and send them in one request:

// Collect batch delete IDs
const deleteIds = [...document.querySelectorAll('.delete-checkbox:checked')].map(el => el.value);

// Send one batch request
fetch('/api/records/bulk-delete', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({ ids: deleteIds })
});

Minimize Data Transfer #

Only send the fields that have changed, not the entire record. For example, when updating a record, send just { id: 123, updated_field: 'new_value' } instead of every field in the object.

Use Asynchronous Parallel Requests (If Needed) #

If you can't batch, use Promise.all() to run multiple requests in parallel (instead of waiting for each to finish sequentially):

const updatePromises = recordsToUpdate.map(record => 
  fetch(`/api/records/${record.id}`, {
    method: 'PUT',
    body: JSON.stringify(record.changes)
  })
);

// Wait for all updates to finish
Promise.all(updatePromises).then(() => {
  console.log('All updates complete');
});

Just be careful not to flood the server with too many concurrent requests—limit the batch size if needed.

5. Bonus Database-Level Fixes #

• Enable MySQL compression (set compress=1 in your DB connection settings) to reduce the size of data transferred over the network.
• Wrap multiple operations in a database transaction—this reduces the number of commit operations the remote DB needs to process, as commits are costly for remote connections.

内容的提问来源于stack exchange，提问作者Jass Waraich