Solved

Why do my Delphi (using Pervasive PDAC VCL) applications run 100x slower performing database function (Relational or Transactional) on a Win7 64bit computer, than a Win7 32bit?

Posted on 2014-07-24
21
1,182 Views
Last Modified: 2014-08-12
I have several applications that I've built for my company over the years.  Just in the past year, we've refreshed all of our computers, and now everyone (there are a few WinXP computers that have no access to internet so I left them in play) has a new desktop running at least Windows 7 Pro (64bit).  Well, I've also migrated my applications to the newest (that PDAC supports..that's Pervasive Database Component Library for Delphi) Delphi (currently for me, it's XE5).  I have one laptop that is running Windows 7 which is a 32bit computer.  It performs the database functions of my applications just as fast as it should, just like the XP computers (all 32bit).  However, on any of my other computers running WIndows 7 Pro, 64bit, it takes 100x as long to perform these same functions of these programs.  It has to do with the PDAC.  I'm sure of it, but I don't know what to do to fix this.  Apparently Pervasive (Actian) does have 64bit compiled "runtime" PDAC libraries, but I don't know how to deploy them, as the PDAC is normally a design time component library (as well as it's runtime library files).  Only the 32bit versions have a visual component.  I thought it was a Stringgrid issue, but I've realized since, that this happens on ANY database process.

We are running Windows Server 2008 R2 Enterprise, with Pervasive SQL v11.3 (all latest patches in place on server and clients).  All workstations have at least 8GB of RAM, running WIndows 7 Pro 64bit (except the ones I mentioned earlier).  My specific development environment that I'm trying to work in currently is Delphi XE5 (but I have 7, XE, & XE2 installed on my old Win XP computer..which I'm slowly phasing out of use).

Any help is greatly appreciated!

Thanks,
Brent
0
Comment
Question by:answerfreak
  • 10
  • 7
  • 4
21 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40217544
How have you determined that PDAC is the root cause?  I ask this because I have seen issues with some 64-bit workstations and some AV/Firewall solutions (OK, so maybe only with TrendMicro and McAfee) -- where the system slowed down considerably (each request was throttled to one per 1/2-second, instead of the usual 1/2ms)  when the AV/firewall was installed.  TrendMicro had an update which fixed this.  However, this would be 1000x slower.

Can you duplicate the issue by using the Function Executor to read the same record 50000 times and time the result?  (If you are not familiar with the Function Executor, you can get instructions on how to do this at http://www.goldstarsoftware.com/papers/CheckingYourDatabaseServerCPUPerformance.pdf)  Post your response times for this test, and be sure to indicate if the database file is local or over a network (and if so, what speed).
0
 

Author Comment

by:answerfreak
ID: 40218302
Ok, so I performed the test (2 times) as detailed using the Function Executor.

At Server (Dell PowerEdge T710):  2.5 seconds / 2.4 seconds (database on server, so not across LAN)
At Workstation (Dell Optiplex 3020, 8GB Ram, Win7 Pro 64bit):  21.0 seconds / 21.2 seconds (connected to the Server via shared 24 Port GbE Switch)
At Laptop (Dell Latitude E5510, 4GB Ram, Win7 Pro 32bit):  19.3 seconds / 19.5 seconds (connected to the Server via shared 24 Port GbE Switch)

I also tried turning off McAfee completely (we use McAfee Managed VirusScan via Sonicwall router), and running my database application to test for speed, and it's the same slow result.

Based on the results from the Function Executor, I would have to say that the difference in times for both client machines is negligible.  It doesn't reflect the slow processing that takes place within my applications.  I think maybe it is possibly due to the very thing that the document you linked to mentions, with regards to multiple cores on a computer.

I'm still at a loss, so any more help will be appreciated.

Thanks,
Brent
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40218327
Great.  Your database numbers are just about the same as mine -- I have 14s across a GbE network link to a virtual server.  We can therefore (most likely) exclude the network, database engine, and server from the problem.

I have two ideas for the next step:  
1) Can you build a very simple application in Delphi that does the same thing, with the DemoData database?  I would like to be able to test your application on my own virtual systems.  I normally run Win7 x64, and I can find a Win7 x86 VM to play with, too.  Unfortunately, I don't own a Delphi development license, so I cannot build the same test here.  However, I should be able to run an app that you create.
2) The other option is to capture the application (running the exact same function on the exact same data on both workstations) with a network analyzer.  This will show us exactly where the differences lie and should help narrow down the issue even further.  If you are not familiar with Wireshark, there is some basic instructions at http://www.goldstarsoftware.com/papers/CapturingNetworkTrafficWithWireshark.pdf
0
 

Author Comment

by:answerfreak
ID: 40225648
I'm sorry Bill, I have been slammed the past two days (Friday & Monday - today).  I started building the new project with the DemoData, but I keep getting interrupted and have to take care of other issues, etc.  I will also try the network analyzer as soon as I can.

I appreciate your help, and I want to stay on this until I get some type of resolution.  So please bare with me!

Thanks,
Brent
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40226107
21 seconds for 64-bit compared to 19 seconds for 32-bit is more or less the same bad performance
the big difference is between those and the app on the server

the difference with that is network + database client

this difference will also show up with large amounts of rows
large amounts across a network really slow down performance
> grids can hide this problem as they only display a few lines on screen
however the query components may load a lot more lines than displayed
you have grids with lots of rows ?
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40226661
Any time you run over a network from a client machine, the perceived "performance" is likely to be slower.  In my own experience, 7x to 8x slower is about right over just about any network, because most packets are small and can be transferred quickly.  Although the electron wave travels at approximately 2/3rd the speed of light, it still takes a finite time to get the request from the client through the network cables, switches, etc. to the server, and then to get the reply back again.  This is simply a matter of physics.  When you run directly on the server, you skip all of this latency, and the system can get data much more rapidly.

The observation from Geert Gruwez is, however, certainly accurate.  Depending on your development methodology, if you are populating a large grid and requesting 100,000 data elements to fill the grid, it is definitely going to take some time.  The way to address this is to redesign the application to either request multiple records per network packet (using Btrieve GetNextExtended calls or SQL calls with a larger client buffer), or to pre-filter your data before reading it.

However, the main point of this question is the difference in deploying a 32-bit PDAC application between the 32-bit environment and the 64-bit environment.  The 32-bit component should work just about identically on either platform, with slight overhead perhaps on the WoW (Windows on Windows) environment utilized by the x64 OS, which might be overshadowed by the increased OS performance of the x64 environment.
0
 

Accepted Solution

by:
answerfreak earned 0 total points
ID: 40227652
Ok, so I found the problem.  Ready for this....... by disabling the "SQLHourGlass" on the TPvSession component, I've completely fixed the issue.  It was slowing down the transactional & relational database processing (ie:  TPvSQLSession.SQLHourGlass:=false & TPvSession.SQLHourGlass:=false).

I know it sounds hard to believe, but the processing went from 30 seconds to refresh a populated TStringGrid component (special routine that iterates and performs math functions on data, then populates the StringGrid with daily order totals, with a break-down of dollars for specific types of orders, etc.)....  to 2 seconds!!!

I do appreciate all the help though.  I cannot believe that this is it, but it really is.

Thanks again to all who responded.
Brent
0
 

Author Closing Comment

by:answerfreak
ID: 40237084
I found the issue to be a problem with using the SQLHourGlass set to true on either PvSQLSession or PvSession Pervasive components.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40240058
The developers at Actian tried to duplicate your issue, and could not do so.  They want to know what OS you are using, and exactly which PDAC release.  Also, they were curious if you could provide a test case that exhibits the problem.  It doesn't need to use youir own tables, but if it can use a table from DemoData, that would be ideal.
0
 

Author Comment

by:answerfreak
ID: 40246085
Client computer:
Windows 7 64bit SP1
PDAC (v11.31.042) - latest PDAC
Pervasive Client v11 32bit & 64bit (June 2014 Patch - v11.31)

Server computer:
Windows 2008 R2 Enterprise 64bit
Pervasive Server v11 64bit (June 2014 Patch - v11.31)

I will build the test application like you mentioned, as soon as I can.  When I do, I'll attach it to another post for you to review, and forward to Actian as you see fit.

Thanks,
Brent
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:answerfreak
ID: 40247661
Ok, here's my sample application that shows a huge difference in the processing time when ran with the SQLHourGlass turned on (which is the default when the program is initially started) on a Win 64bit client with the latest and greatest PDAC and Pervasive Client (all patches in place for client and server) versus when it's off.

On a Win 7 32bit client, the differences in time to process are only slightly different, but on the Win 7  64bit client they are huge!

Win 7 64bit:
Time to process (SQLHourGlass: On) => 72,462 milliseconds
Time to process (SQLHourGlass: Off) => 12,894 milliseconds

Win 7 32bit:
Time to process (SQLHourGlass: On) => 25,164 milliseconds
Time to process (SQLHourGlass: Off) => 22,542 milliseconds

I've attached a zipped file that contains the source code, and the compiled exe.  This program attaches to the DEMODATA Pervasive Sample Database that comes with Pervasive software.  There is nothing fancy about this app.  All it does when you click "Run Process", is iterate through the "Persons" table and overwrite the "Comments" field with some text, then move to the next record, until it reaches the end of the file.  Then it calculates the time it took, in milliseconds, to process and displays that in a ShowMessage event.  After processing the first time, use the button on the left to "Turn Off" the SQLHourGlass, and then run the process again.

Let me know how it goes.

Thanks,
Brent

UPDATE:  I've tried to upload my zip file containing my project but no go....please tell me how to get it to you.  Thx.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40252549
iterate ...
in the past i have encountered a few dev guys who have made an art form of "iterating"  
the art form and performance didn't go well together
after reprogramming one of their apps we went from 3 days to 45 seconds
(after some fine tuning it went to .3 seconds)
amazing what iterate can do ... :)

just post the source from your pas file where you update the data
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40253007
Brent:  As I will not be the one looking at the code, please forward your code directly to Actian via the email address "defectsfyi@pervasive.com".  I have already been in contact with them and they are expecting it.  If you have any problems with this Email address, let me know and I can get you an FTP address to submit the package.
0
 

Author Comment

by:answerfreak
ID: 40253184
@BillBach,

I did what you requested, and sent the package to them in a zip file.  Thx.


@Geert Gruwez,

Here is the code:

procedure TForm3.btnClickMeClick(Sender: TObject);
var
thentime:TDateTime;
ms:Int64;
begin
  thentime:=now;
  TablePerson.First;
  while not TablePerson.Eof do begin
    TablePerson.Edit;
    TablePersonComments.AsString:= '....Appended String to the comments...';
    TablePerson.Post;
    TablePerson.Next;
  end;
  ms:=MillisecondsBetween(now, thentime);
  Showmessage('Processing time: ' + IntToStr(ms));
end;

procedure TForm3.Button1Click(Sender: TObject);
begin
  if Button1.Caption = 'Turn Off SQLHourGlass' then begin
    PvSession1.SQLHourGlass:=false;
    Button1.Caption := 'Turn On SQLHourGlass';
  end
  else begin
    PvSession1.SQLHourGlass:=true;
    Button1.Caption := 'Turn Off SQLHourGlass';
  end;

end;

Open in new window

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40253426
the iterating way is really slow if you update the whole table
why not use a simple update statement ?

what actually happens:
open table
copy all rows to delphi app
open cursor for updating
move cursor pointer to first row
send update statement to database where current of cursor
? commit ? > depends on autocommit functionality, which can force a disk access

move cursor pointer to next row
send update statement to database where current of cursor
? commit ? > depends on autocommit functionality, which can force a disk access

move cursor pointer to next row
send update statement to database where current of cursor
? commit ? > depends on autocommit functionality, which can force a disk access

move cursor pointer to next row
send update statement to database where current of cursor
? commit ? > depends on autocommit functionality, which can force a disk access

... until eof
close cursor
requery table ?

i'd do it like this:
query.sql.text := 'update Person set comments = ''Appended string to the comments'' ';
query.execsql;

Open in new window


if you really want to append ...
query.sql.text := 
  'update Person set comments = concat(comments, ''Appended string to the comments'')';
query.execsql;

Open in new window

0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40253449
@Geert Gruwez:  Normally, I might agree with this suggestion.  However, three factors come into play here:
1) This is test code to prove a software defect in the PDAC components.  Clearly, nobody would update every record like this.  Even if you did want to update every record, you would more than likely use a computed field of some kind, and not update every record with the exact same text.
2) The initial problem/solution has already been identified as being related to the Hourglass function, specific to the 64-bit PDAC.  Changing this ONE factor changes the way the code runs dramatically, and it really shouldn't make such a difference.  Again, the test code is there to prove the problem and show the validity of the solution, so that Actian can figure out what is happening in the 64-bit PDAC components and fix it, if possible.
3) With the PSQL database engine, implementing a massive UPDATE statement as you suggest is not likely to be any faster.  Because the underlying Microkernel Database Engine implements each SQL query inside of an MKDE transaction, such a statement can cause an extensive number of record locks, a lengthy transaction, and potential blocking when multiple users are running and accessing the same table simultaneously.   In fact, for very large databases, such a statement can cause failures if the MKDE runs out of memory for tracking the transaction itself.  Unlike other SQL engines which may use disk-based transaction logs, the MKDE uses memory-based transaction tracking for performance reasons -- so a query will often be limited in the total size of the data that can be changed at once.  

In fact, the final issue is likely to be the biggest reason for NOT doing such a statement.  With PSQL, a stored procedure that reads each record and updates it "where current of" is vastly faster, especially on large data sets, because of the fact that each UPDATE statement is touching a single record only.  It seems counter-intuitive, but it is true in the world in which we live and play.
0
 

Author Comment

by:answerfreak
ID: 40253519
Hi Geert,

I appreciate and understand your comments regarding best practices for updating a record using SQL.  I agree, that this method is much faster, as we would be letting the server do the work.  However, this is outside the scope of the problem reported here.  As a matter of fact, the SQLHourGlass property which is part of the TPvSession component, is also part of the TPvSQLSession component for Pervasive.  Unless I use ODBC, the problem would still exist, unfortunately.

Brent
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 40255039
@BillBach
"With the PSQL database engine, implementing a massive UPDATE statement as you suggest is not likely to be any faster"

if what you are saying is as you say it is, then it's bummer for all those who use PSQL.

the network layer always has a huge impact on using iteration for multiple single record updates
the performance will always be faster for a single update statement which updates multiple records
if that's not the case ... use a different database type
that's why a database engine was invented in the first place

the hourglass is obviously a bug
nevertheless, after that bug is fixed ...

i'll be happy to point out where you can still have a 100x better performing app with correct sql usage and the code around it
always try and process data as a set and not as single record items
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 40255251
As of yesterday, Actian had not received it yet. Please try sending to database@pervasive.com as well.
0
 

Author Comment

by:answerfreak
ID: 40255657
@BillBach,

I just sent it again to both addresses.  I haven't received any bounces on the one I sent yesterday though.  Oh well, hopefully they'll get it.
0
 

Author Comment

by:answerfreak
ID: 40255685
@Geert,

Yes, I do agree.  But don't worry, I do understand those best practices and do my best to implement them where possible.  Basically, this problem with the SQLHourGlass has became known to me as a problem, for simply scrolling through records on the users end.  If I have to update a record, I will typically do this in an SQL update statement.  I only created the example using transactional updates because it allows this problem to be easily seen.

Thanks,
Brent
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
This is about my first experience with programming Arduino.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now