创建带权限控制的可编辑矩阵：SQLite下加载缓慢的优化咨询

Alright, let's tackle this slow loading issue you're hitting when your Descriptor count crosses 15. I’ve worked on similar matrix UIs with granular object-level permissions before, so here are practical, actionable fixes you can implement step by step:

SQLite is great for small datasets, but it struggles with complex, repeated queries. Here’s how to fix that:

• Eliminate N+1 query problems: If you’re fetching Descriptors first then looping through each to pull related CellCE objects, you’re generating dozens of separate SQL calls. Use Django’s select_related (for foreign keys) or prefetch_related (for many-to-many relationships) to fetch all related data in 1-2 queries. Example:

  # In your view
  descriptors = Descriptor.objects.all()
  cell_ces = CellCE.objects.filter(descriptor__in=descriptors).prefetch_related('descriptor')
  

• Add database indexes: Index the foreign key linking CellCE to Descriptor, plus any fields used in permission checks (like user_id if tied to your object-level permissions). Update your model:

  # In models.py
  class CellCE(models.Model):
      descriptor = models.ForeignKey(Descriptor, on_delete=models.CASCADE, db_index=True)
      # Add db_index=True to other frequently filtered fields
  

  Then run python manage.py makemigrations and migrate to apply the indexes.
• Filter permissions at the database level: Don’t fetch all CellCE objects then filter for user permissions in memory. Use your permission framework’s database-level methods (e.g., django-guardian’s get_objects_for_user) to only pull cells the user can interact with.

Heavy template logic is a common culprit for slow page loads:

• Move logic out of templates: Precompute editable status for every cell in your view, then pass a ready-to-render 2D array to the template. For example, create a list of rows where each row contains cell data and a is_editable boolean. This avoids running permission checks or database calls inside template loops.
• Cache rendered fragments: Use Django’s template cache to store the rendered matrix (or rows) for logged-in users. Since permissions are user-specific, include the user ID in the cache key:

  {% load cache %}
  {% cache 300 matrix_cache request.user.id %}
      <!-- Your matrix HTML here -->
  {% endcache %}
  

• Minimize DOM nodes: If your matrix has hundreds of cells, each with complex HTML, simplify the structure. Use CSS classes instead of inline styles, and avoid nested <div>s where possible.

Granular object-level permissions can get expensive if not optimized:

• Batch permission checks: Instead of checking user.has_perm('edit_cellce', cell) for every single cell, fetch all editable CellCE IDs in one query first. Then use that ID list to mark cells as editable when building your matrix data. Example:

  # In your view
  editable_cell_ids = CellCE.objects.filter(
      # Your permission filter here
  ).values_list('id', flat=True)
  # Then when building matrix rows, check if cell.id is in editable_cell_ids
  

• Cache permission data: Store the editable CellCE ID list in Django’s cache framework (e.g., Redis or even local memory cache) with a short TTL (5-10 minutes). This avoids re-running permission queries on every page load.

Even if your backend is fast, a huge DOM matrix will slow down the browser:

• Implement virtual scrolling: Use a frontend library (like react-window for React, vue-virtual-scroller for Vue) or build a simple custom solution to only render cells that are visible in the viewport. This reduces DOM nodes from hundreds to just a few dozen.
• Lazy-load non-critical content: If your page has other elements outside the matrix, load them after the matrix renders using JavaScript.
• Optimize CSS: Avoid heavy CSS selectors that force the browser to reflow the page constantly. Use CSS grid or flexbox for the matrix layout—they’re far more efficient than nested tables for dynamic content.

Since you’re using SQLite, these tweaks can give you a quick performance boost:

• Enable WAL mode: Write-Ahead Logging improves read performance when there are concurrent writes. Update your database settings:

  # In settings.py
  DATABASES = {
      'default': {
          'ENGINE': 'django.db.backends.sqlite3',
          'NAME': BASE_DIR / 'db.sqlite3',
          'OPTIONS': {
              'journal_mode': 'WAL',
              'timeout': 20,
          },
      }
  }
  

• Increase cache size: SQLite’s default cache is small—boost it to reduce disk I/O. Add 'cache_size': -20000 to the OPTIONS (the negative value means KB, so this sets it to 20MB).
• Run VACUUM periodically: This defragments the SQLite database and optimizes query performance. You can run it via a Django management command or using sqlite3 directly.

If your dataset keeps growing beyond SQLite’s capabilities:

• Switch to PostgreSQL: It handles large datasets and complex queries far better than SQLite, with support for advanced indexes, parallel querying, and better concurrency.
• Restructure your data: If the matrix is wide (many Descriptors), consider using a denormalized table where each Descriptor is a column. This reduces the number of rows you need to fetch, though it trades off flexibility for performance.

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