weird performance

[Ludek]

I have read some of your "increase performance" tips and tried them. And I found, that many of them doesn't work at all, they lead to smaller or bigger performance decrease!

1.

Open

If you don't need to edit the dataset, you can set its ReadOnly property to increase its opening speed. In that case, an additional information, required for INSERT, UPDATE, and DELETE statement generation, will not be requested.

I tried following code:

Code: Select all

    for i := 1 to 100 do begin
      qtest.Open;
      while not qtest.Eof do begin
        qtest.next;
      end;
      qtest.close;
    end;

time with readonly = false: 1,7 s
time with readonly = true: 2,5 s
!!!!!!


2.

When UniDirectional is True, an application requires less memory and performance is improved.

same code as above, following times:
unidirectional = false: 1,68 s
unidirectional = true: 1,79 s

i have everything tried many times, the times were slightly different, but the time difference was always there.

Do you have an explanation of these times? I really don't understand why your increase performance tips don't work, but it is really so.

[yeohray]

The Unidirectional property helps when you are retrieving large datasets.

E.g. say you are retrieving 100,000 rows, and each row is 10 Kb in size. With Unidirectional false, and FetchAll true, SDAC will retrieve all 100,000 rows into memory when you call Open, resulting in memory utilization of ~ 1 GB.

With Unidirectional true, and FetchAll false, SDAC will only retrieve the number of rows specified by the FetchRows property (default 25) each time, so only 25 rows are in memory at any one time i.e. 250 Kb. This is very useful when you only need to scroll forward in a dataset.

[Ludek]

But that does not explain to me, why setting a restrictive option makes performance worse... If I say UniDirectional := true, I'm saying:

hey, my TMSQuery, I won't ever scroll back, so, make some internal optimizations, that will make the whole execution use fewer memory and run faster.

it PERHAPS is taking fewer memory, but it is definitively running slower. I think, the statement "performance is improved" is not correct. Or, there's a bug.

[yeohray]

I think it all depends on your application.

In my app, I retrieve large blobs from the database. If I were to fetch all the rows at once, my clients' workstation will surely crawl to a halt when SDAC starts using more memory than what is available. It will still work, but the page file will be swapping like crazy. If I were to only work with a limited set of rows at any time, for sure I can process the whole dataset faster. So in my case, 'performance is improved'.

[Ludek]

I understand, that such option could help in scanning tables in size of many hundred MBs. But I don't understand this slowdown in scanning smaller tables. I could also reformulate my question:

What TMSQuery options should be set to make a fastest possible scan (forward-only) of a query/table with 10 or more records(up to 10000, i'm not doing larger scans)? Used memory is no question, only the speed. just following code:

Code: Select all

query.open;
while not query.eof do  begin
  // some code
  query.next;
end
query.close;

[Antaeus]

FetchAll, Unidirectional, and ReadOnly may increase performance of the Open operation. But completed Open does not always mean that all records are fetched to the client. Therefore subsequent calls to Next require additional resources to fetch data from server.

Try to play with other options, for example CursorType. For more information take a look at the "Performance of obtaining data" topic in SDAC help, and try the ServerCursors demo.

[Ludek]

I've read (once more ) the named article. You write there, that the setting with fastest navigation ist ctDefaultResultSet and FetchAll=true. I'm using this setting the whole time already. If I understand it correctly, the other types only make the opening faster, but the fetching slower. So it can't help me, I need to optimize "opening+fetching" of my queries; the first 20 rows are of no importance for me, I need all of them.

to serverside cursors - I really can't imagine, that server-side cursors could make scanning of a table any faster. Also you are writing in the help, that navigation is then slower.

Could you tell me, why only the setting of readonly from false to true makes so big difference in time? if opening with readonly=true is faster, the fetching must be much slower. why is fetching a readonly dataset slower than fetching a editable dataset?
I think, it should be metioned in the help, that setting readonly=true makes opening faster, but the whole processing of the query could be pretty slower than with readonly=false.

[Antaeus]

I could not reproduce the problem with ReadOnly. Please send us (sdac*crlab*com) your test application, including the scripts to create table fill it with random data.

Also supply the following information:
- exact version of SDAC. You can see it in the About sheet of TMSConnection Editor;
- exact version of your IDE;
- exact version of SQL server and client. You can see it in the Info sheet of TMSConnection Editor;
- any other information about the test condition that may be useful.

[Ludek]

I'll do it ASAP!

[Ludek]

I don't understand now anything. I tried the same executable, same db, same query today and readonly does not affect performance any more.
It is now slightly faster as it should be.
The only change I made - I installed SP1 on my vista - perhaps some new unknown performance improvement in SP1 ???
Sorry for my post, Ludek.