Optimizing table with more than 54 million records

I have a table that has more than 54 million records and I'm searching
records using the LIKE statement. I looking for ways to
optimize/partition/etc. this table.
This is the table structure:
TABLE "SEARCHCACHE"
Fields:
- searchType int
- searchField int
- value varchar(500)
- externalKey int
For example, a simple search would be:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
value LIKE 'name2%')
*******
This works just fine, it retrieves records in 2-3 seconds. The problem
is when I combine more predicates. For example:
*******
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
*******
this may take up to 30-40 seconds!
Any suggestion?
Thanks in advanced.On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
maybe you could consider full-text search on the table? this would
require afair rewriting queries though.|||The original solution used FullText but it didn't work for my scenario.
That's why I implemented this SearchCache table.
Thanks.
Piotr Rodak wrote:
> On Jul 30, 1:23 pm, Gaspar <gas...@.no-reply.com> wrote:
>> I have a table that has more than 54 million records and I'm searching
>> records using the LIKE statement. I looking for ways to
>> optimize/partition/etc. this table.
>> This is the table structure:
>> TABLE "SEARCHCACHE"
>> Fields:
>> - searchType int
>> - searchField int
>> - value varchar(500)
>> - externalKey int
>> For example, a simple search would be:
>> *******
>> SELECT TOP 100 *
>> FROM SearchCache
>> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
>> value LIKE 'name2%')
>> *******
>> This works just fine, it retrieves records in 2-3 seconds. The problem
>> is when I combine more predicates. For example:
>> *******
>> SELECT TOP 100 *
>> FROM SearchCache
>> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
>> OR value LIKE 'name2%'))
>> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
>> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
>> *******
>> this may take up to 30-40 seconds!
>> Any suggestion?
>> Thanks in advanced.
> maybe you could consider full-text search on the table? this would
> require afair rewriting queries though.
>|||Gaspar
1) Don't use TOP 100 command especially without ORDER BY clause
2) Please provide some details from an execution plan
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:e1xYASq0HHA.4824@.TK2MSFTNGP02.phx.gbl...
>I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.|||Gaspar,
Try unioning the result from independent statements.
select top 100 *
from
(
SELECT TOP 100 *
FROM SearchCache
WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
OR value LIKE 'name2%'))
union
SELECT TOP 100 *
FROM SearchCache
where ((searchType = 2) AND (searchField = 3) AND (value LIKE
'anothervalue1%' OR value LIKE 'anothervalue2%'))
) as t
AMB
"Gaspar" wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>|||You may want to try running the profiler and check suggestions under the
tuning wizard for the tables - it could be a lack of a good index.
--
Regards,
Jamie
"Gaspar" wrote:
> I have a table that has more than 54 million records and I'm searching
> records using the LIKE statement. I looking for ways to
> optimize/partition/etc. this table.
> This is the table structure:
> TABLE "SEARCHCACHE"
> Fields:
> - searchType int
> - searchField int
> - value varchar(500)
> - externalKey int
> For example, a simple search would be:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE (searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%' OR
> value LIKE 'name2%')
> *******
> This works just fine, it retrieves records in 2-3 seconds. The problem
> is when I combine more predicates. For example:
> *******
> SELECT TOP 100 *
> FROM SearchCache
> WHERE ((searchType = 2) AND (searchField = 2) AND (value LIKE 'name1%'
> OR value LIKE 'name2%'))
> OR ((searchType = 2) AND (searchField = 3) AND (value LIKE
> 'anothervalue1%' OR value LIKE 'anothervalue2%'))
> *******
> this may take up to 30-40 seconds!
> Any suggestion?
> Thanks in advanced.
>sql