Approximate String Searches

For anybody that implemented fuzzy searches in SQL Server or any other
database for that matter. I have implemented edit distance algorithm,
specifically q-grams, in SQL Server and wanted to get some opinions
from anyone who used a similar algorithm in a DBMS.

We are looking to implement fuzzy address search and are employing
number of different techniques including synonyms for strings like
"drive", "dr", "drv", etc... Number expansion, so 6 ave = Six Ave =
6th Ave = 6... Now, we also have an implementation of edit distance
algorithm, which would cover misspellings and similar sounding words.
One of the challenges is putting various techniques together. Synonyms
and number expansion techniques go well together but no edit distance.

Also, I am trying to figure out how to leverage edit distance
implementation for 'contains' searches. So if a string like
'Mississippi Municipal Building' would be returned if the Search is for
'Misisipi Municipal'.

Any thoughts?Have you checked out the text extraction and fuzzy grouping stuff in SQL
Server Integration Services in SQL Server 2005, I've not used it on
addresses yet, but keyword searches and posts from these forums - quite
interesting results too!

Give it a wurl, its not got the options but it does work.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

<boblotz2001@.yahoo.com> wrote in message
news:1130967638.684858.10080@.g49g2000cwa.googlegro ups.com...
> For anybody that implemented fuzzy searches in SQL Server or any other
> database for that matter. I have implemented edit distance algorithm,
> specifically q-grams, in SQL Server and wanted to get some opinions
> from anyone who used a similar algorithm in a DBMS.
> We are looking to implement fuzzy address search and are employing
> number of different techniques including synonyms for strings like
> "drive", "dr", "drv", etc... Number expansion, so 6 ave = Six Ave =
> 6th Ave = 6... Now, we also have an implementation of edit distance
> algorithm, which would cover misspellings and similar sounding words.
> One of the challenges is putting various techniques together. Synonyms
> and number expansion techniques go well together but no edit distance.
>
> Also, I am trying to figure out how to leverage edit distance
> implementation for 'contains' searches. So if a string like
> 'Mississippi Municipal Building' would be returned if the Search is for
> 'Misisipi Municipal'.
> Any thoughts?

Approximate row count?

Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?EXEC sp_spaceused 'table_name'
SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Those will be more accurate, of course, if you first issue DBCC
UPDATEUSAGE(0) ...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Try:
select
rowcnt
from
dbo.sysindexes
where
id = object_id ('MyTable')
and
indid in (0, 1)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?|||Hi,
DBCC UPDATEUSAGE command will lock the table.
Use the below command to get the approximate table count:-
sp_spaceused <table_name>
Inacse if you have more descrepency then use the command UPDATEUSAGE which
scans the data pages in the table and makes any
necessary corrections to the sysindexes table regarding the storage space
used by each table
sp_spaceused <table_name>,@.updateusage = 'TRUE'
The above command will locks, so execute when the table access is less,
Thanks
Hari
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Thanks for the quick response. Is there any way to do something similar, but
find out how many rows fit a particular value for an indexed (or nonindexed)
column?
"Aaron Bertrand [SQL Server MVP]" wrote:

> EXEC sp_spaceused 'table_name'
> SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
>
>|||> Thanks for the quick response. Is there any way to do something similar,
> but
> find out how many rows fit a particular value for an indexed (or
> nonindexed)
> column?
No, values are stored in the table, so you need to query the table.
What is the actual problem? Does SELECT COUNT(*) take too long? How many
rows are there, is there a clustered index, and what kind of hardware is it?
I am currently looking at a table on a system here, with 70,000,000 rows,
and SELECT COUNT(*) comes back in a few seconds. Granted, I use the NOLOCK
hint for ad hoc stuff like this, and it is on enterprise-class hardware
(quad zeon, 16gb ram, emc clariion). But design is definitely going to have
a big impact here.
A|||> The above command will locks, so execute when the table access is less,
Yes Hari, I didn't mean to imply that anyone should run DBCC UPDATEUSAGE
before every query.sql

Approx date of next release

Does anyone have an approximate date of the next release of Reporting
Services?
Thanks in advance.
MarkReporting Services will be shipping along with Yukon. This is sometime in
the second half of next year most likely.
--
| From: "Mark" <mfield@.idonotlikespam.cce.umn.edu>
| Subject: Approx date of next release
| Date: Mon, 25 Oct 2004 08:55:27 -0500
| Lines: 8
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.181
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181
| Message-ID: <e#K3FppuEHA.2016@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: x15-238.cce.umn.edu 134.84.15.238
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:33045
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Does anyone have an approximate date of the next release of Reporting
| Services?
|
| Thanks in advance.
|
| Mark
|
|
|