Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Register
  • Sign in
  • O openpyxl
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 282
    • Issues 282
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 7
    • Merge requests 7
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages and registries
    • Packages and registries
    • Container Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar

Due to a massive storm of spam, registration on this instance now requires explicit administrator approval. Sorry for the added friction, we're also looking into automatic filtering options.

  • openpyxl
  • openpyxl
  • Issues
  • #2054
Closed
Open
Issue created Jun 15, 2023 by Paul LeBlanc@pLeBlanc93

Performance degrades when saving many hyperlinks

When writing lots of elements that utilize RelationshipList (eg hyperlinks), performance degrades. The setter call in append calls _convert as it inserts more elements, resulting in O(n^2).

So for 5,000 hyperlinks, _convert ends up being called 12.5 million times.

Tested on 3.2 branch:

import cProfile
import pstats
import sys

import openpyxl
from openpyxl.cell import WriteOnlyCell


print(sys.version_info)
print('openpyxl', openpyxl.__version__)


N = 5_000  # ~10 seconds
print(f'{(N * (N + 1)) // 2:,} calls\n')


def setup(num):
    book = openpyxl.Workbook(write_only=True)
    name = f'test_{num}'
    sheet = book.create_sheet(name)
    sheet.append(('test',))  # Target for hyperlinks

    for i in range(num):
        cell = WriteOnlyCell(sheet, i)
        cell.hyperlink = f"{name}!A1"
        sheet.append((cell,))

    return sheet


def profile(sheet):
    with cProfile.Profile() as pr:
        sheet.close()
    stats = pstats.Stats(pr)
    stats.strip_dirs().sort_stats('ncalls').print_stats(5)  # Top 5 functions by number of calls


data = setup(N)
profile(data)

On my machine:

sys.version_info(major=3, minor=9, micro=16, releaselevel='final', serial=0)
openpyxl 3.2.0b1
12,502,500 calls
         37802736 function calls (37797730 primitive calls) in 9.224 seconds
   Ordered by: call count
   List reduced from 72 to 5 due to restriction <5>
   ncalls  tottime  percall  cumtime  percall filename:lineno(function)
 12567517    1.019    0.000    1.019    0.000 {built-in method builtins.isinstance}
 12507502    3.863    0.000    7.415    0.000 sequence.py:27(<genexpr>)
 12502500    2.549    0.000    3.552    0.000 base.py:53(_convert)
    40046    0.007    0.000    0.007    0.000 {built-in method builtins.getattr}
    35008    0.050    0.000    0.050    0.000 base.py:24(__set__)
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
Time tracking