 | |
#11
 
| |||
| |||
|
Re: Problem with long keys & ACCESS -
07-13-2006
, 04:25 AM
|
I cannot help but think about an post a few days old where 'collections' where being emulated via temp files! Whoops... only works with certain data sets (short data, no AM marks ..etc) - But hey! We don't need no 'new' ways of doing stuff... (Loving the move to 'modern' languages!) dave "Dale" <benedictknowspam (AT) silk (DOT) net> wrote in message news:12bbatn7hgbg071 (AT) corp (DOT) supernews.com... Donno about D3 7.4 but in my experimentation 7.2.1 has a item id length limit of 99 or 100 characters. Regards, Dale "Alan Pritchard" <alan.pritchard (AT) gmail (DOT) com> wrote in message news:memo.20060711194830.11488A (AT) aovq45 (DOT) cix.co.uk... Just when I thought things could not get more complicated. I've been having some problems with ACCESS selections on my database. This is d3 7.4.0 on RH Linux 2.4.20 SELECT TOWN.REF WITH LAT OR WITH LAT.DEC 9m items selected out of 13m [paraphrase!] now to refine the numbers some more & knock out items flagged for deletion SELECT TOWN.REF WITH COUNTY [DEL] OR WITH CHK DEL] 60K items selected out of 80K Now this is WRONG! There should be around 3.5m items. So I've copied those 60K and noted the last one to be copied in a file. Then done an ITEM on the record ID. The last or next to last record ID in the group is very long. I know where it came from, but it does not seem to have caused any problems previously. Only since d3 and Linux were upgraded as month or so ago. Luckily I have an independent index file and can find these very long IDs. In each case I can retrieve the record and chop down the ID to something more normal. When I do this, I can get further though the file. The problem is that I don't know just how many there are. In one case that I am looking at now, the ID has a length of 101. There may be longer ones, even. I thought at first that I had somehow got real corruption in the file that was causing file truncation. This is a pain when I am trying to get a large job out quickly, but I can cope with it. What I am interested in, though, is whether the assembled experts would expect this pattern of behaviour to occur with very long record IDs of this type? Alan Best wishes Alan Pritchard MPhil FCLIP MBCS The GLOBAL GAZETTEERT: the world on file http://www.allm-geodata.com Tel: +44 (0) 1202 417 477 Please reply to: alan.pritchard (AT) gmail (DOT) com |
#12
| |||
| |||
|
|
In article <1152710480.312734.154890 (AT) m79g2000cwm (DOT) googlegroups.com>, michael (AT) preece (DOT) net (Mike Preece) wrote: *From:* "Mike Preece" <michael (AT) preece (DOT) net *Date:* 12 Jul 2006 06:21:20 -0700 How easy would it be to re-engineer things so that the IDs are of a more sensible length? Very difficult. |
return to the UK next month and am looking for work there. This is the
sort of challenge I'd relish. If you were to take me on for, say, a
three month contract on low market rates - and I fail to solve this
(enormous ID) issue and a few more besides, you can post back here to
say I'm not up to snuff. If I succeed though, you'll have to tell
everybody how good I am - and we'd have to renegotiate the rate if you
want to keep me on.
Mike.
Quote:
|
Who is responsible for designing a system with enormous IDs in the first place? I am. The record key is a place or organisation name. I have never had a problem in 11 years with these until a couple days ago These things can and should be a doddle to fix if everyone called generic IO subroutines instead of doing direct read/writes/deletes/releases in the code - sorta like a CALLX but activated for reads etc., not just writes. I am not sure that this would work in my case. I take in data from a wide range of census, gazetteer, telephone area code etc data files, and each one is different, so I have to write specific programs to reformat each type into my standard database structure. Alan Alan Pritchard wrote: Thanks for the various comments. Picking up on some of the points. 1. I do have d3 indexes, but was not using them for this selection. The index I mentioned in my OP is a local inverted file of an index key that I maintain. 2. I am now going through each of these large-ID records, chopping the ID down (or deleting the whole record). What is so odd, though, is that some of these have been on for over two years with no problem at all. It is only since the d3/Linux upgrade that problems have arisen. Best wishes Alan Pritchard MPhil FCLIP MBCS The GLOBAL GAZETTEER™: the world on file http://www.allm-geodata.com Tel: +44 (0) 1202 417 477 Please reply to: alan.pritchard (AT) gmail (DOT) com Best wishes Alan Pritchard MPhil FCLIP MBCS The GLOBAL GAZETTEER™: the world on file http://www.allm-geodata.com Tel: +44 (0) 1202 417 477 Please reply to: alan.pritchard (AT) gmail (DOT) com |
#13
| |||
| |||
|
|
*From:* "Mike Preece" <michael (AT) preece (DOT) net *Date:* 13 Jul 2006 02:41:12 -0700 Alan Pritchard wrote: In article <1152710480.312734.154890 (AT) m79g2000cwm (DOT) googlegroups.com>, michael (AT) preece (DOT) net (Mike Preece) wrote: *From:* "Mike Preece" <michael (AT) preece (DOT) net *Date:* 12 Jul 2006 06:21:20 -0700 How easy would it be to re-engineer things so that the IDs are of a more sensible length? Very difficult. Really? OK. You know your system. I don't. Still - I'm planning a return to the UK next month and am looking for work there. This is the sort of challenge I'd relish. If you were to take me on for, say, a three month contract on low market rates - and I fail to solve this (enormous ID) issue and a few more besides, you can post back here to say I'm not up to snuff. If I succeed though, you'll have to tell everybody how good I am - and we'd have to renegotiate the rate if you want to keep me on. Mike. |
to go through the whole file, checking for the length of the ID,
truncating it to 45 chars and adding a number at the end to make the ID
unique, and write the record back to the file with the new ID. Delete the
old record with the long ID.
Seems to have solved the problem and I now have the number of records on
the second selection that I thought I should have.
Thanks for the offer of help, but I'm afraid I can't help, for reasons
that become clear in another posting!
Best wishes
Alan Pritchard MPhil FCLIP MBCS
The GLOBAL GAZETTEER™: the world on file
http://www.allm-geodata.com
Tel: +44 (0) 1202 417 477
Please reply to: alan.pritchard (AT) gmail (DOT) com
#14
| |||
| |||
|
|
*From:* "Ross Ferris" <rossf (AT) stamina (DOT) com.au *Date:* 13 Jul 2006 01:26:54 -0700 01 execute "create-file keysize-file 1 7" 02 open "","keysize-file" to file else stop 03 mykey = "" 04 loop 05 mykey := "A" 06 while len(mykey) < 1000 do 07 read rec from file,mykey then 08 execute "delete-file keysize-file" 09 print len(mykey)-1 :" is max keysize" 10 stop 11 end 12 write "" on file,mykey 13 print @(0,24):len(mykey): 14 repeat Gives 101 on D3/Linux 7.4.0, which corresponds "nicely" to the observation. Numerous ways to make keys smaller, especially in situations like I imagine you have here (we used to get very "tricky" with data warehouse structures. This limit used to be much smaller, but now we "cheat" with Visage.BIT structures and have "keys" much longer) Dale wrote: Donno about D3 7.4 but in my experimentation 7.2.1 has a item id length limit of 99 or 100 characters. Regards, Dale |
more fully.
BUT, the fact that it killed selections and appeared to truncate the file
is rather worrying. Law of unintended consequences at work.
Best wishes
Alan Pritchard MPhil FCLIP MBCS
The GLOBAL GAZETTEER™: the world on file
http://www.allm-geodata.com
Tel: +44 (0) 1202 417 477
Please reply to: alan.pritchard (AT) gmail (DOT) com
#15
| |||
| |||
|
|
In article <1152779214.669470.148540 (AT) h48g2000cwc (DOT) googlegroups.com>, rossf (AT) stamina (DOT) com.au (Ross Ferris) wrote: *From:* "Ross Ferris" <rossf (AT) stamina (DOT) com.au *Date:* 13 Jul 2006 01:26:54 -0700 01 execute "create-file keysize-file 1 7" 02 open "","keysize-file" to file else stop 03 mykey = "" 04 loop 05 mykey := "A" 06 while len(mykey) < 1000 do 07 read rec from file,mykey then 08 execute "delete-file keysize-file" 09 print len(mykey)-1 :" is max keysize" 10 stop 11 end 12 write "" on file,mykey 13 print @(0,24):len(mykey): 14 repeat Gives 101 on D3/Linux 7.4.0, which corresponds "nicely" to the observation. Numerous ways to make keys smaller, especially in situations like I imagine you have here (we used to get very "tricky" with data warehouse structures. This limit used to be much smaller, but now we "cheat" with Visage.BIT structures and have "keys" much longer) Dale wrote: Donno about D3 7.4 but in my experimentation 7.2.1 has a item id length limit of 99 or 100 characters. Regards, Dale That is interesting. That was the maximum on my system once I investigated more fully. BUT, the fact that it killed selections and appeared to truncate the file is rather worrying. Law of unintended consequences at work. Best wishes Alan Pritchard MPhil FCLIP MBCS The GLOBAL GAZETTEERT: the world on file http://www.allm-geodata.com Tel: +44 (0) 1202 417 477 Please reply to: alan.pritchard (AT) gmail (DOT) com |
#16
| |||
| |||
|
|
Hi Collections can be covered with numeric keys for LOOOOOONG data. Pick could not give a hoot whether it is sorting on attribute 1 or attribute zero. I must check if Ross Ferris's btree have a limit, we never hit it. Peter McMurray "dave" <stope19REMOVETHIS (AT) NOSPAMoptusnet (DOT) com.au> wrote in message news:44b60cd7$0$22362$afc38c87 (AT) news (DOT) optusnet.com.au... I cannot help but think about an post a few days old where 'collections' where being emulated via temp files! Whoops... only works with certain data sets (short data, no AM marks ..etc) - But hey! We don't need no 'new' ways of doing stuff... (Loving the move to 'modern' languages!) dave "Dale" <benedictknowspam (AT) silk (DOT) net> wrote in message news:12bbatn7hgbg071 (AT) corp (DOT) supernews.com... Donno about D3 7.4 but in my experimentation 7.2.1 has a item id length limit of 99 or 100 characters. Regards, Dale "Alan Pritchard" <alan.pritchard (AT) gmail (DOT) com> wrote in message news:memo.20060711194830.11488A (AT) aovq45 (DOT) cix.co.uk... Just when I thought things could not get more complicated. I've been having some problems with ACCESS selections on my database. This is d3 7.4.0 on RH Linux 2.4.20 SELECT TOWN.REF WITH LAT OR WITH LAT.DEC 9m items selected out of 13m [paraphrase!] now to refine the numbers some more & knock out items flagged for deletion SELECT TOWN.REF WITH COUNTY [DEL] OR WITH CHK DEL] 60K items selected out of 80K Now this is WRONG! There should be around 3.5m items. So I've copied those 60K and noted the last one to be copied in a file. Then done an ITEM on the record ID. The last or next to last record ID in the group is very long. I know where it came from, but it does not seem to have caused any problems previously. Only since d3 and Linux were upgraded as month or so ago. Luckily I have an independent index file and can find these very long IDs. In each case I can retrieve the record and chop down the ID to something more normal. When I do this, I can get further though the file. The problem is that I don't know just how many there are. In one case that I am looking at now, the ID has a length of 101. There may be longer ones, even. I thought at first that I had somehow got real corruption in the file that was causing file truncation. This is a pain when I am trying to get a large job out quickly, but I can cope with it. What I am interested in, though, is whether the assembled experts would expect this pattern of behaviour to occur with very long record IDs of this type? Alan Best wishes Alan Pritchard MPhil FCLIP MBCS The GLOBAL GAZETTEERT: the world on file http://www.allm-geodata.com Tel: +44 (0) 1202 417 477 Please reply to: alan.pritchard (AT) gmail (DOT) com |
#17
| |||
| |||
|
|
Peter, So, you are still using SuperB? It probably needs an update for the 4K frame sizes these days, so it can cram more effectively into frames - still the old R83/500 byte limit will reduce overflow. |
Second of rule programming. If it works don't fix it. However I trust you
so will contact in near future - you are obviously busy. (First rule of
programming KISS)
Quote:
|
SuperB does have a limit - but it is sooooo large I don't think you will find it in a hurry (and you will need some VEEEEERY BIIIIG disks) Speaking of HUUUUUGE projects, I've been buried for a few months on a proposal that I have to finish over the weekend (there is a thread that I haven't had time to respond to yet - you can guess which one) - this will see us collecting data from around 4,000 SITES. Database is projected to grow at the rate of 2Gb/day, with 5 year data retention, so we are talking about a database that will end up in the 5-7Tb range at the current rate, but the expectation is that things are likely to double in the 2-3 year timeframe (sites & data volume). Sounds fabulous! I hope the people knocking multivalue storage retrieval |
Peter McMurray
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.