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:
> 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.
>

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

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:
> 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.
>

Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

sql

Optimizing LIKE OR Selects

I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:

table Company, fields CompanyID, CompanyName

table Address, fields AddressID, AddressName

table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID

CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:

Select c.CompanyID, c.CompanyName, a.AddressName

FROM Company c

LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID

LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID

WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))

There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.

Is there any way to optimize this query, or is it a problem with the base table implementation?

Any advice would be appreciated.

? Hi Brian, Do you really need to use OUTER JOINs here? For an INNER JOIN, the optimizer can sometimes pick a more efficient execution plan. It's a long shot, but sometimes performance for a query with OR can be improved by re-writing it as a UNION of two queries: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION -- Or UNION ALL, see below SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' If your data is such that you can be sure there will never be an overlap in the results of the two UNION'ed queries, then change UNION to UNION ALL to gain some more performance. If that's not possible, then you could also try how this one runs: SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Change to INNER JOIN if possible LEFT JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID LEFT JOIN Address AS a ON a.AddressID = caa.AddressID WHERE c.CompanyName LIKE 'TEST%' UNION ALL SELECT c.CompanyID, c.CompanyName, a.AddressName FROM Company AS c -- Definitely no need for LEFT OUTER JOIN in this half of the query INNER JOIN CompanyAddressAssoc AS caa ON caa.CompanyID = c.CompanyID INNER JOIN Address AS a ON a.AddressID = caa.AddressID WHERE a.AddressName LIKE 'TEST%' AND c.CompanyName NOT LIKE 'TEST%' -- Assumes CompanyName is never NULL The queries above are untested. See www.aspfaq.com/5006 if you prefer a tested reply. -- Hugo Kornelis, SQL Server MVP <Brian S. Ward@.discussions.microsoft..com> schreef in bericht news:822eb526-281c-409e-80df-9a03e79d3f05@.discussions.microsoft.com... I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example: table Company, fields CompanyID, CompanyName table Address, fields AddressID, AddressName table CompanyAddressAssoc, fields AssocID, CompanyID, AddressID CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter: Select c.CompanyID, c.CompanyName, a.AddressName FROM Company c LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%)) There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address. Is there any way to optimize this query, or is it a problem with the base table implementation? Any advice would be appreciated.|||

Hi Hugo,

Thanks for replying to my question. I tried a couple of the things that you mentioned.

Changing the OUTER JOINS to INNER JOINS had no noticeable effect on performance. Additionally, the execution plan seemed to become more complicated.

I tried using a UNION ALL clause between 2 SQL statements setup specifically to select AddressName and CompanyName, but performance was destroyed trying that. I used the actual version of the SQL rather than the test SQL I submitted, which contains about 7 joins.

The only solution that I can think of at this time is to change the schema of the base tables, moving the AddressName and CompanyName into the associative table, therefore allowing one search field to be indexed. It would be a bit more cryptic, but would solve the problem of the LIKE OR issue ( since there would be only one LIKE statement for both checks ).

Any other ideas would be appreciated.

|||

It sounds like having a denormalized schema like you suggest may speed things up. There is nothing wrong in duplicating the addressname and company name fields in the one table, lots of companies have denormalized databases for performance purposes. I used to work on a database for one of the biggest Oil companies in the world, and that was largely denormalized and had no relationships set up (they were enforced by triggers and in the stored procedures).

An alternative which may work (although its a long shot) is to rewrite the OR as a not and such that

A OR B = NOT(NOT A AND NOT B)

One of my former colleagues used to assure me that was faster, but I have never tested it. It works as all computers are built from NAND gates, and thus any boolean statement can be rewitten as a series of NANDs

|||

Hi,

Have you tried to run it as to queries?

Without the OR statement.

Try that and see if it gets better.

If so, then insert the result into a temptable and make the final select from there.

It's hard to speed upp OR selects.

Regards

|||

I've tried that too, running 2 queries then trying to merge them after, but that becomes pretty convoluted trying to decide which records from the 2 sets makes the Top 100. I've decided to go with the denormalization plan for now, populating a 'Name' field in the associative table and using that to search. One index, no Or statement, runs really fast.

Thanks to everyone for your input.

|||

Can you post the statistics profile output (and the xml showplan, if possible)?

We can tell where things are wrong based on that.

Thanks,

Conor

optimizing a query to delete duplicates

I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:

+----+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 123 |
+--+--+

As you can see, the data is tabular. This is how it is stored in the table:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 234 |
+--+----+----+

What I need is to delete all records having the same "Row" when there exists
the same set of records with a different (smaller, to be precise) "Row".
Using the example above, what I need to get is:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
+--+----+----+

A slow way of doing this seem to be:

DELETE FROM X
WHERE Row IN
(SELECT DISTINCT Row FROM X x1
WHERE EXISTS
(SELECT * FROM X x2
WHERE x2.Row < x1.Row
AND NOT EXISTS
(SELECT * FROM X x3
WHERE x3.Row = x2.Row
AND x3.FieldName = x2.FieldName
AND x3.FieldValue <> x1.FieldValue)))

Can this be done faster, better, and cheaper?my knee-jerk reaction is:

Why is it important to optimize it? I think you should delete the
duplicates, then create a constraint that prevents them from recurring.

If, for some reason, you are unable to fix the application that creates
these duplicates, and creating a constraint causes errors in the application
that you can't tolerate, then I suppose an alternative would be to create a
trigger that deletes them upon entry. Having a composite index on the
columns that are being duplicated would enable such a trigger to run
quickly.

But looking at your query, I find it strangely complex.

Why not just:

DELETE FROM X
WHERE EXISTS (SELECT * FROM X x2
WHERE x2.Row < x.Row
AND X.FieldName = x2.FieldName
AND X.FieldValue = x2.FieldValue)

Am I missing something? Your NOT EXISTS has me a bit confused... I think it
might delete data in situations other than described.

Also, NOT EXISTS is generally slow.|||On 2004-07-15, Aaron W. West <tallpeak@.hotmail.NO.SPAM> wrote:
> Why is it important to optimize it? I think you should delete the
> duplicates, then create a constraint that prevents them from recurring.

Such constraint may not be created. This table is a temporary table, where
data from an input file is loaded. Duplicate sets of records must be
deleted because the data then goes into permanent tables. Those table have
constraints against duplicates.

> But looking at your query, I find it strangely complex.

Me too. I'm trying to improve it. Its complexity seems to hinder its
performance.

> Why not just:
> DELETE FROM X
> WHERE EXISTS (SELECT * FROM X x2
> WHERE x2.Row < x.Row
> AND X.FieldName = x2.FieldName
> AND X.FieldValue = x2.FieldValue)

This would delete records that should not be deleted. Here's an example:

+--+----+----+
| Row | FieldName | FieldValue |
+--+----+----+
| 1 | a | 123 |
| 1 | b | 234 |
| 2 | a | 345 |
| 2 | b | 456 |
| 3 | a | 123 |
| 3 | b | 666 |
+--+----+----+

Here the combination of values for "a" and "b" on every "Row" is
different. There are no duplicates here. The query that you proposed would
delete the second to last row

+--+----+----+
| 3 | a | 123 |
+--+----+----+

because it has the same FieldName and FieldValue as the first row.

Think of it the data this way:

+--+--+
| a | b |
+--+--+
| 123 | 234 |
| 345 | 456 |
| 123 | 666 |
+--+--+

No duplicate rows here.|||Hi

You could try only selecting the correct data when you move it into the
permanent tables. But the following may work better:

DELETE FROM X1
FROM X X1 JOIN X X2
ON x2.Row < x1.Row
AND x1.Fieldvalue = x2.Fieldvalue
AND x1.FieldName = x2.FieldName

John

"Alexander Anderson" <no@.spam.com> wrote in message
news:slrncfe0ft.mk1.alex@.Toronto-HSE-ppp3682122.sympatico.ca...
> I have a DELETE statement that deletes duplicate data from a table. It
> takes a long time to execute, so I thought I'd seek advice here. The
> structure of the table is little funny. The following is NOT the table,
> but the representation of the data in the table:
> +----+
> | a | b |
> +--+--+
> | 123 | 234 |
> | 345 | 456 |
> | 123 | 123 |
> +--+--+
> As you can see, the data is tabular. This is how it is stored in the
table:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> | 3 | a | 123 |
> | 3 | b | 234 |
> +--+----+----+
> What I need is to delete all records having the same "Row" when there
exists
> the same set of records with a different (smaller, to be precise) "Row".
> Using the example above, what I need to get is:
> +--+----+----+
> | Row | FieldName | FieldValue |
> +--+----+----+
> | 1 | a | 123 |
> | 1 | b | 234 |
> | 2 | a | 345 |
> | 2 | b | 456 |
> +--+----+----+
> A slow way of doing this seem to be:
> DELETE FROM X
> WHERE Row IN
> (SELECT DISTINCT Row FROM X x1
> WHERE EXISTS
> (SELECT * FROM X x2
> WHERE x2.Row < x1.Row
> AND NOT EXISTS
> (SELECT * FROM X x3
> WHERE x3.Row = x2.Row
> AND x3.FieldName = x2.FieldName
> AND x3.FieldValue <> x1.FieldValue)))
> Can this be done faster, better, and cheaper?

Optimize the Query

Listed below is tsql statement that I set up execute every 60 minutes.
To check to see if all the values exist in the following list: '806478',
'806479','806480','806481' in the Call_Movements table.
I am only interest in the first six characters and I'm converting iNum data
type from money to varchar(20).
left(cast(iNum as varchar(20)),6)
Please help me optimize the t-sql statement listed below since this table is
large.
Is there away to change the following to binary format:
DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
listed below to be optimal?
Thank You,
T-SQL
DECLARE @.CNT_MTN_REC_0 SMALLINT
SET @.CNT_MTN_REC_0 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806478'))
--
-- Count the
--
DECLARE @.CNT_MTN_REC_1 SMALLINT
SET @.CNT_MTN_REC_1 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806479'))
--
--
--
DECLARE @.CNT_MTN_REC_2 SMALLINT
SET @.CNT_MTN_REC_2 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806480'))
--
--
--
DECLARE @.CNT_MTN_REC_3 SMALLINT
SET @.CNT_MTN_REC_3 =
(Select count(iNum)
from Call_Movements
where DATEDIFF(mi, Start_Time, GETDATE()) <=60
AND left(cast(iNum as varchar(20)),6) = ('806481'))
--
--
--
if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
(@.CNT_MTN_REC_3 = 0)
BEGIN
PRINT "Error has Occurred'
ENDJoe K. (Joe K.@.discussions.microsoft.com) writes:
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum
> data type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this
> table is large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
It would have helped if you had posted the CREATE TABLE and CREATE INDEX
statements for the tables.
But first, there is no need to run four SQL statemennts.
This could either be done as:
Select count(iNum), left(cast(iNum as varchar(20)),6)
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
GROUP BY left(cast(iNum as varchar(20)),6)
This produces a result set of four rows. If you need to get the result
into variables, you can do:
Select @.CNT_MTN_REC_0 = SUM(CASE left(cast(iNum as varchar(20)),6)
WHEN '806478' THEN 1
ELSE 0
END),
..
from Call_Movements
where StartTime >= DATEADD(mi, -60, GETDATE())
AND left(cast(iNum as varchar(20)),6) IN
('806478', '806479', '806480', '806481')
As you can see, I have also changed the condition on Start_Time, in case
this column is indexed. When an indexed column appears in an expression
like in your query, the index is of on use. The query is still problematic
due to the >=. If the index on StartTime is clustered it is not much of
an issue, but if there is only a onn-clustered index, the optimizer is not
likely to pick it in this case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Joe,
Will you have iNum values that start with 806478 (or any of the other 3
values) followed by other numbers before the decimal point? For example
8064781.01?
If not, then make sure you add a nonclustered index on
Call_Movement(iNum, StartTime) and add the following predicates to the
WHERE clause:
AND iNum >= 806478
AND iNum < 806482
You could try the following query:
IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
FROM Call_Movements
WHERE StartTime >= DATEADD(minute, -60, CURRENT_TIMESTAMP)
AND iNum >= CAST(806478 AS money)
AND iNum < CAST(806482 AS money)
AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
'806481')
) < 4
BEGIN
PRINT "Error has Occurred'
END
HTH,
Gert-Jan
Joe K. wrote:
> Listed below is tsql statement that I set up execute every 60 minutes.
> To check to see if all the values exist in the following list: '806478',
> '806479','806480','806481' in the Call_Movements table.
> I am only interest in the first six characters and I'm converting iNum dat
a
> type from money to varchar(20).
> left(cast(iNum as varchar(20)),6)
> Please help me optimize the t-sql statement listed below since this table
is
> large.
> Is there away to change the following to binary format:
> DECLARE @.CNT_MTN_REC_0 SMALLINT and change the following t-sql statement
> listed below to be optimal?
> Thank You,
>
> T-SQL
> DECLARE @.CNT_MTN_REC_0 SMALLINT
> SET @.CNT_MTN_REC_0 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806478'))
> --
> -- Count the
> --
> DECLARE @.CNT_MTN_REC_1 SMALLINT
> SET @.CNT_MTN_REC_1 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806479'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_2 SMALLINT
> SET @.CNT_MTN_REC_2 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806480'))
> --
> --
> --
> DECLARE @.CNT_MTN_REC_3 SMALLINT
> SET @.CNT_MTN_REC_3 =
> (Select count(iNum)
> from Call_Movements
> where DATEDIFF(mi, Start_Time, GETDATE()) <=60
> AND left(cast(iNum as varchar(20)),6) = ('806481'))
> --
> --
> --
> if (@.CNT_MTN_REC_0 = 0) OR (@.CNT_MTN_REC_1 = 0) OR (@.CNT_MTN_REC_2 = 0) OR
> (@.CNT_MTN_REC_3 = 0)
> BEGIN
> PRINT "Error has Occurred'
> END|||And encapsulate LEFT operation to sub-query may be get more good
performance.
Gert-Jan Strik =E5=86=99=E9=81=93=EF=BC=9A
> Joe,
> Will you have iNum values that start with 806478 (or any of the other 3
> values) followed by other numbers before the decimal point? For example
> 8064781.01?
> If not, then make sure you add a nonclustered index on
> Call_Movement(iNum, StartTime) and add the following predicates to the
> WHERE clause:
> AND iNum >=3D 806478
> AND iNum < 806482
> You could try the following query:
> IF ( SELECT COUNT(DISTINCT CAST(iNum as char(6)) )
> FROM Call_Movements
> WHERE StartTime >=3D DATEADD(minute, -60, CURRENT_TIMESTAMP)
> AND iNum >=3D CAST(806478 AS money)
> AND iNum < CAST(806482 AS money)
> AND CAST(iNum as char(6)) IN ('806478', '806479', '806480',
> '806481')
> ) < 4
> BEGIN
> PRINT "Error has Occurred'
> END
>
> HTH,
> Gert-Jan
>
> Joe K. wrote:
data
le is
=3D 0) OR|||"navyzhu@.gmail.com" wrote:
> And encapsulate LEFT operation to sub-query may be get more good
> performance.
I have not done performance tests to disprove it, but I highly doubt it!
I don't think LEFT will outperform CAST.
Besides, why use the proprietary LEFT when the standard CAST will do
just fine...
Gert-Jan
> Gert-Jan Strik 写道：
>

Optimize Query

Can someone look at this sql statement and tell me if it can be sped up? Also I have to add to it by joining it with another table. How do I do that? Just by nesting another join?

Thanks!

Set rs=Server.CreateObject("ADODB.Recordset")
sql = "SELECT td.TeamID, td.TeamName, rt.PartID, rt.Effort, rt.UnitMeas, pd.MinMilesConv "
sql = sql & "FROM TeamData td INNER JOIN PartData pd ON td.TeamID = pd.TeamID "
sql = sql & "JOIN RunTrng rt ON pd.PartID = rt.PartID "
sql = sql & "WHERE rt.TrngDate >= '" & Session("beg_date") & "' AND rt.TrngDate < '" & Session("end_date")
sql = sql & "' AND pd.Archive = 'N' AND pd.Gender = '" & sGender & "' AND pd.Grade >= " & iMinGrade
sql = sql & " AND pd.Grade <= " & iMaxGrade & " ORDER BY td.TeamID"
rs.Open sql, conn, 1, 2this should run faster
because it does a Sub Select that retrieves only the
RunTrng/PartData that you need to join to the TeamData table

SELECT Team.TeamID, Team.TeamName, PartRun.PartID, PartRun.Effort, PartRun.UnitMeas, PartRun.MinMilesConv
FROM TeamData Team
INNER JOIN (SELECT Part.TeamID, Part.MinMilesConv, Run.PartID, Run.Effort, Run.UnitMeas,
FROM PartData Part
INNER JOIN RunTrng Run ON Part.PartID = Run.PartID
WHERE Run.TrngDate >= @.StartDate AND
Run.TrngDate < @.EndDate AND
Part.Archive = 'N' AND
Part.Gender = @.Gender AND
Part.Grade >= @.MinGrade AND
Part.Grade <= @.MaxGrade) PartRun
ON Team.TeamID = PartRun.TeamID
ORDER BY Team.TeamID|||Thanks! I will give it a shot!!!

Optimize "LIKE"

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.

Using '%word%' will always scan every row in the entire table.

Hope this assists.

Optimize "LIKE"

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

You can optimize like by not having a wildcard (% or ?) at the beginning of the search argument.

Using '%word%' will always scan every row in the entire table.

Hope this assists.

Optimise Select Statement

Hi
I have this select statement that I need to optimise:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
The tblCustomer.Code is a string like 'STAR00000001' the function removes
the padded zeros.
The tblEvents table contains information in a string including the
contracted Customer.Code in this format "Type STAR1 ........"
Any help would be much appreciated.
Thanks
BDon=B4t know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
> Hi
> I have this select statement that I need to optimise:
> SELECT Created, Code, tblEvents.Ref
> FROM tblCustomers, tblEvents
> WHERE tblEvents.description LIKE 'Type ' +
> dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
> The tblCustomer.Code is a string like 'STAR00000001' the function removes
> the padded zeros.
No offense, but I think you need to optimise the design, not the query. Why
are you storing padded zeros if they're irrelevant or different from the
data you're actually modeling? Why are these tables quasi-related via a
string that changes?|||Hi Aaron
I would love to change the design but it is a third party product, although
i have requested the change in a future release I need a temporary solution.
Thanks
B
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#d7CV1evFHA.2292@.TK2MSFTNGP12.phx.gbl...
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
removes
> No offense, but I think you need to optimise the design, not the query.
Why
> are you storing padded zeros if they're irrelevant or different from the
> data you're actually modeling? Why are these tables quasi-related via a
> string that changes?
>|||On Tue, 20 Sep 2005 14:51:05 +0100, Ben wrote:

>I have this select statement that I need to optimise:
>SELECT Created, Code, tblEvents.Ref
>FROM tblCustomers, tblEvents
>WHERE tblEvents.description LIKE 'Type ' +
>dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
>The tblCustomer.Code is a string like 'STAR00000001' the function removes
>the padded zeros.
>The tblEvents table contains information in a string including the
>contracted Customer.Code in this format "Type STAR1 ........"
>Any help would be much appreciated.
Hi Ben,
User-defined functions can be slow. If possible, use builtin functions
that achieve the same effect.
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE
'Type ' + REPLACE(tblCustomer.Code, '0', '') + '%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jens
Would this work for codes such as:
STAR00000102?
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127225495.861651.299360@.g47g2000cwa.googlegroups.com...
Dont know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||DECLARE @.String varchar(2000)
SEt @.String = 'STAR00000102'
SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
results in "STAR%"|||Hi Jens
Thanks for your post.
The problem is that we have codes stored in tblEvents.description as STAR1,
STAR2, STAR3......STAR102, STAR103
and for example we need to distict "Type STAR103 ...." from "Type STAR1
...."
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127294257.162562.55160@.z14g2000cwz.googlegroups.com...
> DECLARE @.String varchar(2000)
> SEt @.String = 'STAR00000102'
> SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
> results in "STAR%"
>|||That=B4s not easy, there has to be a delimiter or something where you
can tell that the trailing zeros start, how do you want to differ
perhaps
STARS100 and STARS1 ?|||On Wed, 21 Sep 2005 09:36:47 +0100, Ben wrote:

>Hi Jens
>Would this work for codes such as:
>STAR00000102?
Hi Ben,
I assume that this has to be "shortened" to STAR102?
DECLARE @.a varchar(20)
SET @.a = 'STAR00000102'
SELECT STUFF(@.a, CHARINDEX('0', @.a),
PATINDEX('%[1-9]%', @.a) - CHARINDEX('0', @.a), '')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Operand type clash: datetime is incompatible with text

I am getting this error on my insert statement, do I need to do something
for my datetime fields?
Here is the statement
INSERT INTO tblCalendar (
adjName, DispDueDate, ClaimNumber, Juris, HearingDate, HearingType,
ClaimantName, Location, HearingTime, HearingPart, WCB#, Counsel,
DateRecd)
VALUES (
@.Adjuster, @.newDispositionDueDate, @.ClaimNumber, @.Juris, @.newHearingDate,
@.HearingType, @.ClaimantName, @.HearingLocation, @.newHearingTime,
@.HearingPartRoom, @.WCBClaimNumber, @.AssignedCounsel, @.newNoticeReceived)You might want to check the datatypes of each column in tblCalendar table
and make sure they are compatible with the variables in the VALUES() clause.
If you have difficulty, post the exact CREATE TABLE statements and the
declared types and assigned values for each variables in the INSERT
statement.
Anith

OpenXML XPath question

I've included the xml and my OpenXML statement below. Using XPath in the
WITH portion of the OpenXML statement I can retrieve most values I need, but
I am trying to retrieve the values 'Last Name of Client' and 'First Name of
Client' from the xml and am not having any luck. I tried using '..' as the
xpath query but that returns text in the Question node and all child nodes.
<Section Value="AA"> Name and Identification Numbers
<Question Value="1.A"> Last Name of Client
<Question_Text>Last</Question_Text>
<Answer_Text>ROSSI1</Answer_Text>
<Answer_Value>ROSSI2</Answer_Value>
</Question>
<Question Value="2.A"> First Name of Client
<Question_Text>First Name</Question_Text>
<Answer_Text>TestA1</Answer_Text>
<Answer_Value>TestA2</Answer_Value>
</Question>
</Section>
OpenXML(@.idoc, '/ns0:GoldCare_Assessment/Section/Question')
WITH ([SectionValue] varchar(50) '../@.Value',
[QuestionValue] varchar(50) '@.Value',
[Question_Text] varchar(50) './Question_Text',
[Answer_Text] varchar(50) './Answer_Text',
[Answer_Value] varchar(50) './Answer_Value')Look at the bottom of this article where the stored procedure is shown:
http://www.eggheadcafe.com/articles...er_bulkload.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:e1us7sSGFHA.2756@.TK2MSFTNGP15.phx.gbl...
> I've included the xml and my OpenXML statement below. Using XPath in the
> WITH portion of the OpenXML statement I can retrieve most values I need,
> but
> I am trying to retrieve the values 'Last Name of Client' and 'First Name
> of
> Client' from the xml and am not having any luck. I tried using '..' as
> the
> xpath query but that returns text in the Question node and all child
> nodes.
> <Section Value="AA"> Name and Identification Numbers
> <Question Value="1.A"> Last Name of Client
> <Question_Text>Last</Question_Text>
> <Answer_Text>ROSSI1</Answer_Text>
> <Answer_Value>ROSSI2</Answer_Value>
> </Question>
> <Question Value="2.A"> First Name of Client
> <Question_Text>First Name</Question_Text>
> <Answer_Text>TestA1</Answer_Text>
> <Answer_Value>TestA2</Answer_Value>
> </Question>
> </Section>
> OpenXML(@.idoc, '/ns0:GoldCare_Assessment/Section/Question')
> WITH ([SectionValue] varchar(50) '../@.Value',
> [QuestionValue] varchar(50) '@.Value',
> [Question_Text] varchar(50) './Question_Text',
> [Answer_Text] varchar(50) './Answer_Text',
> [Answer_Value] varchar(50) './Answer_Value')
>
>