Performance Question

martin · Post by **martin** » Fri 30 Nov 2007 11:17

Hi there,

I have a general question regarding Performance.

We use Delphi 6 and Microsoft SQL Server 2000 with your excellent dbExpress Driver Components.

SQL Server 2000 is an Enterprise Version with 12 GB of dedicated RAM, machine is a very fast 4-CPU Server.

Client is a very fast Quad-Opteron machine running Windows XP SP2.

Network is switched Gigabit.

All data is cached (as we have monitored in performance mon).

Still we cannot achieve more than ~1000 of the following queries per second:

SELECT SimpleIntegerField FROM Table Where Key = xy

where there is of course a clustered index on Field Key.

We have experimented with Network Packet Size, Network Protocol Changes (IP, Named Pipes) but no improvement could be achieved.

It's hard for me to believe that in such a powerful environment no more than 1000 queries per second are possible.

What do you think where is the bottle neck ?

Thanks
Martin

martin · Post by **martin** » Mon 03 Dec 2007 10:03

Core Lab Team ?

Antaeus · Post by **Antaeus** » Mon 03 Dec 2007 14:36

You should initially determine, which of three components (client/network/server) is the slowest. Try to run your program at the server to exclude network overheads. Try to make a stored procedure that runs such SELECTs at the server to exclude client overheads.

martin · Post by **martin** » Tue 04 Dec 2007 08:40

The Test App is a bit faster but not much (10%) when running on the db server itself. But I assume all the traffic has still to go through a loopback so there is still network overhead there.

When I run the same test app 5 times in parallel on the client, all 5 apps do have almost the same performance like running all alone, so the server is not the bottleneck nor is the network.

So my question is: Is there so much protocol overhead and latency involved in the row Delphi-dbExpress-OleDB-Network-SQLServer, that one cannot fire more then 1000 point queries per second ?

PS: Solving the issue within a stored procedure is not possible because these are random point queries.

Antaeus · Post by **Antaeus** » Wed 05 Dec 2007 15:01

Can you archive higher performance with other data access solutions like BDE, or the standard SQL Server driver for dbExpress?
Probably you should use some special tools to measure workload of separate chains in your system to determine the bottleneck.

martin · Post by **martin** » Fri 14 Aug 2009 11:36

We have to revisit this problem, since we seem to reach scalability problems in some of our server applications.

Environment: Delphi 6.02, Win2003 Enterprise, SQL Server 2008 Enterprise.

We use 16 workerthreads running on 8 Cores to fetch BLOBs from the database. We cannot bypass ~250 BLOBs per second, no matter how many threads we are using. Each thread does have its own SQLConnection.

Question: Is there any kind of synchronisation (e.g. memory management) inside the dbexpress driver that could potentially cause this bottleneck when running many queries in parallel from the same application ?

EDIT: What Memory Manager does the compiled version of dbexpsda.dll use ?

martin · Post by **martin** » Tue 18 Aug 2009 14:19

What Memory Manager does the compiled version of dbexpsda.dll use ?