Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Memory Tables / Queries & Performance Options
Wed, Oct 1 2014 10:01 PMPermanent Link

Adam H.

Hi,

I'm just thinking out load (and probably right out of the box too here).
I'm running an application with DBSRVR. Within the application are some
queries that do some heavy processing work.

There are multiple queries in the one TQuery component which heavily
relies on inserting and updating data in Memory tables before giving the
end result. This is where the majority of the work is done.

What has been noticed is that given an example of one of these reports -
it takes around 2 minutes to execute. Using perfmon - we can see that
disk read/write access are at a minimum - most of the work is done with
one of the CPU cores clocked at close to 100% for DBSYS.

This has me thinking - in this instance - would I be better off having
the users process this data locally on their own workstations?
(Considering that their CPU clock speed may infact be faster than the
servers). A single query uses a single thread, so the benefit of
multiple processors on the server can't be taken advantage of.
(Obviously if a 2nd user is connected they will use a separate
processor, but I'm talking about performance for a given user here).

And... if it is better to do it this way - what would be the best way to
transfer data from the server to use in local memory tables. Is there an
option in DBISAM that I can use where memory tables can be loaded and
executed locally even though the data is using stRemote - or am I
barking up the wrong tree? Smile

Cheers

Adam.
Thu, Oct 2 2014 6:11 AMPermanent Link

Jose Eduardo Helminsky

HPro Informatica

Adam

You can use two table components, one connected to a remote session and other connected to a local session and then use LoadFromStream to do that. I realize, at least in most of my applications that use of memory table connected to local session is better than remote.

Eduardo (HPro)
Thu, Oct 2 2014 10:35 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Adam


Awkward questions to answer, so much is dependent on hardware and data.

However, sticking my neck out, here's a simple test:

If it runs a lot faster when running the entire thing in single user fileserver mode on a single PC then think about messing about. By lots faster I'd look for about 3x faster.

My logic (such as it is) goes - if its CPU bound and you have a faster PC then you should get some gains, however, to offset that you have additional traffic over the network, and you're also introducing a more complex environment and, possibly, creating difficulties with data synchronisation.

You could well get more gains by looking how the data is extracted / generated and splitting some of that off into threads.


Roy Lambert
Mon, Oct 6 2014 6:22 PMPermanent Link

Adam H.

Thanks Eduardo

> You can use two table components, one connected to a remote session and other connected to a local session and then use LoadFromStream to do that. I realize, at least in most of my applications that use of memory table connected to local session is better than remote.

That could work. I'll have to give this a shot and see what sort of
performance differences there are. Thank you!
Mon, Oct 6 2014 6:24 PMPermanent Link

Adam H.

Thanks Roy,

In this instance the queries are only designed for reporting - not for
altering database data. The memory tables are only used for the
compilation of the data in a format that the report needs in the end.

But good point about it needing to be significantly faster. I'll have a
play with what Eduardo recommended and see how it goes.

Cheers

Adam.

> Awkward questions to answer, so much is dependent on hardware and data.
>
> However, sticking my neck out, here's a simple test:
>
> If it runs a lot faster when running the entire thing in single user fileserver mode on a single PC then think about messing about. By lots faster I'd look for about 3x faster.
>
> My logic (such as it is) goes - if its CPU bound and you have a faster PC then you should get some gains, however, to offset that you have additional traffic over the network, and you're also introducing a more complex environment and, possibly, creating difficulties with data synchronisation.
>
> You could well get more gains by looking how the data is extracted / generated and splitting some of that off into threads.
Image