Full text or not full text?

I have problem with the speed of queries.
I'm searching for UK car number (registration) plates consisting of 2
letters, followed by 2 numbers, followed by 3 letters.
ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
Most of these number plates are in their own separate row, but some of
them are in a comma delimited string,
Now suppose I want to find 'AB12ABC' in a string that consists of
'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
Now at the moment, I've got a database table consisting of nearly
380,000 rows.
I've got indexs placed on the 'plate' column, and I've even tried
setting up full-text indexing, none of which have increased the speed
of the query:
SET NOCOUNT ON
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
BEGIN
SELECT 'Yes'
END
ELSE
BEGIN
SELECT 'N/A'
END
which, at [resent takes around 30 seconds to complete, so running a
report for say 300 number plates to find if they have been ordered or
not, takes around 30+ minutes to complete.
If there another way of doing things that any one can suggest? Apart
from splitting the strings up?That is the price of not normalizing. If the data was in its own
table, as all repeating groups should be, performance would not be a
problem at all. It would be pretty much instantaneous.
If doing them one at a time takes 30 seconds each, don't do them one
at a time. Load all 300 into a table, and run them all in one pass.
Performance won't be good, but it should not be as bad.
--This version just lists the ones that match
SELECT M.RegNum
FROM MatchList as M
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
--This version lists them all, with results of the match for each
SELECT M.RegNum,
CASE WHEN V.plates IS NOT NULL
THEN 'Yes'
ELSE 'N/A'
END as Found
FROM MatchList as M
LEFT OUTER
JOIN vclivePlates as V
ON V.plates LIKE '%' + M.RegNum + '%'
Roy Harvey
Beacon Falls, CT
On 6 Jul 2006 05:03:40 -0700, "pinhead" <dlynes2005@.gmail.com> wrote:

>I have problem with the speed of queries.
>I'm searching for UK car number (registration) plates consisting of 2
>letters, followed by 2 numbers, followed by 3 letters.
>ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
>Most of these number plates are in their own separate row, but some of
>them are in a comma delimited string,
>Now suppose I want to find 'AB12ABC' in a string that consists of
>'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
>Now at the moment, I've got a database table consisting of nearly
>380,000 rows.
>I've got indexs placed on the 'plate' column, and I've even tried
>setting up full-text indexing, none of which have increased the speed
>of the query:
>SET NOCOUNT ON
>IF EXISTS (SELECT id
>FROM vclivePlates
>WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
>BEGIN
>SELECT 'Yes'
>END
>ELSE
>BEGIN
>SELECT 'N/A'
>END
>which, at [resent takes around 30 seconds to complete, so running a
>report for say 300 number plates to find if they have been ordered or
>not, takes around 30+ minutes to complete.
>If there another way of doing things that any one can suggest? Apart
>from splitting the strings up?|||You run into performance problem because you store few plates in one
row. If you can modify the database and store each plate in its own
row, then you won't have to use wildcard in the beginning of your
search criteria and the server will be able to use index seek instead
of table scan. If you must use one row to store few plates, then you
can try something else. In your post you said that most plates are in
there own rows and only some rows store more then one plate. If only
small percentage of the rows store few plates, then maybe this will
help - create a computed column on the table that counts the number of
comas in the column that holds the registration plate. Then create an
index on that column. Modify you query so it will look like this:
IF EXISTS (SELECT id
FROM vclivePlates
WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
1)
OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
This might cause the server to use the indexes, but it depends on the
number of rows that contain more then one plate.
Adi
pinhead wrote:
> I have problem with the speed of queries.
> I'm searching for UK car number (registration) plates consisting of 2
> letters, followed by 2 numbers, followed by 3 letters.
> ie; AB12CDE or maybe UV98XYZ - examples ONLY to show type of data.
> Most of these number plates are in their own separate row, but some of
> them are in a comma delimited string,
> Now suppose I want to find 'AB12ABC' in a string that consists of
> 'AB00AAA, AA01AAA, AA02AAA, AB12ABC,TR12SDF' - what is the best way?
> Now at the moment, I've got a database table consisting of nearly
> 380,000 rows.
> I've got indexs placed on the 'plate' column, and I've even tried
> setting up full-text indexing, none of which have increased the speed
> of the query:
> SET NOCOUNT ON
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%'))
> BEGIN
> SELECT 'Yes'
> END
> ELSE
> BEGIN
> SELECT 'N/A'
> END
> which, at [resent takes around 30 seconds to complete, so running a
> report for say 300 number plates to find if they have been ordered or
> not, takes around 30+ minutes to complete.
> If there another way of doing things that any one can suggest? Apart
> from splitting the strings up?|||Having a major braindead day today, so forgive me for this...
how would I go about counting the number of commas in the data row?
Adi wrote:[vbcol=seagreen]
> You run into performance problem because you store few plates in one
> row. If you can modify the database and store each plate in its own
> row, then you won't have to use wildcard in the beginning of your
> search criteria and the server will be able to use index seek instead
> of table scan. If you must use one row to store few plates, then you
> can try something else. In your post you said that most plates are in
> there own rows and only some rows store more then one plate. If only
> small percentage of the rows store few plates, then maybe this will
> help - create a computed column on the table that counts the number of
> comas in the column that holds the registration plate. Then create an
> index on that column. Modify you query so it will look like this:
> IF EXISTS (SELECT id
> FROM vclivePlates
> WHERE (plates LIKE '%' + RTRIM(LTRIM(@.RegNum)) + '%') AND NewCol >
> 1)
> OR (Plates LIKE RTRIM(LTRIM(@.RegNum)) AND NewCol = 0)
>
> This might cause the server to use the indexes, but it depends on the
> number of rows that contain more then one plate.
> Adi
> pinhead wrote:|||Sorry -
DATALENGTH(plates) - DATALENGTH(REPLACE(plates, ',', ''))
works well for me
pinhead wrote:[vbcol=seagreen]
> Having a major braindead day today, so forgive me for this...
> how would I go about counting the number of commas in the data row?
>
> Adi wrote: