Adam,

<< I haven't had any experience of prepare-times varying in the way you say.
It might be something to do with structures on disk? >>

Even if it is, the problem is that the layout of the rows, index pages, etc.
will continue to become "disorganized" as the tables are updated, etc.  IOW,
Roy would have to continually optimize the tables experiencing this, and
even then it won't solve the issues with navigating a table using different
active indexes.

The bottom line is that the network is going to make certain I/O access
patterns prohibitively expensive, and while you may solve some of them, you
won't solve them all without going to C/S.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< That sounds fair enough but I'm not fully convinced I can't get my
>head around why it did with such a variance so consistently. >>
>
>Go check out Process Monitor from MS (formerly SysInternals):
>
>http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

I may do that but I use it so infrequently that I always have difficulty getting my head round the volume of data generated.

>and monitor all I/O in your application. That will convince you of where
>the issue is - the network.

Whilst the network exacerbates the effect running the same check program local, single user demonstrates it as well. Over the LAN or locally difference locally I get 4+x difference. Locally its around 9 ticks vs 50 ticks which make it difficult to spot with the naked eye but its the same three tables (Contacts, Career, Sites) at the top of the league.

I get the same effect local/fileserver single/multi user. It may be Windows buffering, it may have something to do with oplocks and SMB but what makes me suspicious is its picking on the same three tables every time. I'd expect some variability when PCs are turned off / on.

Roy Lambert

ps to make sure it was local and there could be no network effects I unplugged the network cable, turned off wireless and restarted the PC.

Tim


Found it I think. How does this sound:

The three tables that take a lot longer to prepare are those with triggers that involve an external function in a dll so the extra time relates to the dll being loaded from disk into local memory or something. I'm sure you'll know.

It would explain the consistent differences, the differences in actual time for local / wired / wireless etc.

The pure sql triggers with no calls to external functions of any sort are fine (there may be a bit of difference but its below my interest threshold) and I don't have any triggers calling sql based external functions but I suspect they would be fine.

The question is what if anything can I do about it? I don't think there's a chance of rewriting the offending trigger in sql (it uses arrays, stringlists and is called from Delphi as well).

Roy Lambert

Roy,

<< Whilst the network exacerbates the effect running the same check program
local, single user demonstrates it as well. Over the LAN or locally
difference locally I get 4+x difference. Locally its around 9 ticks vs 50
ticks which make it difficult to spot with the naked eye but its the same
three tables (Contacts, Career, Sites) at the top of the league. >>

The fact remains that the network is what makes a formerly negligible amount
of overhead a problem, unless 50 ticks is a big problem now.

Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< The three tables that take a lot longer to prepare are those with
triggers that involve an external function in a dll so the extra time
relates to the dll being loaded from disk into local memory or something.
I'm sure you'll know. >>

I'd have to profile it to say for sure, but yes, it could theoretically take
that long (50 ticks) for the OS to open/load the DLL and then have EDB bind
the appropriate functions.

<< The question is what if anything can I do about it? I don't think there's
a chance of rewriting the offending trigger in sql (it uses arrays,
stringlists and is called from Delphi as well). >>

Sorry if I'm getting repetitive, but the answer is the same:  don't use them
over the network - use the EDB Server.   If you *must* use network access,
then the only thing that may help is opening the affected tables using
TEDBTable components at app startup and keep them open for the duration of
the app.  That's the only way to keep the triggers prepared, and
subsequently, the called procedures loaded along with their DLL instances.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>The fact remains that the network is what makes a formerly negligible amount
>of overhead a problem, unless 50 ticks is a big problem now.

Depends on whose clock we're using!

Roy

Tim

>Sorry if I'm getting repetitive,

You are but a) I'll forgive you and b) continue to ignore you

>then the only thing that may help is opening the affected tables using
>TEDBTable components at app startup and keep them open for the duration of
>the app. That's the only way to keep the triggers prepared, and
>subsequently, the called procedures loaded along with their DLL instances.

There's only one external function (and only one external dll) involved in the triggers and opening any one of the tables seems to "solve" the problem.

Do I need to do this per function, per dll, per database or what?

I'm guessing its at least per database so creating a mock table in an in-memory database to load and bind things wouldn't work for the disk based tables.

Would it be a silly suggestion to ask for a function to say LoadAndBind these functions for these databases?

Roy

Roy,

Here are a few suggestions you can mull over.

The DLL unless it is huge, won' take long to load into memory from disk.
To get around this disk loading, you can create a do nothing .exe on the server at startup that loads the dll and the program just sits there (minimized)  to keep the dll in memory. Other programs on the server will then be able to reference the dll in memory (won't have to reload it from disk).

The slowdown may also be due to the DLL initialization that gets executed by every process.

Are you using load time dynamic linking, or run-time dynamic linking (LoadLibrary)?

I'm wondering if switching to a Delphi Package would eliminate the problem?

Barry

Barry


>The DLL unless it is huge, won' take long to load into memory from disk.

After representations Tim reworked the framework so the dll is quite small now - 763Kb.

>To get around this disk loading, you can create a do nothing .exe on the server at startup that loads the dll and the program just sits there (minimized) to keep the dll in memory. Other programs on the server will then be able to reference the dll in memory (won't have to reload it from disk).

>The slowdown may also be due to the DLL initialization that gets executed by every process.
>
>Are you using load time dynamic linking, or run-time dynamic linking (LoadLibrary)?
>
>I'm wondering if switching to a Delphi Package would eliminate the problem?

Its Tim's dll (well I wrote it using Tim's framework but its used by ElevateDB) so I don't have control there.

From my experiments so far I only need to open one table and leave it open and that means I only get the hit once for the app. Depending on Tim's answers to my questions I may end up creating a special table just to force all the dlls and functions to be loaded.

The big problem was tracking down the root cause and for that, ultimately, I did have to resort to Process Monitor and my brain still hurts!

Roy

Barry,

<< Are you using load time dynamic linking, or run-time dynamic linking
(LoadLibrary)? >>

EDB uses LoadLibrary for all external modules.  It keeps them open as long
as any of the modules are being used, and unloads them when they're not in
use.  This is done to allow the modules to be replaced while the EDB Server
is still running.

Tim Young
Elevate Software
www.elevatesoft.com