Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / fpc-devel / 2015-06

What do you think about TSQLScript using 'TextReader'?

3 messages · 2 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

silvioprog

Hello,

A few weeks ago, I took a look at the TSQLScript (that IMHO is a very nice class!) source, and I noticed that it seems slow for processing big files.

I did some tests with this class. My SQL file: 90 MB, with CREATE TABLE, CREATE PROCEDURE, CREATE TRIGGER, ALTER TABLE, several INSERT INTO and more. Using the current TSQLScript implementation, the execution of this file is very slow, and the CPU/memory usage is extremely large(*), but after make some changes in the original TSQLScript source code, adapting it to use this[1] new class (recently applied in FCL), I got a significant difference, ie, the CPU/memory usage now is amazing less, increasing the performance +-80%. =) The answer to the significant gain is simple: using a textreader, the TSQLScript class read the SQL (from string, stream or file) by demanding (line by line, and using a low level byte reading too), instead of loading an entire file to a buffer in a stringlist.

So, after this explanation, what do you think about TSQLScript using textreader? I can send a patch showing this idea as a real implementation, of course, keeping the 'Script' as a stringlist, just adding a new property, called 'Source' and declared as 'TTextReader'.

An observation: I suspect it has a small bug at this line[2]: the 'Directive' variable could break the loop skipping for the next line after its assignment. OK, if it is a bug, it is very easy to fix.

(*) In a specific case on a Linux server, the kernel killed my executable while importing my SQL file, see more details here[3]. [1] http://bugs.freepascal.org/view.php?id=28075
[2]
https://github.com/graemeg/freepascal/blob/master/packages/fcl-db/src/base/sqlscript.pp#L387 [3]
http://unix.stackexchange.com/questions/136291/will-linux-start-killing-my-processes-without-asking-me-if-memory-gets-short

Silvio Clécio My public projects - github.com/silvioprog

Reply by email

Michael Van Canneyt
to silvioprog
silvioprog wrote 9 lines
Hello, A few weeks ago, I took a look at the TSQLScript (that IMHO is a very nice class!) source, and I noticed that it seems slow for processing big files. I did some tests with this class. My SQL file: 90 MB, with CREATE TABLE, CREATE PROCEDURE, CREATE TRIGGER, ALTER TABLE, several INSERT INTO and more. Using the current TSQLScript implementation, the execution of this file is very slow, and the CPU/memory usage is extremely large(*), but after make some changes in the original TSQLScript source code, adapting it to use this[1] new class (recently applied in FCL), I got a significant difference, ie, the CPU/memory usage now is amazing less, increasing the performance +-80%. =) The answer to the significant gain is simple: using a textreader, the TSQLScript class read the SQL (from string, stream or file) by demanding (line by line, and using a low level byte reading too), instead of loading an entire file to a buffer in a stringlist.

I think you hit the stringlist performance limit with 90 mb =)

So, after this explanation, what do you think about TSQLScript using textreader? I can send a patch showing this idea as a real implementation, of course, keeping the 'Script' as a stringlist, just adding a new property, called 'Source' and declared as 'TTextReader'.

I think this is a good idea.

An observation: I suspect it has a small bug at this line[2]: the 'Directive' variable could break the loop skipping for the next line after its assignment. OK, if it is a bug, it is very easy to fix.

Please report it in the bugtracker.

Show quoted text 5 lines
(*) In a specific case on a Linux server, the kernel killed my executable while importing my SQL file, see more details here[3]. [1] http://bugs.freepascal.org/view.php?id=28075 [2] https://github.com/graemeg/freepascal/blob/master/packages/fcl-db/src/base/sqlscript.pp#L387 [3] http://unix.stackexchange.com/questions/136291/will-linux-start-killing-my-processes-without-asking-me-if-memory-gets-short

If you want, you can create a patch and put it in the bugtracker. Anything to improve performance is welcome.

Michael.

Reply by email

silvioprog
to Michael Van Canneyt

On Mon, Jun 15, 2015 at 6:29 AM, Michael Van Canneyt <

Show quoted text 60 lines
wrote: On Sun, 14 Jun 2015, silvioprog wrote: > Hello, > > A few weeks ago, I took a look at the TSQLScript (that IMHO is a very > nice class!) source, and I noticed that it seems slow for processing big > files. > > I did some tests with this class. My SQL file: 90 MB, with CREATE TABLE, > CREATE PROCEDURE, CREATE TRIGGER, ALTER TABLE, several INSERT INTO and > more. Using the current TSQLScript > implementation, the execution of this file is very slow, and the > CPU/memory usage is extremely large(*), but after make some changes in the > original TSQLScript source code, adapting it to > use this[1] new class (recently applied in FCL), I got a significant > difference, ie, the CPU/memory usage now is amazing less, increasing the > performance +-80%. =) The answer to the > significant gain is simple: using a textreader, the TSQLScript class read > the SQL (from string, stream or file) by demanding (line by line, and using > a low level byte reading too), instead > of loading an entire file to a buffer in a stringlist. > I think you hit the stringlist performance limit with 90 mb =) > So, after this explanation, what do you think about TSQLScript using > textreader? I can send a patch showing this idea as a real implementation, > of course, keeping the 'Script' as a > stringlist, just adding a new property, called 'Source' and declared as > 'TTextReader'. > I think this is a good idea. > An observation: I suspect it has a small bug at this line[2]: the > 'Directive' variable could break the loop skipping for the next line after > its assignment. OK, if it is a bug, it is very > easy to fix. > Please report it in the bugtracker. > (*) In a specific case on a Linux server, the kernel killed my executable > while importing my SQL file, see more details here[3]. > [1] http://bugs.freepascal.org/view.php?id=28075 > [2] > https://github.com/graemeg/freepascal/blob/master/packages/fcl-db/src/base/sqlscript.pp#L387 > [3] > http://unix.stackexchange.com/questions/136291/will-linux-start-killing-my-processes-without-asking-me-if-memory-gets-short > If you want, you can create a patch and put it in the bugtracker. Anything to improve performance is welcome. Michael.

Very nice, I'll do that ASAP. =)

Thank you!

Silvio Clécio My public projects - github.com/silvioprog

Reply by email