Xref: utzoo comp.databases:7949 comp.sys.mac.hypercard:5096
Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!apple!bc
From: bc@Apple.COM (bill coderre)
Newsgroups: comp.databases,comp.sys.mac.hypercard
Subject: Re: Accessing an Oracle database from the hypercard interface
Message-ID: <46800@apple.Apple.COM>
Date: 25 Nov 90 17:02:48 GMT
References: <499@n5pph007.UUCP>
Organization: Apple Computer Inc., Cupertino, CA
Lines: 93

In article <499@n5pph007.UUCP> tpg@n5pph007.UUCP (T Peter Graham) writes:
|We are beginning work on a large Oracle-based system that we want to front-end
|on the Mac using Hypercard.  The databases itself will be running on an HP 
|unix machine, with the access from the Macs being through SQL*Net.  The
|problem is that we are seeing a 20-28 second delay on EVERY database access.
|The bottleneck doesn't seem to be on the HP end...the data is being 
|sent to the Mac with little to no delay.  There then seems to be some kind of 
|hangup in Hypercard.  This delay occurs regardless of the type of access or the 
|amount of information returned.  I have several questions:
|
|    Is this problem inherent in using the Mac's bit-mapped interface?

Definitely not. It may be the fault of the Hypercard handlers
"catching" the data, or it may be the overall design of the data
pipeline. There might also be some kind of network problem, but in
general:
	the Mac can do very quick TCP, via a Hypercard interface.

	Hypercard can receive fairly large (30K) chunks of data and
	deal with them quickly.

|    Has anyone else seen/worked through this problem?

As a contractor to Apple, I used Sybase's HyperDBLib product for
similar purposes. It was not without its drawbacks, but it did the job
much faster than yours. In particular, it shipped back data one
element at a time. So if a query generated, say 100 rows of 10
elements each, the response might take 100 seconds. Sybase told us that
they were working to eliminate this bottleneck. Our workaround was not
to ship thousands of elements. another workaround was to ship the data
back as an ascii image (1 element) and then reparse it at the
Hypercard end. Almost as slow.

I suggest you find the person who wrote your Hypercard interface to
the network (it might be an Oracle package, or something custom) and
talk to them. It might be possible to change the interface protocol
slightly and drastically speed things up. It might also be possible to
take some chunk of Hypercard (for example, an input parser) and have
it written in C as an "XCMD" that Hypercard can call. That definitely
will speed things up, if you pick the right things.

I also suggest you call Oracle Tech Support to get them to give you
help with your specific problem. If they can't help, they might at
least be able to point you at other people who have done similar
things. The info in your letter isn't specific enough to pinpoint the
problem, and I haven't worked with Oracle's package.

Having written several Hypercard front ends for databases myself, I
can assure you that it is indeed a fine idea to use Hypercard. It's
usually fast enough to code the entire UI, can handle almost any
necessary computation quickly enough if you are clever, and is easy to
test, modify, extend, and customize. You could make an extremely
friendly "intelligent database editor", for example, displaying the
data in human-friendly format, decoding all your flag fields into
radio buttons, checking user input for consistency, and providing
"human readable" printouts, all with a minimum of SQL, in about 50 or
60 pages of Hypertalk. Adherence to a few simple rules will ensure
that your front end is about as quick as possible:

	1	Minimize data exchange with the host
		1a make sure each query returns the minimum it can
		1b never use two queries when you can join

	2	Use OOP style coding practices.
		2a put your database communication code into one script
		2b isolate your interface to the host

	3	Use quick algorithms instead of code tweaking

	4	placate the user
		4a only perform long operations when users expect them
		4b use user feedback devices to tell how much longer

	5	Don't do extra work
		5a the host can usually do the work much faster than HC
		5b if you work hard to add an extra feature that isn't used,
		   rip it out.

The code I wrote, a scheduler (ala chron), used custom hypercard
editors to access the databases on a Sun running Sybase, via
Transact/SQL and HyperDBLib. Even when the databases contained a lot
of records, response time was reasonable, even on a Mac Plus. On any
II-class machine, the response time was almost as good as a custom C
application, and I was able to test my stuff with users on a
day-to-day basis.

While I don't think a Hypercard front end would work for situations
where thousands of records must be processed at the Mac end, even then
Hypercard would make an excellent test-bed for ideas and interfaces,
which would then be handed off to a C programmer as a project spec.

bill coderre
private consultant, not apple sposkedroid, today