From: Felix Windt <fwmailinglists@...>
Date: 2007-11-03T04:14:24+09:00
Subject: Re: Database speed issues

------=_NextPart_000_0CAA_01C81D48.6D2A5200
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

> -----Original Message-----
> From: JeremyWoertink@gmail.com [mailto:JeremyWoertink@gmail.com] 
> Sent: Friday, November 02, 2007 10:45 AM
> To: ruby-talk ML
> Subject: Database speed issues
> 
> So, I have a bit of a design problem. I have an application that work,
> but not as well as I would like it to. My problem is that I had to
> write an application that checks millions of records against hundreds
> of millions of records to see if there are any duplicates. The only
> way I could think to do this is pretty much select from the different
> tables where the specific column matches my search string. This is
> really slow! Obviously if it returns something, then  there is a dup,
> if not, then YAY!
> 
> I know there has to be a better way then just doing a SQL select
> statement. Any ideas?
> 
> 
> Thanks,
> 
> 
> ~Jeremy

You could let the database do the work in the following way, though it's
relatively dirty:

First, duplicate the database you're comparing to. Then change the indexes
on the tables so that you can force uniqueness on the records based on the
data you're comparing. Then simply insert the records you're checking. If
the insert did not work, it's a duplicate.

HTH,

Felix

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

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIII3jCCAmYw
ggHPoAMCAQICEFJ3uelO4AJUNG7Np2ieTWkwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA3MDMxMTEzMzk1OVoXDTA4MDMxMDEzMzk1
OVowSjEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEnMCUGCSqGSIb3DQEJARYYZndt
YWlsaW5nbGlzdHNAZ21haWwuY29tMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDoHQNMSMIi
1fb3BhJdz7Byt8PSeaa/Yx0wJ1BzLj7dJZK+8djBP06HvolZ9IYibnGerNg+LnxXT8tyGUZ0vw0Q
f3qSYtuGcYfdm44qVOAtV+fex14uBSmSj8i/QOL+710oSQdPGAJE1L+8N1bLvJAxznsF6tpD5GUZ
iPRY1HcloQIDAQABozUwMzAjBgNVHREEHDAagRhmd21haWxpbmdsaXN0c0BnbWFpbC5jb20wDAYD
VR0TAQH/BAIwADANBgkqhkiG9w0BAQUFAAOBgQCtXsHSAFBpcohm7/t5Vlak0eySI5c5cghVELhu
LyoNMdx+qCyKE4HJoWrr2+id/+YVPfiOaBsxsir3Aeg3DPuCODoafFYErm3OzJV13lRlwKWfrz01
vczScC2VvABk0ZvlQEHA54IJqiV8QoXntlQWaa2NYO3ggalUyaOeNmsk1TCCAy0wggKWoAMCAQIC
AQAwDQYJKoZIhvcNAQEEBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUx
EjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsT
H0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25h
bCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNv
bTAeFw05NjAxMDEwMDAwMDBaFw0yMDEyMzEyMzU5NTlaMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UE
CBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25z
dWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQD
ExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZy
ZWVtYWlsQHRoYXd0ZS5jb20wgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBANRp19SwlGRbcelH
2AxRtupykbCEXn0tDY97Et+FJXUodDpCLGMnn5V7S+9+GYcdhuqj3bnOlmQawhRuRKx85o/oTQ9x
H0A4pgCjh3j2+ZSGXq3qwF5269kUo11uenwMpUtVfwYZKX+emibVars4JAhqmMex2qOYkf152+Va
xBy5AgMBAAGjEzARMA8GA1UdEwEB/wQFMAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAx+ySfk749Zal
Z2IqpPBNEWDQb41gWGGsJrtSNVwIzzD7qEqWih9iQiOMFw/0umScF6xHKd+dmF7SbGBxXKKs3Hnj
524ARx+1DSjoAp3kmv0T9KbZfLH43F8jJgmRgHPQFBveQ6mDJfLmnC8Vyv6mq4oHdYsM3VGEa+T4
0c53ooEwggM/MIICqKADAgECAgENMA0GCSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMG
A1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBD
b25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYD
VQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFs
LWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQsw
CQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UE
AxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQAD
gY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow
1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenpruf
ZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBD
BgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVl
bWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVM
YWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0wh
uPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBP
ZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC3CEZNd4ksdMdRv9dX2VPMYIC+DCCAvQCAQEwdjBi
MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG
A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEFJ3uelO4AJUNG7Np2ie
TWkwCQYFKw4DAhoFAKCCAdgwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUx
DxcNMDcxMTAyMTkwMzQ4WjAjBgkqhkiG9w0BCQQxFgQUS7Eq0oOue6JEIxmKpWZnucOaju4wZwYJ
KoZIhvcNAQkPMVowWDAKBggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAw
BwYFKw4DAgcwDQYIKoZIhvcNAwICASgwBwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQ
BDF4MHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0
ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBSd7npTuAC
VDRuzadonk1pMIGHBgsqhkiG9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRo
YXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVl
bWFpbCBJc3N1aW5nIENBAhBSd7npTuACVDRuzadonk1pMA0GCSqGSIb3DQEBAQUABIGAH7xl5K/7
xYo06MBfxmZ/vmBzskctuUTng5JxpNosfMgfEX/glvTYTW0zz4kqw2YqaLXtAT1eBfhxfE1opEEg
aRoeCMva13Syg2kYuu6M6TLYw0E/fnG3SF9RdgxA3++p7xvKJPNobsDcduWGaebpXVuRoOjxj8/A
puxe/atSRuIAAAAAAAA=

------=_NextPart_000_0CAA_01C81D48.6D2A5200--