Re: How to achieve scalability

On Jul 2, 1:02=A0am, Tom van Stiphout <no.spam.tom7...@[EMAIL PROTECTED]
> wrote:
> On Wed, 2 Jul 2008 00:45:11 +0000 (UTC), "Carlos Nunes-Ueno"
>
> <sulla...@[EMAIL PROTECTED]
> wrote:
>
> Was that a real example? It seems difficult to come up with consistent
> rules that convert "The Yummy and Tasty Waffle Cor****ation" into
> "TASTYWAFFLE". Something like "Take the 4th and 5th word, and omit the
> spaces"? Can you tell us what kinds of rules you're applying for the
> conversion?
>
> I have had good success with the Ratcliff/Obershelp algorithm that
> returns a similarity (a number between 0 and 1) between two strings. I
> checked and for your two company names the similarity is 0.75. Using
> some cutoff value you can narrow down the most similar companies and
> bunch them up that way.
> We recently implemented this algorithm as a .Net assembly in SQL
> Server 2005, and it is very fast. 10,000 comparisons in way less than
> 1 second.
>
> I'm not at all convinced RegEx is the ticket here.
>
> -Tom.

I'm not so familiar with running .Net in SQL Server. Does ".Net
assembly in SQL Server 2005" imply the function is converted to
assembly language? Or is it compiled to machine code? P-Code? Or just
interpreted script? Will any of these impact much on string functions
anyway?

Will the Ratcliff/Obershelp algorithm deal with such directions as:
If you find "Yummy" and "Tasty" in the string return "YummyTasty"?

An air code RegExp that does that with both strings provided by the
OP, runs a million iterations in 7 seconds on my computer. Of course,
that's just P-Code.

If I were doing this I'd probably explore getting big strings with
ADO's GetString, or maybe XML record set saves, and applying the rules
to those big strings that included many records.

Sample Code (done over coffee and with the connivance of allergy
induced sleeplessness - we have met the pollen and it has won!)

Private Declare Function GetTickCount& Lib "kernel32" ()

Private Sub Whatever()
    Dim Iterator&
    Dim RegExp As Object
    Dim ThousandthsofaSecond&
    Dim WhatComesOut$
    Dim WhatGoesIn$(0 To 1)
    WhatGoesIn(0) =3D "The Yummy and Tasty Waffle Cor****ation"
    WhatGoesIn(1) =3D "Yummy & Tasty Waffle, Incor****ated"
    ThousandthsofaSecond =3D GetTickCount()
    Set RegExp =3D CreateObject("VBScript.RegExp")
    With RegExp
        .Global =3D True
        .IgnoreCase =3D True
        .pattern =3D ".*yummy.*tasty.*"
        For Iterator =3D 0 To 999999
            WhatComesOut =3D .Replace(WhatGoesIn(Iterator Mod 2),
"YUMMYTASTY")
        Next Iterator
    End With
    Debug.Print _
    "(" & WhatGoesIn(0) _
    & "||" _
    & WhatGoesIn(1) & ")" _
    & "->" _
    & WhatComesOut & vbNewLine _
    & Iterator _
    & " times in " _
    & (GetTickCount() - ThousandthsofaSecond) / 1000 _
    & " seconds"
End Sub

(The Yummy and Tasty Waffle Cor****ation||Yummy & Tasty Waffle,
Incor****ated)->YUMMYTASTY
1000000 times in 7.176 seconds