Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

image database slow on non primary path of detail dataset

SILVERSTAR
Frequent Advisor

image database slow on non primary path of detail dataset

We have two image database on hp3000 mpe 6.0
On the first one I find 1500 records with query in more than 10 seconds on a table of 4000000 records searching a key non primary with this characteristics:
Master data set type Automatic
capacity 250007
entries 175509
load factor 70.2%
secondaries 28.2%
max blocks 3
block factor 34
max chain 7
ave chain 1.39
Std dev 0.66
expd blocks 1.00
avg blocks 1.01
ineff ptrs 0.8%
elongation 1.01

Detail data set
capacity 4200018
entries 3142754
load factor 74.8%
highwater 3142754
block factor 6
search key3
max chain 7830
ave chain 18.91
Std dev 89.65
expd blocks 3.73
avg blocks 17.47
ineff ptrs 87.1%
elongation 4.69

On the second one I can find 1500 records with query in less than 1 second on a table of 4000000 records searching a key primary with this characteristics:
Master data set
type Automatic
capacity 200001
entries 163966
load factor 82.0%
secondaries 31.8%
max blocks 14
block factor 21
max chain 7
ave chain 1.47
Std dev 0.72
expd blocks 1.00
avg blocks 1.12
ineff ptrs 9.4%
elongation 1.12

Detail data set type detail
capacity 3700002
entries 3056810
load factor 82.6%
highwater 3056810
block factor 2
search key1
max chain 7468
ave chain 18.64
Std dev 88.55
expd blocks 9.63
avg blocks 10.53
ineff ptrs 51.1%
elongation 1.09

The second one has been built by our cobol programmers as a copy of the first sorted by key3 (renamed key1) because the search on the first one was to slow. In the second (faster) we have on master per detail. In the first (slower) the master is shared by the detail data set.

In Oracle I simply build an index on the first db on the non primary key and it works. I don't make a copy of the table sorted on a second key to go faster.

How can I go faster also on a non primary key on turbo(!!!) image?
1 REPLY
Mark Landin
Valued Contributor

Re: image database slow on non primary path of detail dataset

I strongly suggest you repost this question in the mpe-ix/databases forum, as you will find more Image/SQL experts there than on the hp-ux/database forum. :)

Also, you may consider posting your question to the HP3000-L mailing list. There level of Image/SQL knowledge present on that mailing list is exceeded only in HP Labs (and not by a large margin, I bet). You can join the list at:

http://raven.utc.edu/cgi-bin/WA.EXE?SUBED1=hp3000-l&A=1.

Alternatively, if you access to Usenet, the 3000-L mailing list is gatewayed to the newsgroup comp.sys.hp.mpe.

I am sure someone there can give you the insight to solve your problem.