[fpc-devel] What do you think about TSQLScript using 'TextReader'?

[silvioprog]

On Mon, Jun 15, 2015 at 6:29 AM, Michael Van Canneyt <michael at freepascal.org
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20150619/fd8c87a5/attachment.html>