Hi,
I'm experiencing performance problems running simple queries on
UniVerse.
At the Retrieve command prompt I'm running the following query:

The JOB.ORDER table is very big however response times are around 10
minutes.

I've ran the LIST.INDEX command:


I'm curious because I'm sharing this database with another system which
has no problems with what I assume to be a very similar query.. any
ideas?

Cheers,
Graeme.

What type of file is it?
use HASH.HELP and FILE.STAT to give an idea of file usage.
Indexes will help, but isnt a subsititue for poor file creation choices

Are the machines configured the same (memory, disk, raids)?
Are the machines both loaded the same, same number of users when you
run the query?
HTH
Rob

Thanks,

I will run HASH.HELP and FILE.STAT...

The other application runs on the same machine as the DB server, As
does my query because it is being executed from a Retrieve command
prompt (after telnet on to the server)

Output of HASH.HELP:

File JOB.ORDER Type= 18 Modulo= 535333 Sep= 2 05:12:41pm 15
May 2006
PAGE 1

Of the 674501 total keys in this file:

0 keys were wholly numeric (digits 0 thru 9)
(Use File Type 2, 6, 10 or 14 for wholly numeric keys)

0 keys were numeric with separators (as reproduced below)
0123456789#$%&*+-./:;_
(Use File Type 3, 7, 11 or 15 for numeric keys with
separators)

674501 keys were from the 64-character ASCII set reproduced below

!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`
(Use File Type 4, 8, 12 or 16 for 64-character ASCII keys)

0 keys were from the 256-character ASCII set
(Use File Type 5, 9, 13 or 17 for 256-character ASCII keys)

The keys in this file are more unique in their right-most four bytes.
The smallest modulo you should consider for this file is 579653.
The smallest separation you should consider for this file is 2.
The best type to choose for this file is probably type 7.
Press any key to continue...

File name = JOB.ORDER
File type = 18
Number of groups in file (modulo) = 535333
Separation = 2
Number of records = 674501
Number of physical bytes = 813363200
Number of data bytes = 422272280

Average number of records per group = 1.2600
Average number of bytes per group = 788.8030
Minimum number of records in a group = 0
Maximum number of records in a group = 7

Average number of bytes per record = 626.0514
Minimum number of bytes in a record = 256
Maximum number of bytes in a record = 11808

Average number of fields per record = 194.3784
Minimum number of fields per record = 75
Maximum number of fields per record = 232

Groups 25% 50% 75% 100% 125% 150% 175% 200% full
152407 41670 128188 29781 65621 39253 25333 53080
Press any key to continue...

I'm new to UniVerse not sure what this is telling me.. any ideas

Goo'day,

On 15 May 2006 05:31:52 -0700, graeme.robb (AT) gmail (DOT) com wrote:

With the size of the file and the number of records, why don;t you
create an index, at least for the definition "PROPERTY", as well as
any other "alternate key" you might want to use...?

I would have thought that if you had the wherewithal to *look* for an
index, you'd also recognise the need for it.....<g>


Bruce Nichol
Talon Computer Services
ALBURY NSW Australia

http://www.taloncs.com.au

If it ain't broke, fix it until it is....

graeme.robb (AT) gmail (DOT) com wrote:
[...]

What do you mean by 'sharing this database'?
If this Universe file is on a different server & has to load through a
network that could explain it.
10 minutes for a simple list on a half million items seems a tad slow.
All depends on what sort of system you have, how heavily loaded it is.
On a server that also ran a certain MySQL query I could get a much
worse response time than that. Other times half a million items would
take about a minute or less to scan through.
I assume this 10 minutes is a consistant time? ie if you run it again
at a different time of the day you get similar result.
If it runs faster in the morning than say, the afternoon, suspect some
other process is hogging the system.

Jeremy Thomson

yea.. this is why I'm confused..

The other application runs on the database server. And it doesnt
require unirpc to be running :S

My test is executed by telnet onto the database server and executing
the command at a Retrieve command prompt. ie. also executed on the
server.

But I agree that it is overly slow..

I suppose I'm wondering if there is a way to query Universe other than
executing Retrieve commands such as LIST and SELECT..

SQL. With a basic program .... why? what problem are you having? OK,
you said "performance" when compared to another database in the OP, but
not details of what that is, indices etc.

Have you tried creating the index as suggested? Nothing like letting
the database be your friend :-)