Mercurial > gemma
annotate pkg/xlsx/sql.go @ 5520:05db984d3db1
Improve performance of bottleneck area calculation
Avoid buffer calculations by replacing them with simple distance comparisons
and calculate the boundary of the result geometry only once per iteration.
In some edge cases with very large numbers of iterations, this reduced
the runtime of a bottleneck import by a factor of more than twenty.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 21 Oct 2021 19:50:39 +0200 |
parents | 979f139cde4d |
children | 6270951dda28 |
rev | line source |
---|---|
5321
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 // This is Free Software under GNU Affero General Public License v >= 3.0 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 // without warranty, see README.md and license for details. |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
3 // |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
4 // SPDX-License-Identifier: AGPL-3.0-or-later |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
5 // License-Filename: LICENSES/AGPL-3.0.txt |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
6 // |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
7 // Copyright (C) 2021 by via donau |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
8 // – Österreichische Wasserstraßen-Gesellschaft mbH |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
9 // Software engineering by Intevation GmbH |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
10 // |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
11 // Author(s): |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de> |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
13 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
14 package xlsx |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
15 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
16 import ( |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
17 "context" |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
18 "database/sql" |
5347
1fc454da7ee8
XLSX templater: To ease debugging enrich error with SQL statement if it fails.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5333
diff
changeset
|
19 "fmt" |
5321
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
20 "strconv" |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
21 "strings" |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
22 ) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
23 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
24 type sqlResult struct { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
25 columns []string |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
26 rows [][]interface{} |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
27 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
28 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
29 func (sr *sqlResult) find(column string) int { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
30 for i, name := range sr.columns { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
31 if name == column { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
32 return i |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
33 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
34 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
35 return -1 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
36 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
37 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
38 func replaceStmt(stmt string) (string, []string) { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
40 var names []string |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
41 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
42 add := func(name string) int { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
43 for i, n := range names { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
44 if n == name { |
5352
979f139cde4d
XLSX templater: Fixed indexing of already used vars.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5347
diff
changeset
|
45 return i + 1 |
5321
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
46 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
48 names = append(names, name) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
49 return len(names) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
50 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
51 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
52 replace := func(s string) string { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
53 n := add(strings.TrimSpace(s)) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
54 return "$" + strconv.Itoa(n) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
55 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
56 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
57 out := handlebars(stmt, replace) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
58 return out, names |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
59 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
60 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
61 func query( |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
62 ctx context.Context, |
5333
6c0f40676984
Run xlsx templating in a read-only transaction.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5321
diff
changeset
|
63 tx *sql.Tx, |
5321
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
64 stmt string, |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
65 eval func(string) (interface{}, error), |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
66 ) (*sqlResult, error) { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
67 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
68 nstmt, nargs := replaceStmt(stmt) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
69 args := make([]interface{}, len(nargs)) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
70 for i, n := range nargs { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
71 var err error |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
72 if args[i], err = eval(n); err != nil { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
73 return nil, err |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
74 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
75 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
76 |
5333
6c0f40676984
Run xlsx templating in a read-only transaction.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5321
diff
changeset
|
77 rs, err := tx.QueryContext(ctx, nstmt, args...) |
5321
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
78 if err != nil { |
5347
1fc454da7ee8
XLSX templater: To ease debugging enrich error with SQL statement if it fails.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5333
diff
changeset
|
79 return nil, fmt.Errorf("SQL failed: '%s': %v", nstmt, err) |
5321
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
80 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
81 defer rs.Close() |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
82 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
83 columns, err := rs.Columns() |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
84 if err != nil { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
85 return nil, err |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
86 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
87 var rows [][]interface{} |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
88 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
89 ptrs := make([]interface{}, len(columns)) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
90 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
91 for rs.Next() { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
92 row := make([]interface{}, len(columns)) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
93 for i := range row { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
94 ptrs[i] = &row[i] |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
95 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
96 if err := rs.Scan(ptrs...); err != nil { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
97 return nil, err |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
98 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
99 rows = append(rows, row) |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
100 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
101 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
102 if err := rs.Err(); err != nil { |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
103 return nil, err |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
104 } |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
105 |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
106 return &sqlResult{ |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
107 columns: columns, |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
108 rows: rows, |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
109 }, nil |
0919946f624b
Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
110 } |