Hi all,

This is a long one, so bear with me.

We are revamping our application to take it out of the pick world and
into the .net world. One of the requirements of this project is to be
able to release it in modules - thus the new application must be able
to talk back with the old application. To do this, we have gone
through the ODBC flattening process for both databases (the actual
steps involved were that we would create a new pick database, design
our own files that made sense in the RDBMS world, and would then edit
the md/voc to point to the legacy database. This would allow for the
legacy application to continue talking to the legacy database while we
wrote to the pseudo new database). So, after flattening the database,
we design our objects, build the gui(web based app) and run all of our
queries through ODBC (consequently, the version of D3 we are using is
7.2 and 7.3 and the version of UniData we are using is 6.0). Now, we
have one set up where the web server, D3 and UniData are all running
on the same box, and another set up where the web server, D3 and
UniData are running on separate boxes. In one of our processes, we
run a series of statements against the database. The first is to
select everything from table A. The second is to select all of the
items from table B that relate to table A. We loop over the results
from Table A and using the results from B, we do validation(3 queries
per loop iteration). Following the validation, there are 3 insert
statements and 2 delete statements that are run. Now here is where
the fun part is. If D3 is running on the same system as the web
server and we go to execute the code, the time to run through a loop
of 10 is 7.8437500 seconds. Same test using UniData is 01:50.2968750
seconds. Same test using SqlServer is 0.5156250 seconds. Now, if we
move to the model where the web server is on one box, the db server on
the other, the time changes. D3 is 01.1115984 seconds, UniData is
02.2231968 seconds and SqlServer is 00.7610944 seconds. The code base
is always the same, all of the tables are, for the most part, the
same(some have slightly different column names). The only db server
that returns results that are "normal" is Sql - the results it is
posting show that it is faster to run everything off of one box - less
network traffic - than it is to run it off of two boxes. The fact
that there is such a huge descrepency in time between D3/UniData when
they are running on the same box as the web server and on a different
box than the web server is leading me to assume a few things:

a) the box that has both web server/db server on it is bogus and
needs to be rebuilt
b) D3/UniData cannot handle being on the same box as the app
server/web server
c) D3/UniData cannot perform well under ODBC.

Does anyone know of anything that I should be aware of?

Thanks

Tyler Davey
tdavey (AT) ptsoft (DOT) com

tdavey (AT) ptsoft (DOT) com (Sparky) wrote:
I wish I could help with the performance issue but SQL Performance
isn't my bag.

I would suggest you look into the Pick Data Provider .NET from Raining
Data. Same code works with D3 and UniData, no need to flatten files,
and depending on how you tie your UI and mid-tier components from
back-end data access, you can transition more easily from this to
other databases.

If you need more info or assistance, please let me know.
Tony
TG (AT) removethispartNebula-RnD (DOT) com

If you are going thru the trouble to rewrite your application why
don't you follow the recommended 3-tier model. Even microsoft's
latest .net product suggests you follow this model. In a 3-tier model
you eliminate from your client interface any direct connections to the
database and instead use a middle-layer routine to do that work. Let
me give you an example I just fixed this morning.

Machine Layout:
Primary: d3-aix 215 user Main machine
Web Server D3-Linux running web processes

The d3-linux machine communicates with the D3-Aix database via OSFI.
If you are not aware of OSFI, this is a d3 mechanism that allows one
d3 machine to see files on another. It works kinda like SQL in that
it actually hands a select statement to the remote machine, instead of
pulling the data across the network.

I had a particular query that worked as such

DATA 'SELECT LOANS WITH CORRESPONDENT = "XXX"'
EXECUTE 'SELECT LOANS.REPORT WITH ORIG.YEAR.MONTH = "2003-7"'

Loans report is a special index file that stores all my dates in
different formats, allowing quick index selects. The overall database
has over 160 thousand records, with the loans file items usually being
pretty large.

On the Primary machine the query is immediate. The first query
returns around 6000 records (a narrowed down search) while the second
query then returns 6.

When I run this from my web server (both files are accessed via OSFI)
the first query is immediate (6000 items selected), but the second
query takes 10+ seconds. I am guessing that I am exceeding the
ability of the OSFI and am now probably pulling all 6000 records
across the wire for the second query.

Even though I could probably fix this by combining this into a single
query, I have many other queries that require this type of stacking.
I have recently implemented a RPC Style function (using http and soap)
that allows my D3-Linux machines to initiate a RPC call to the D3-Aix
machine (basically allowing d3-linux to call routines that execute on
the d3-aix machine).

I therefore created a SOAP.EXECUTE.COMMAND function on the D3-Aix
machine. I then pass a string that has my command(s) I wish to
execute, (i.e.)

COMMAND='SELECT LOANS.REPORT WITH ORIG.YEAR.MONTH = "2003-7"'
COMMAND<-1>='SELECT LOANS WITH CORRESPONDENT = "XXX"'

My RPC command running on D3-Aix takes this command and executes it
locally, getting an immediate response. It grabs up the IDS and
returns them as a string. Now my query from the web server is
immediate.

The real moral of the story here is whenever you directly attach to
your data over a network wire you will hit performance snags, even
with SQL based systems. Accessing data on the same machine is always
faster! It is much better to have contained routines on your servers
that do all the cross query work and return to you only the data you
need. This is pretty much the direction that microsoft is pushing
with .net (with asp.net and disconnected datasets).

There are many solutions for this on Pick systems. Doug at Modsoft
has created a series of RPC functions that are cross platform
independent and work on top of Coyote. D3-'s pdp provider also has a
mechanism for calling remote functions of the machine (I believe that
is the case). All the web providing packages can do this with a
little coding (as I have done).

If you are still deterined to eventually port completly off pick, this
direction is still doable. In this senario I would.

#1: Get pick PDP package and write .net asp front end modules that
access the data from pick and publish the routines as SOAP calls. I
would NOT directly access files but instead write pick routines to
return datasets that you would call via the PDP package.
#2: I would then write my front end package to call the asp.net soap
calls.
#3: As I port off pick, I would then be only required to re-write the
..net ASP calls to reference whereever the data is currently.

We actually are using the 3 tier model when it comes to Sql Server, as
we are able to write Stored Procs and access them through our DAL
layer in .net. The architecture of our system is as follows:

ASP.net acts as our GUI that makes calls to our Business/Middle Layer.
Our middle layer access our data via our DAL layer that allows us to
use multiple databases. That layers, depending on which database we
are using, will either hit the database for a stored proc(SQL/Oracle)
or use in code sql statements to produce the same results(D3/UniData).
when implemented over D3/UniData, we are using a convuluted 3 tier
model in that the GUI, Business Layer, and Data Layer are kept
seperate from each other, but we use inline sql statements instead of
sql statements that are contained within the db.

I agree, except that my performance testing is proving that as wrong.
If the web server and db server are on the same machine and I'm
accessing it, D3 and UniData take upwards of 5 times longer to access
the data than if I access it from a load balanced architecture with
the web server on one machine and the db server on another machine -
reread my original post and you'll see the numbers that i've
collected.

This is not possible for several reasons, the main one being a
knowledge and resource crunch. For the re-write of the application,
the developers are strong in the Sql Server / Oracle world (RDBMS) and
very weak in the Pick world. Our legacy developers that know Pick
cannot write the routines as they do not have time to (busy acting as
billable). So really it comes down to a "get it done with the
resources you have" issue. Everything is working great from a design
and coding perspective. What the issue is is that why does it take so
long for D3/UniData to execute SQL commands through ODBC? Keep in
mind that we are using Windows as our server. We are talking huge
differences in numbers that are not related to the server or the code.
UniData takes almost 2 minutes to execute approximately 30 inserts,
10 deletes and 3 queries. D3 takes about 20 seconds to do the same.
Sql Server takes less than a second to do the same. This is when
everything is isolated on one system - no network bandwidth used at
all, all calls made on one machine. If I place the web server on one
system and the db server on another, performance increases
substantially to run the same queries - D3 about 1 second, UniData 2
seconds and Sql is sub second. Again, as per your email, the results
being posted by UniData and D3 are disturbing as it SHOULD be faster
to run them on the same box then on seperate boxes. So where does the
problem lay? ODBC? Windows? D3? UniData? .net? Load Balancing? The
Server? How can the same code take 2 minutes on a self contained
system and 2 seconds when load balanced? Does pick battle with .net
processes for cpu usage? Is there a contention issue that we don't
know about? You cannot tell me that it is normal for D3/UniData to
take 5 - 50 times longer to run queries on the same box then it does
when the web server is removed and placed on a different box.

Sorry, I should have read the post closer. You are probably on the
right track by looking at resource usage on the one machine. Perhaps
a ODBC client uses different mechanisms if it is local or remote
(named pipes vs sockets for example).

It is unfortunate that you cannot utilize RPC/Soap style calls. I
prefer maintaing as much control as possible over the process. With a
SOAP call you can optimize and easily control how things are working.
Utilizing ODBC you are forced to rely on the vendors ODBC driver and
how they implemented stuff. As you have found there must be some
resource issue and it now looks like you must find some NT performance
tools and see where the bottleneck is at.

- Patrick

tdavey (AT) ptsoft (DOT) com (Sparky) wrote in message news:<25cce448.0309220915.58b89d5a (AT) posting (DOT) google.com>...

I don't think this is it, but I'm wondering if it's a DNS issue (and I
do mean Domain Name Server). Are you using a DSN (and I do mean Data
Source Name) which refers to localhost with some name that must be
resolved elsewhere? Are you using a fully qualified host.domain.tld
reference? Try adding an entry in the local hosts file or use
127.0.0.1 as the server address.

The fact that both UniData and D3 are both behaving similarly means
the issue is probably outside them.

It's not clear which system you have hosting the DBMS s/w: Have you
tried connecting to that same degraded DBMS box via ODBC from another
client with Excel and MS Query?

For D3, have you tried running D3 and the ODBC Server in foreground so
that you can watch inbound queries? It should be obvious if the delay
is in the connection or in the turnaround after the connection.

Dumb question, but how are your file sizes (frames going into
overflow)? Are you using indexes?

HTH.
Tony

Tried changing the name to both fully qualified and localhost IP and
actual IP, neither affected the performance - actually, changing it to
the static IP address slowed it down a bit >(. I agree, I think that
the issue is probably not D3 and UniData. What I personally think it
is is the ODBC drivers issued by them cannot handle queries that well.
We are trying to procure a copy of the .net data provider from
Raining Data, but we don't want to dish out 700 CDN for something that
we don't know if it works or not, so we are hoping for an eval copy.
I've used WinSQL to run the queries, and from a third party client
tool, the queries are all taking sub seconds to execute, so this is
where it's leading me to believe that it is something with the ODBC
data provider. Don't know how to run the ODBC Server in the
foreground to view the queries coming into D3. Is it similar to
Profiler from SQL Server? File sizes look good - no overflows on the
files being read or written to.

Tyler
Tony Gravagno <g6q3x9lu53001 (AT) sneakemail (DOT) com.invalid> wrote

tdavey (AT) ptsoft (DOT) com (Sparky) wrote:
I don't recall if you mentioned the platform D3 is running on. It
sounds like D3NT.

Here's info on starting the servers, but since it looks like this is
not a DBMS-related issue then this might be academic.

Start D3 in the foreground with "d3vme /debug" rather than starting
from a service. Then start the ODBC server in the foreground with
"d3odbcsv -dpv". If you use the /debug option on that as well as the
-dpv switches it behaves differently (uses port 1924 instead of
1904?). I don't know the details on this. Mark Brown is the expert
in this area and I hope he follows-up on this.

It _used_ to be that D3 and the ODBC server must both be started as
background or both be started as foreground if they were to work
together. I don't know if that is still the case, which is why I
suggest starting D3 in the foreground with ODBC.

I do suggest you check out PDP.NET since you're working with the newer
tecnologies anyway. Someone here said a 30 day eval is free. I know
there are other issues here, but ODBC is painfully slow compared to
other methods, and PDP might be a good way to buy you the time you
need for the future without having to flatten your data for relational
queries.

HTH
Tony

I have tested this..

You can try

www.conduitit.hostecom.com

its a small comp but it works..fast..

I dont know about D3, but one thing to note about unidata is that if
you are running it on a box with other applications, you would normally
have to tune the udtconfig parameters accordingly.