Hi Roy,

<< Out of interest what is it you're actually assigning to? It can't just be a simple string which is what I used - can it?  >>

I'm adding the values to a dynamic array. I removed that code to simplify the example. I have tested with and without the dynamic array and it makes little difference.

<< Also if you have the time try using a simple query rather than an SP >>

I'll give that a try.

Thanks,

Steve

Steve


I assume a two dimensional array. If so are you "pre-sizing" it or adding as you go? I don't know if the same applies but the difference in populating a largish stringlist varies a lot depending on how capacity is set at the start.

The other thing to test is just do a while not eof  / next loop to see how much time is spent in navigating the result set and how much accessing the data and stuffing into the array.

Roy Lambert

Hi Roy,

<< I assume a two dimensional array. If so are you "pre-sizing" it or adding as you go? I don't know if the same applies but the difference in populating a largish stringlist varies a lot depending on how capacity is set at the start. >>

I am pre-sizing, eg. SetLength(MyArray, FStoredProcedure.RecordCount);

<< The other thing to test is just do a while not eof  / next loop to see how much time is spent in navigating the result set and how much accessing the data and stuffing into the array. >>

It turns out that navigating the result set is what takes the time.  I have stripped out all code from the loop, except for FStoredProcedure.Next, and it takes 15 to 16 seconds.  I have tried for/while/repeat loops but they all take the same amount of time.  BTW, session type is Remote.

= Steve

Steve


>It turns out that navigating the result set is what takes the time. I have stripped out all code from the loop, except for FStoredProcedure.Next, and it takes 15 to 16 seconds. I have tried for/while/repeat loops but they all take the same amount of time. BTW, session type is Remote.

Lookup

TEDBDataSet.RemoteReadSize Property

in the manual - experiment

Roy

>>You will see about a 20% improvement just by recompiling in XE8 due to the changes in how values are read and written in TField.
>That must be a first

Indeed

<< Lookup

TEDBDataSet.RemoteReadSize Property

in the manual - experiment >>

Thanks Roy, already tried that.

I have emailed Tim so I'll see if he has any suggestions.

= Steve

Steve


>I have emailed Tim so I'll see if he has any suggestions.

Make sure you let us know the answer

Roy

<< Make sure you let us know the answer >>

Turns out it was the table encryption causing the problem (Tim discovered it).  Once I disabled the encryption it went from 16 seconds to less than 0.5 seconds to iterate through all of the records!!

Thanks Tim!

= Steve

Steve Gill wrote:

>Turns out it was the table encryption causing the problem (Tim discovered it).  Once I disabled the encryption it went from 16 seconds to less than 0.5 seconds to iterate through all of the records!!<

So decrypting the data took 32x longer than actually retrieving and sending the data over the network to the client??? From my experience encryption usually adds 20% to the overall query time. Is there something odd with Stored Procedures that increases the decryption time so much?

Barry

Barry


>>Turns out it was the table encryption causing the problem (Tim discovered it). Once I disabled the encryption it went from 16 seconds to less than 0.5 seconds to iterate through all of the records!!<
>
>So decrypting the data took 32x longer than actually retrieving and sending the data over the network to the client??? From my experience encryption usually adds 20% to the overall query time. Is there something odd with Stored Procedures that increases the decryption time so much?

Total guess here but my take would be its a mixture of transport and decryption times. Still seems high though

Roy