There is the next piece of code that takes data from the SQLite database, then loads it (as text) into the client application, but unfortunately loading the data takes too long, how to make it faster?

 results = c.execute(select).fetchall() stroka = '' for line in results: stroka = stroka + str(line) + '\n' self.ui.textEdit.setText(stroka)
  • Show the request and the corresponding DDL. How much data is this query retrieved? What specifically means "long." - Sergey Gornostaev
  • 6
    In a loop, you simply collect data in a string, splitting \n ? Try one line: text = '\n'.join(str(line) for line in results) or so text = '\n'.join(map(str, results)) . String string slower join works. And the body in your code can be simplified through the operator += like this: stroka += str(line) + '\n' - gil9red
  • @ gil9red Thanks, through join it has become faster! - Argentum-
  • It is better not to wait for all the lines from the base to be received, but using the generator, pull one at a time and display - suit
  • one
    related question Incomprehensible deceleration of concatenation in a loop - jfs

2 answers 2

I propose to optimize the compilation of text by replacing the string concatenation with join :

 text = '\n'.join([str(line) for line in results])

or so:

 text = '\n'.join(map(str, results))
  • You can even variation on the topic: '\n'.join([result for [result] in c.execute(select)]) try. Combining results into a single line in sql looks harder - jfs
  • I agree, but this is at the discretion of the author, I would do as you suggested, only: '\n'.join(result for (result,) in c.execute(select)) - gil9red
  • for both performance and readability, [] best left (inside join for efficiency, and inside for for readability). Compare: python -mdis <<<'[result]=1' vs. python -mdis <<<'(result,)=1' (identical) .¶ join([]) faster join() : python -mtimeit -s 's=[("a",)]*10**3' '"\n".join([a for [a] in s])' vs. python -mtimeit -s 's=[("a",)]*10**3' '"\n".join(a for [a] in s)' - jfs
  • I'm confused by what we write [result] , although sqlite3 returns a tuple. About [] in join I thought that the efficiency would be exactly the opposite, I was wrong, I agree with readability - gil9red
  • so I explicitly python -mdis command so that you clearly see that [] and (,) left of = do (this is a special syntax: list, tuple are not created here). - jfs

You can do this on the side of SQLite.

Example:

 sqlite> select * from tab; s ---------- 111 222 333 sqlite> select group_concat(s,'') from tab; group_concat(s,'') ------------------ 111222333 sqlite> select group_concat(s,'\n') from tab; group_concat(s,'\n') -------------------- 111\n222\n333

in the following case, one line is returned:

 sqlite> select group_concat(s,char(10)) from tab; group_concat(s,char(10)) ------------------------ 111 222 333

Thanks to @jfs for the Python code :

 [[result]] = db.execute("select group_concat(s, '\n') from tab")

PS This variant has O(n) in execution time.

  • one
    [[result]] = db.execute("select group_concat(s, '\n') from tab") Explicitly mention the guarantee ( O(n) ) for the execution time. - jfs
  • @jfs, thanks, added ... - MaxU