?
Solved

old gds32.dll faster then new version?

Posted on 2003-03-17
19
Medium Priority
?
10,540 Views
Last Modified: 2013-12-09
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
0
Comment
Question by:ITugay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +2
19 Comments
 
LVL 6

Expert Comment

by:BAlexandrov
ID: 8156901
I have never heard of something simillar...
If you are russian - ask in
news://forums.demo.ru/epsylon.public.interbase


Bojidar Alexandrov
0
 
LVL 9

Author Comment

by:ITugay
ID: 8156961
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
 
LVL 6

Expert Comment

by:BAlexandrov
ID: 8157233
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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 9

Author Comment

by:ITugay
ID: 8157674
>> 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
 
LVL 9

Author Comment

by:ITugay
ID: 8158302
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
 
LVL 1

Expert Comment

by:mtrimpe
ID: 8190439
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
 
LVL 6

Accepted Solution

by:
BAlexandrov earned 1000 total points
ID: 8190783
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
 
LVL 9

Author Comment

by:ITugay
ID: 8193260
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
 

Expert Comment

by:zbrskz
ID: 8216729
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
 
LVL 9

Author Comment

by:ITugay
ID: 8216774
zbrskz,
do you mean that you can speedup fetching if you use old gds32.dll? How it different in your case?
----
Igor
0
 
LVL 1

Expert Comment

by:mtrimpe
ID: 8217063
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
 
LVL 9

Author Comment

by:ITugay
ID: 8223181
here is collection of gds32.dll
http://web.host.kz/itugay/zip/gds32dll.zip
0
 

Expert Comment

by:zbrskz
ID: 8224646
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
 

Expert Comment

by:zbrskz
ID: 8225097
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
 
LVL 9

Author Comment

by:ITugay
ID: 8229904
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
 

Assisted Solution

by:zbrskz
zbrskz earned 1000 total points
ID: 8230470
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
 
LVL 10

Expert Comment

by:kacor
ID: 9795389
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
 
LVL 10

Expert Comment

by:kacor
ID: 9830632

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question