old gds32.dll faster then new version?

Hi all,
Strange things happens after installing new version of gds32.dll.

I have a database on remote server and have an app that shows how many rows per sec fetched from some table. Usually it is from 10,000 to 12,000.

GDS32.DLL version 6.0.1.0

After insatlling new version of client (GDS32.DLL 6.0.1.6 or 7.0.0.206) I noticed that fetching speed  slowdown and become 2,200 .. 2,500 records per second.

Problem dissapears after copy old version of gds32.dll in system32 directory.


What's wromg with new versions of gds32.dll?

_____
Igor
LVL 9
ITugayAsked:
Who is Participating?
 
BAlexandrovConnect With a Mentor Commented:
Tomorrow (monday) I will have time try to reproduce this.
At any case and thursday i will go for 6 months in the army :(
So keep the good work guys!

Bojidar Alexandrov
0
 
BAlexandrovCommented:
I have never heard of something simillar...
If you are russian - ask in
news://forums.demo.ru/epsylon.public.interbase


Bojidar Alexandrov
0
 
ITugayAuthor Commented:
Hi Bojidar,
this is not only my problem,
my friends in another company has the same. I just ask them to do some test and they was very affected.

It is difficult to detect this problem at once,
I suppose that it is not always can be noticed by user or developer if they do not have large databases with long fetch and do not migrate from 6.0.1.0 to new version. Most of them was surprised that database can work faster after replacing gds32.dll by old one.

I have two tests:
1. fetch all data from large table (~1,000,000 records)
2. execute SELECT statement to fetch only one record
   SELECT * FROM LARGETABE WHERE PK_FIELD = :RANDOM_VALUE
   I calculate number of  responces within 30 sec.

Second test show the same resultt in all gds32.dll
But first test show difference ~4x times.

Thank you for link, I will try to ask there.

____
Igor
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
BAlexandrovCommented:
It is possible that I have not notice that, because I can't remember somewhere to have that large fetches at once.
Drop the second test - it depends 99% on server and current physical data distribution.
Do a plain select from table

Also check if this depend on data or only on number of records, ie select int_field from table, or Select large_text_field from table.

Also check with firebird's gds32 (1.0.2 and 1.5)
See exact times and report them here and in the newsgroup!

Bojidar Alexandrov
0
 
ITugayAuthor Commented:
>> Drop the second test - it depends 99% on server and current physical data distribution.

the test is good enought when running on workstation with the same configurationand the same database. I run it not in concurence mode, only in different workstations one after other. Result of the test mostly depend from network connection (we have complicated network configuration). I use it to detect clients which need to be connected to another switch.


>> Also check if this depend on data or only on number of records, ie select int_field from table, or Select large_text_field from table.

OK, I will try...

0
 
ITugayAuthor Commented:
Ok, resume.

Test database has a table with 500,000 record

ID_DATA int primary key,
INT_DATA int,
STR_DATA varchar(100),
DAT_DATA timestamp

testing loop looks like this

I: Integer;

begin
  ResetStatistic;
  Q.Open;
  I := 0;
  while not Q.EOF do
  begin
    inc(I);
    if I mod 1000 = 0 then
       ShowStatistic(I);
    Q.Next;
  end;
  Q.Close;
end;

nothing unusual.

Results doesn't depend too much from SELECT statement

select * from testtable
select field1, field2... from testtable

but depend from gds32.dll version

tested on IB6 and IB7 server
6.0.1.0 ~20000 per second
6.0.1.6 ~4500 per second
7.0.0.206 ~4500 per second

tested on IB6 and IB7 servers with the same results.

surprise!
0
 
mtrimpeCommented:
Not really an answer, but this discussion isn't going anywhere. If it's reproducable as you say it is very likely that it's a big or a severe performance degradation through some ineffective coding or some complex error-checking.
The only thing you can do about this is contacting the Interbase development team and tell them of this problem. They probably haven't noticed this themselves and will be happy to fix and or explain the reason to you.
0
 
ITugayAuthor Commented:
mtrimpe,
yes, I reproduce the same degradation effect in another company. But they has the same network configuration as in our company. I'm afraid that it's some issue of network and TCP/IP configuration. Not sure, have to try in another LAN with different configuration.

BAlexandrov

>> At any case and thursday i will go for 6 months in the army :(

then you have to spend your time for something else then computer :-)

Anyway, I wish you good luck in your army approach. And I hope to meet you here later.

____
Igor
0
 
zbrskzCommented:
Hi guys,

Have the same problem!, got to this site with a google search "gds32.dll faster"...

Though, the degradation is not as severe as in your case.

Curious enough, I come from version 4 and had a very odd problem: for small row fetching the performance would be horrible, but if I would fetch an extra dummy col with more than 200 bytes the fetching speed would be ok.
Only happened for remote servers, for local queries no problems at all. This was verified by both delphi applications as for a isql query.

Version 6 does not suffer from this, but it is slower for big amount of rows to be fetched. I've tested many cases.

Can someone bring some light into this question?

Luis Fernandes
0
 
ITugayAuthor Commented:
zbrskz,
do you mean that you can speedup fetching if you use old gds32.dll? How it different in your case?
----
Igor
0
 
mtrimpeCommented:
OK,
I'm in a near research environment network. No disturbances or anything.
I have Interbase 5.5.
Maybe you could give me the new gds32.dll and I can try as well (if it works from 5 to 6.)

I can run some test for you then ...
0
 
ITugayAuthor Commented:
here is collection of gds32.dll
http://web.host.kz/itugay/zip/gds32dll.zip
0
 
zbrskzCommented:
Igor,

Ok, I finally have some numbers for you.

Server: P4@2.6Ghz running Interbase Server V4
Client: P3@650Mhz

Query: 14 joined tables (all by index), fetching 9000 rows, 33 columns mostly strings (some big!)

Using GDS32.DLL V4:
- 0.95s opening the query (damned "order by"!) ;
- 1.8s fetching all the data (query.next cycle) ;

Using GDS32.DLL V6:
- 0.95s opening the query ; (looks like server dependent only)
- 3.6s fetching all the data (woh! - 2 times slower...);

I then altered the query, and requested only 6 cols. Here are the results:

Using GDS32.DLL V4:
- 0.7s opening the query ;
- 0.660s fetching data ;

Using GDS32.DLL V~6:
- 0.7s opening the query ;
- 2.25s fetching data (even worst, 3.4 times slower now!) ;

Back home, I tried something different.
I made a server out of my notebook, and put a very old PC doing the client stuff.

Server: P3@650Mhz running Interbase Server V4
Client: P1@200Mhz

Same query, 14 tables, 33 columns, 9000 rows

Using GDS32.DLL V4:
- 6.6s opening the query (that "order by" is getting me mad!);
- 5s fetching data ;

Using GDS32.DLL V6:
- 6.6s opening the query ;
- 8.3s fetching data (only 1.66 times slower) ;

Finally, just by curiosity I got Interbase Server V6 running on the notebook. Here are the results:

- 4.1s opening the query (IB V6 handles "order by"s better...);
- 6.1s fetching data (this is odd...) ;

Using GDS32.DLL V6:
- 4.1s opening the query ;
- 8.3s fetching data (same as in IB V4) ;

In conclusion, GDS32.DLL is in fact slower for a remote query and the diference decreases as the amount of data being fetched gets larger. If the server/client machine configuration is faster, then de performance gap from the old to the new GDS32.DLL also increases.

What are your comments?

Luis Fernandes
0
 
zbrskzCommented:
Igor,

I've just tried your dlls, thanx for the link.

The ones I used in my previous post as V4 and V6 are respectively versions 4.2.1.328 and 6.0.1.6.

Here are the results for the ones you suplied:

V6.0.1.0: 1.4s
V6.5.0.28: 3.6s
V7.0.0.206: 3.6s

(V4.2.1.328: 1.8s)
(V6.0.1.6: 3.6s)

I was refering as old version the V4.2.1.328, while you were using V6.0.1.0 that in fact is faster than the V4.

By the way, do you know how to speed up the opening of a query with multiple (left) joins and an "order by"?. The one I'm using in this test is taking almost a second to open because of the "order by", and I really cannot live without it.

Luis Fernandes
0
 
ITugayAuthor Commented:
Hi Luis,

thank you for tests. Now I'm sure that it is not our network configuration problem. It is a global problem. You are right, then slower server computer then less degradation, because of server can't send data faster then cleint can recieve it. On very fast server with fetching data by very simple SQL statement, degradation has maximum value.

>> By the way, do you know how to speed up ...

Usually I'm trying to avoid of complex SQL statements. It is much faster to execute few simple statements one by one storing fetched data in memory and then do some operations with it.

At least, I do not use ORDER BY clause. You can try load data into TObjectList and then call TObjectList.Sort(). You can see that productivity can be increased.

Of course, you will need then another visualization controls to show your data for clients. Also coding take much more time. I use this way only in case of long-time operation report from customers.

btw, be carefull with FOREIGN KEY. In some cases it will incredible slow down SELECT statement instead of speedup it as expected. Try to remove all FOREIGN KEYs from tables you use in select with joines by foreign key and see on results. In some cases you will be very surprized.
____
Igor
0
 
zbrskzConnect With a Mentor Commented:
Hi Igor,

You're 100% right about the degradation in faster servers. And, yeap!, not a problem with your network configuration.

About the speeding up, thanx for your comments.
I also got to the same concluision about complex queries: getting down to few simple statements. The problem is, in this case, I need a lot of information and for each row I need to calculate balances. I don't do this is the query, but my application needs the data sorted out for this matter. Takes event longer doing the sort by coding because multiple cols are involved.

Concerning foreign keys, what you said is true for joins, but if you do a left join (my case) it is much faster!

Anyway, the problem is really the order by. If I take it off the query opening speed is 40ms in contrast of the 950ms I'm verifying. The joins are quite fast (all left).

Thanx anyway!

Regards,

Luis Fernandes
0
 
kacorretiredCommented:
Hi ITugay,

if you got the needed answer, please accept it by clicking on Accept in the header of the good answer. By this way you can express thanks for expert's support

with best regards

Janos
0
 
kacorretiredCommented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:
       to split points between BAlexandrov [250 points] and zbrskz [250 points]
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

kacor
EE Cleanup Volunteer
0
All Courses

From novice to tech pro — start learning today.