Client network performance with a join

Willer,

Take a look at this kbase( knowledgebase.progress.com/.../p120701 ) .  I think it will explain your behavior.

R

See knowledgebase.progress.com/.../p120701

Basically when you do a join Progress doesn't pack as many records into a single message.

Yes, I see this.

The idea was, that somebody enhance the performance in client server communication with the prerequisite that all fields needed and no appserver available.

So, remove the pause in the server code and do the prefetch better.

I don't want to copy each table separately into a temp-table on a client side and do the join there.  

Have you tried a smaller -Mm value already? As you are seeing many smaller packages, that might change things.

I wouldn't hold my breath waiting for more client-server improvements. You can enter an enhancement request here and see what happens.

SQL style server side joins that return result sets would be nice... so would 4GL stored procedures that run on the database side. I don't see either of those showing up since they haven't in the last 20 years.

Your only realistic choices are to use AppServer or remote desktops that are on the same local network as the database.

RE: Client network performance with a join

Reply by TheMadDBA

I wouldn't hold my breath waiting for more client-server improvements. You can enter an enhancement request here and see what happens.

SQL style server side joins that return result sets would be nice... so would 4GL stored procedures that run on the database side. I don't see either of those showing up since they haven't in the last 20 years.

Your only realistic choices are to use AppServer or remote desktops that are on the same local network as the database.

Stop receiving emails on this subject.

Flag this post as spam/abuse.

-- 
Tom Bascom
603 396 4886
tom@greenfieldtech.com

Progress certainly does :)

Only better because it is a full application layer which can be deployed on the DB server or replicated on multiple other servers as need be.

Hi Mike,

I analyzed the packets myself by human reading. It is as small as possible. Smaller -Mm didn't help here. But at least with the -Mm 16384 the first table was prefetched here quite good.

Don't move away from the Problem 8-) .

I need "only"  1280 customers with their first order. Do I need to wait 13 seconds or do I have to by an AppServer, Replication and NameServer Loadbalancer to get it in 1 second?

Thomas I agree that AppServers provide additional functionality that pure stored procedures do not.

I would still love to have 4GL stored procedures and true database level triggers (not run on the client), but I have mostly given up on Progress implementing them.

>> Don't move away from the Problem 8-) .

:-)

We aren't moving away from the problem... just trying to tell you the current solutions that are available through the 4GL.

With proper code and design you can get excellent performance with AppServers even across a WAN.

Running client server across a WAN will be painful at best.

While there are things that I want from AppServers that I haven't gotten yet, triggers is not one of them.  Triggers were a sop to try to boost performance back in the days before layered applications.  These days, we want the layers and it is far better to put all of the data access logic for any given table in one place so that one can tell what is happening.

I agree that some people misuse triggers and put way too much business logic into the triggers. This happens much less often in Oracle because the triggers can't just be overridden at the session level. It is either enabled or disabled for everyone.

There are still valid uses of table and field level triggers that you just want to happen all of the time no matter who is doing the update and how they are connected to the database. You can make the argument that a properly designed application with data layers would do the same thing.... but you and I know that the vast majority of applications (especially written in Progress) do not fit that mold.

I see triggers as another way for someone to do it wrong...

Thank you all for the comments,

I of course know the current "workarounds", but I hope someone get touched to solve this piece of performance problem.

My preferred way (remember, No AppServer, all fields needed) currently would be to copy each table individualy to temp-tables on the client and join it there. This is faster, than the server process. Funny isn't it?

13 seconds for a join like that seems very slow to me. I did a similar CS query (limited to 1300 records, two tables) on one of our development databases and it took about 2 seconds the first time, less than a second each time after that.

I don't consider this environment to be particularly fast.

Is there something unusual about these tables? The indexes? The network?

The Good Doctor: Just because people can use things improperly doesn't mean that they are bad. I have seen more mangled where clauses than I care to remember... I would not like to get rid of where clauses though ;-)

The 13 seconds were across a WLAN (which I took to mean WAN) as it not a network local to the server. Latency across a WAN is going to be much worse than a local network.

That's right,

I took WLAN to see the difference more easy. At least it was not too bad with 10MBit/s. But don't forget all data could be delivered within 1 second separately, but it takes 13 s in a join.

Oh the triggers,

yes these test's with the trigger were also funny. But these tests are without triggers, after I removed them.

To be sure, just because something can be misused is no reason to eliminate the feature ... statics, for example.  The point is that I see no reason to use the triggers here at all, so I don't want people to even think about the possibility.

Indeed... Got a little off topic talking about triggers. Stored procedures could be a nice solution for his network issues though.

AppServer obviously works but the pricing scares off some shops.

I have also struggled with client/server performance issues and logged enhancement requests for better networking performance, stored procedures and real database triggers among others:

community.progress.com/.../add_server-side_joins__better_networking_performance.aspx

community.progress.com/.../real_database_triggers_and_stored_procedures_abl.aspx

I have also talked directly with Progress people trying to convince them to implement these things but they have not given me much hope. I always get the same answer: use AppServer.  In our case with a large existing application, it is practically impossible to make it fully AppServer-enabled. And another fact is that most of our customers are small or medium sized companies, for them the additional cost of AppServer is often too much.

If we can't get better client/server performance without AppServer, I think we should at least have an easier way to adopt AppServer. Ideally, programs would be AppServer-enabled without changing code. So, when you have "for each customer, each order of customer" in your code, the client automatically sends the query to AppServer (if it's available) and gets resulting data in a temp-table/prodataset. 

Note that AppServer + WebClient would end less than the cost of Client Networking.