Re: exploiting the object index

[prev] [thread] [next] [Date index for 2005/05/31]

This is a multi-part message in MIME format.

------=_NextPart_000_027A_01C56608.75571520
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

@Tony: the table has only the primary key column and 2 more (name &
description) so all are already in ESSENTIAL. Thanks for the hint!

@Cees: The table only contains 6 rows and the primary-key lookup is for
insert/update/delete of another table.

-Alex


-----Urspr=FCngliche Nachricht-----
Von: Cees Hek [mailto:ceeshek@xxxxx.xxx]=20
Gesendet: Dienstag, 31. Mai 2005 17:36
An: Hartmaier Alexander
Cc: cdbi-talk@xxxxxx.xxxxx.xxx
Betreff: Re: exploiting the object index

On 5/31/05, Hartmaier Alexander <Alexander.Hartmaier@xxxxxxxxx.xx> =
wrote:
> I want to retrieve all objects of a class to avoid lots of
> single-row-returning sql queries against the db.=20
>=20
> e.g.:=20
>=20
> my @cds =3D Music::CD->retrieve_all;=20
>=20
> Then I want to lookup the object by a non-primary-key field which is =
also
> unique.=20
>=20
> e.g.:=20
>=20
> my $obj =3D Music::CD->search($name);=20

Are you sure you want to do that?  Are you certain it will be faster
and worth the effort?  You seem to be trying to ignore the whole
purpose of having a database in the first place by using perl to do
your searching.  This is exactly what databases are good at.

Now if you really do want to do it, there are a few ways you can go
about it, the easiest would be to just use grep:

my @cds =3D Music::CD->retrieve_all;=20

my @subset =3D grep { $_->name eq $name } @cds;

Make sure that 'name' is in marked as an 'Essential' column in your
Class::DBI module, otherwise it will still do multiple calls to the
DB.

If you want to do this automatically behind the scenes in Class::DBI,
then you have a bit of work cut out for you :)

Cheers,

Cees

------=_NextPart_000_027A_01C56608.75571520
Content-Type: application/x-pkcs7-signature;
	name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
	filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIISkjCCApow
ggIDoAMCAQICASQwDQYJKoZIhvcNAQEEBQAwcTELMAkGA1UEBhMCREUxHDAaBgNVBAoTE0RldXRz
Y2hlIFRlbGVrb20gQUcxHzAdBgNVBAsTFlQtVGVsZVNlYyBUcnVzdCBDZW50ZXIxIzAhBgNVBAMT
GkRldXRzY2hlIFRlbGVrb20gUm9vdCBDQSAxMB4XDTk5MDcwOTExMzQwMFoXDTE5MDcwOTIzNTkw
MFowcTELMAkGA1UEBhMCREUxHDAaBgNVBAoTE0RldXRzY2hlIFRlbGVrb20gQUcxHzAdBgNVBAsT
FlQtVGVsZVNlYyBUcnVzdCBDZW50ZXIxIzAhBgNVBAMTGkRldXRzY2hlIFRlbGVrb20gUm9vdCBD
QSAxMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDQ3ZsMoBdERA+vIUBzZ1bwPmloEbrZN/KB
rsMkrGmhzfymGFVW/4ufMsHb53gsOdtggUGl79PNgI0YPOJSDAuf92Se5aDwuGFi9L/go9pYK/0V
BGu9Op58nfI92OSVw+xOwvFlqwxL7EeCW+LhUHXY9mG0GFztM6BLHoP7T4S8eQIDAQABo0IwQDAd
BgNVHQ4EFgQUFDHif5zKEpX78XAg200oE3FCYcYwDwYDVR0TBAgwBgEB/wIBBTAOBgNVHQ8BAf8E
BAMCAQYwDQYJKoZIhvcNAQEEBQADgYEAnR0Q+pPIHWQgRV6cbwVto1J/p6iHZbNnmjaGeHIWFf/X
RXMoAYaInZHq3tYpCww+o5l0QAzM7BDgZPxwtro5EifyXgBQtQvWSt+pb/SzCSiA/NIexOxwRoVM
3kWwAZU4rBafRkruLcu9y2WxOuWmSwRLDjP/HHvIhIRH4VpkRkowggTdMIIERqADAgECAgIAnTAN
BgkqhkiG9w0BAQUFADBxMQswCQYDVQQGEwJERTEcMBoGA1UEChMTRGV1dHNjaGUgVGVsZWtvbSBB
RzEfMB0GA1UECxMWVC1UZWxlU2VjIFRydXN0IENlbnRlcjEjMCEGA1UEAxMaRGV1dHNjaGUgVGVs
ZWtvbSBSb290IENBIDEwHhcNMDQwNjI0MDUzODAwWhcNMTAwNjI0MjM1OTAwWjB6MQswCQYDVQQG
EwJERTElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDElMCMGA1UECxMcVC1T
eXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEdMBsGA1UEAxMUVC1TeXN0ZW1zIENvbXBhbnkgQ0Ew
ggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQC+ooSiyo46KcYIAAJzfHHnXVHzuFUtMTiG
n5+TIKzga38S/sRAj6vPSn1AcoUEW1LZ+CFvdNJgmUouVyZ1siE1DJNH1N1URl5CunNTNMvAFDxj
Cc1GQ6QXpxi7W4cTkF3Cj95eUb8Ux4efTVpeaoV2/Zazw3hVLGRnkcBTVdkED6fjn/UDmDc8i9Mc
Sg4Yr+n4DkZ4mCz3jVZZeIScnI2YzDeNKDRajnygJlve5hY23mqaBLscDzFFntGe6yswp7D6pSDa
108/8a8HySUKGZ1weC3BDgaMLjPz4pcXjtkvleyxWH5BIQo1tlbtXwigqJN+zhxdbsi8b5IY6ko5
sp+jAgMBAAGjggH1MIIB8TB1BgNVHSAEbjBsMGoGCSsGAQQBvUcNEDBdMFsGCCsGAQUFBwIBFk9o
dHRwOi8va3J5cFRTSS50ZWxlc2VjLmRlL2NnaS1iaW4vY2FzZXJ2aWNlL2tyeXBUU0kvRG93bmxv
YWRQREYva3J5cFRTSS1DUFMucGRmMIIBEgYDVR0fBIIBCTCCAQUwXqBcoFqGWGh0dHA6Ly9rcnlw
VFNJLnRlbGVzZWMuZGUvY2dpLWJpbi9jYXNlcnZpY2Uva3J5cFRTSS9hZl9Eb3dubG9hZEFSTC5j
cmw/LWNybF9mb3JtYXQ9WF81MDkwgaKggZ+ggZyGgZlsZGFwOi8vbGRhcC1rcnlwVFNJLnRlbGVz
ZWMuZGUvY249RGV1dHNjaGUlMjBUZWxla29tJTIwUm9vdCUyMENBJTIwMSxvdT1ULVRlbGVTZWMl
MjBUcnVzdCUyMENlbnRlcixvPURldXRzY2hlJTIwVGVsZWtvbSUyMEFHLGM9REU/QXV0aG9yaXR5
UmV2b2NhdGlvbkxpc3QwHQYDVR0OBBYEFGKQKoiCJcPJVuPjvloUpjQ+jwYvMB8GA1UdIwQYMBaA
FBQx4n+cyhKV+/FwINtNKBNxQmHGMA4GA1UdDwEB/wQEAwIBhjASBgNVHRMBAf8ECDAGAQH/AgED
MA0GCSqGSIb3DQEBBQUAA4GBAK2TIG0uoRyvzq/3CUalvZOFSnUz6oDrX5fa6r1Fdh8ODa0zOCzr
qbLQRLZVErwH9qxuC6qxby57Ek1qpwI8TuuhFnMA9ISrkEUJjgxAIe6at2NdQ6bowZG9McwGmcsU
yiCwyipt70V3VHFW2cVHmMsEitGx5PsgfhUiGi+Ufst1MIIFhTCCBG2gAwIBAgICRxYwDQYJKoZI
hvcNAQEFBQAwejELMAkGA1UEBhMCREUxJTAjBgNVBAoTHFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFs
IEdtYkgxJTAjBgNVBAsTHFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFsIEdtYkgxHTAbBgNVBAMTFFQt
U3lzdGVtcyBDb21wYW55IENBMB4XDTA1MDQxMTA5MjgyNFoXDTA4MDQxMTIzNTkwMFowgdgxCzAJ
BgNVBAYTAkFUMSUwIwYDVQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMRAwDgYDVQQL
EwdhdXN0cmlhMSEwHwYDVQQLExhDRUUtQVQtVklFLUVNLTAxLmF1c3RyaWExGjAYBgNVBAsTEVQt
U3lzdGVtcyBBdXN0cmlhMR8wHQYDVQQDExZIYXJ0bWFpZXIgQWxleGFuZGVyIDAxMTAwLgYJKoZI
hvcNAQkBFiFBbGV4YW5kZXIuSGFydG1haWVyQHQtc3lzdGVtcy5jb20wgZ8wDQYJKoZIhvcNAQEB
BQADgY0AMIGJAoGBANo+1hFHTw2SivsA2RxpYFAwSULIFXhagYoguyrIefoxYL/SYfIUoKFejDMg
bX/J4ZSkfdta2rH4TapDDpnFJhkRTsJ4yi8i03PtfsqJmtzqFedRsQta0t0YWBMskNhlXveT1Wqh
jjDJsV8c+dxf1KZltYnPMvpJU46Gu0kKTa1tAgMBAAGjggI4MIICNDAfBgNVHSMEGDAWgBRikCqI
giXDyVbj475aFKY0Po8GLzAOBgNVHQ8BAf8EBAMCBSAwHQYDVR0OBBYEFDWHeGOuGCPJFrZOxI+l
io66rv6wMHUGA1UdIARuMGwwagYJKwYBBAG9Rw0QMF0wWwYIKwYBBQUHAgEWT2h0dHA6Ly9rcnlw
dHNpLnRlbGVzZWMuZGUvY2dpLWJpbi9jYXNlcnZpY2Uva3J5cFRTSS9Eb3dubG9hZFBERi9rcnlw
VFNJLUNQUy5wZGYwTgYDVR0RBEcwRYEgQWxleGFuZGVyLkhhcnRtYWllckB0LXN5c3RlbXMuYXSB
IUFsZXhhbmRlci5IYXJ0bWFpZXJAdC1zeXN0ZW1zLmNvbTCCARkGA1UdHwSCARAwggEMMF6gXKBa
hlhodHRwOi8va3J5cHRzaS50ZWxlc2VjLmRlL2NnaS1iaW4vY2FzZXJ2aWNlL2tyeXBUU0kvYWZf
RG93bmxvYWRDUkwuY3JsPy1jcmxfZm9ybWF0PVhfNTA5MIGpoIGmoIGjhoGgbGRhcDovL2xkYXAt
a3J5cFRTSS50ZWxlc2VjLmRlL2NuPVQtU3lzdGVtcyUyMENvbXBhbnklMjBDQSxvdT1ULVN5c3Rl
bXMlMjBJbnRlcm5hdGlvbmFsJTIwR21iSCxvPVQtU3lzdGVtcyUyMEludGVybmF0aW9uYWwlMjBH
bWJILGM9REU/Q2VydGlmaWNhdGVSZXZvY2F0aW9uTGlzdDANBgkqhkiG9w0BAQUFAAOCAQEAFZCu
YmsdsJQWyEK8286QMCcYytKL6eKzZnm7isDcYmWKr3F1MqV/CsyfQQ0pGC0ACdjw/uWGgtrLsN9X
hYDiO4maoicRgMO7cPR4b5YGHfwC7VYxn0DLCBqx34oBRB90LCGK3Es4cZ5Wo+c5mLAcvJ+IzFnB
BgwDWU3+tXcZe5qzLUb2KSNT4WM4ysaVAwBYtGHgU+5wbPrOD0yYFoyi3rgEU2K63SIymvewOD14
wGX7KaWnJLBrSlbkPYoQxDfKPOxb3sTmVjK5eql8zDoiFc7hETorlQprijNVDmsYPYnFVECUXASQ
HnlQZ1IlT0iXQiZdNmeGUKkOQHl/BeqXxTCCBYYwggRuoAMCAQICAkcVMA0GCSqGSIb3DQEBBQUA
MHoxCzAJBgNVBAYTAkRFMSUwIwYDVQQKExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMSUw
IwYDVQQLExxULVN5c3RlbXMgSW50ZXJuYXRpb25hbCBHbWJIMR0wGwYDVQQDExRULVN5c3RlbXMg
Q29tcGFueSBDQTAeFw0wNTA0MTEwOTI4MTJaFw0wODA0MTEyMzU5MDBaMIHYMQswCQYDVQQGEwJB
VDElMCMGA1UEChMcVC1TeXN0ZW1zIEludGVybmF0aW9uYWwgR21iSDEQMA4GA1UECxMHYXVzdHJp
YTEhMB8GA1UECxMYQ0VFLUFULVZJRS1FTS0wMS5hdXN0cmlhMRowGAYDVQQLExFULVN5c3RlbXMg
QXVzdHJpYTEfMB0GA1UEAxMWSGFydG1haWVyIEFsZXhhbmRlciAwMTEwMC4GCSqGSIb3DQEJARYh
QWxleGFuZGVyLkhhcnRtYWllckB0LXN5c3RlbXMuY29tMIGgMA0GCSqGSIb3DQEBAQUAA4GOADCB
igKBgQCBzuPPhyIz16V/u41Cg8eOEyzCnjTQ2/+XUX76CzSLryHuJHGhjtea14ycDshjC41ZCkLH
avKr909LrQgaPS1BeF3tja9XwJ7u+I8bIFiS+J0AKP2oyp76SpuBzS0ubrOckZgzSiTQHscRnkvb
2SYfyI2d3gD85xo8DQqGSigrVQIEQAAAgaOCAjgwggI0MB8GA1UdIwQYMBaAFGKQKoiCJcPJVuPj
vloUpjQ+jwYvMA4GA1UdDwEB/wQEAwIHgDAdBgNVHQ4EFgQUsiPXp43D6udRc2hzEc18bO7I4pYw
dQYDVR0gBG4wbDBqBgkrBgEEAb1HDRAwXTBbBggrBgEFBQcCARZPaHR0cDovL2tyeXB0c2kudGVs
ZXNlYy5kZS9jZ2ktYmluL2Nhc2VydmljZS9rcnlwVFNJL0Rvd25sb2FkUERGL2tyeXBUU0ktQ1BT
LnBkZjBOBgNVHREERzBFgSBBbGV4YW5kZXIuSGFydG1haWVyQHQtc3lzdGVtcy5hdIEhQWxleGFu
ZGVyLkhhcnRtYWllckB0LXN5c3RlbXMuY29tMIIBGQYDVR0fBIIBEDCCAQwwXqBcoFqGWGh0dHA6
Ly9rcnlwdHNpLnRlbGVzZWMuZGUvY2dpLWJpbi9jYXNlcnZpY2Uva3J5cFRTSS9hZl9Eb3dubG9h
ZENSTC5jcmw/LWNybF9mb3JtYXQ9WF81MDkwgamggaaggaOGgaBsZGFwOi8vbGRhcC1rcnlwVFNJ
LnRlbGVzZWMuZGUvY249VC1TeXN0ZW1zJTIwQ29tcGFueSUyMENBLG91PVQtU3lzdGVtcyUyMElu
dGVybmF0aW9uYWwlMjBHbWJILG89VC1TeXN0ZW1zJTIwSW50ZXJuYXRpb25hbCUyMEdtYkgsYz1E
RT9DZXJ0aWZpY2F0ZVJldm9jYXRpb25MaXN0MA0GCSqGSIb3DQEBBQUAA4IBAQAouXnAG7+LMP/3
urannW0DO4j1n6Hvqt8G+MkxisUkSr41aLK10uen0S/+iyLIhhgUC1mag5vVS/Gkzevsi9K3qcSf
wIIBWwdrJKJ+WifbcNuS2YexMKLuBtVe/l6ydHMGtcZ3SQykxAoWYEmhPSpNxgfMUYATDJOZs7bH
l7WNy/43ZgKbsl9BhCNVCro0dC9berFgc1gAZ2O42D3M7TQB3Hqx68iLoYpvmPgMWiQJ6H6C0h/3
fzamxuB+L1DfMSnnOmJvdftpJjZmnORigvfCuMLxkjPbDESLEJtmw3oROemRMFR1oAIYf9qrexiT
a6TVsQzMVnsuQgoZ+dqaFCLbMYIC2DCCAtQCAQEwgYAwejELMAkGA1UEBhMCREUxJTAjBgNVBAoT
HFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFsIEdtYkgxJTAjBgNVBAsTHFQtU3lzdGVtcyBJbnRlcm5h
dGlvbmFsIEdtYkgxHTAbBgNVBAMTFFQtU3lzdGVtcyBDb21wYW55IENBAgJHFTAJBgUrDgMCGgUA
oIIBrTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNTA1MzExNTQ0
NTZaMCMGCSqGSIb3DQEJBDEWBBSTm99S7DZlbUYLi9kxH/KGB1wqBzAkBgkqhkiG9w0BCQ8xFzAV
MAoGCCqGSIb3DQMHMAcGBSsOAwIaMIGRBgkrBgEEAYI3EAQxgYMwgYAwejELMAkGA1UEBhMCREUx
JTAjBgNVBAoTHFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFsIEdtYkgxJTAjBgNVBAsTHFQtU3lzdGVt
cyBJbnRlcm5hdGlvbmFsIEdtYkgxHTAbBgNVBAMTFFQtU3lzdGVtcyBDb21wYW55IENBAgJHFjCB
kwYLKoZIhvcNAQkQAgsxgYOggYAwejELMAkGA1UEBhMCREUxJTAjBgNVBAoTHFQtU3lzdGVtcyBJ
bnRlcm5hdGlvbmFsIEdtYkgxJTAjBgNVBAsTHFQtU3lzdGVtcyBJbnRlcm5hdGlvbmFsIEdtYkgx
HTAbBgNVBAMTFFQtU3lzdGVtcyBDb21wYW55IENBAgJHFjANBgkqhkiG9w0BAQEFAASBgABIlMw/
G41AKMXIMmjK0RZ2l33KoknnRZ79aGzGH0vDxGu0rC7+rjjqpueKmhAkg44qj2IkdSDqsnU8DBly
NrGl86oVS612DNTd8SRpjIfvoJskKMhzlN8wI5cH0STIZw4GIxyNgWdBIIxI+FQWbBzEbQKAyKn3
3seB7kgkmEGqAAAAAAAA

------=_NextPart_000_027A_01C56608.75571520--