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.
>
Friday, March 30, 2012
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
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.
>
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 in mssql
Hi there,
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?
I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?
Cheers,
CeliaI presume optimise table defragments the table and indexes? If not - please post what it means.
To defrag an index check out the following in BoL:
2000
- DBCC DBREINDEX
- DBCC INDEXDEFRAG
2005
- ALTER INDEX ... REORGANISE\ REBUILD
You will need to see which options are best for your environment.
Is there an optimize table command in mssql which will work the same way as "OPTIMIZE TABLE tablename" of mysql?
I have a php application that should work on both mysql and mssql. To do defragmentation, I am using the above command. Is there an equivalent in mssql?
Cheers,
CeliaI presume optimise table defragments the table and indexes? If not - please post what it means.
To defrag an index check out the following in BoL:
2000
- DBCC DBREINDEX
- DBCC INDEXDEFRAG
2005
- ALTER INDEX ... REORGANISE\ REBUILD
You will need to see which options are best for your environment.
optimizing stored procedures
hi all,
i'm working in a database, i'm not the creater of it, and was assigned to optimize the stored procedures running against this database.
can someone tell me what steps should i follow to check these SP's and to what should i look at and what can be done to optimise them.
thanks expertsIt might be a two line question but it is a massive subject. People make their careers out of this.
read stuf here:
http://www.sql-server-performance.com/
Learn to read execution plans.
Learn about SET STATISTICS IO
Learn to use profiler.
Read BoL (section on Optimising Database Performance).
Learn your database.
Learn about indexing strategies.|||First make sure all the critical joins are indexed. Then eliminate any cursors or loops.|||I usually I just ask the QA people what parts of the app sucks the will to live out of them and then I trace those screens to identify the really big pieces of suck ass code and I look at the execution plans of those queries. I like being reactive.|||exactly. the ONLY sure way to find where the perf problems are is to measure.
ask any dev where the most time is spent in their code, and 90% will give you the wrong answer. the other 10% are right only because they have profiled it. :)|||I would trac it all to a table, th query the damn thing for the longest running pieces of code...
I guess we could make you look like a hero, sinve it seems no one else knows what's going on.
How big is this company?
And how what's your experience level
And do you have SQL Server client tools installed
Do you know what books online is?
Do you know what Google is?|||I guess we could make you look like a hero
...
Do you know what Google is?Downgrading as you type.|||There are some good articles about perf tuning here:
http://www.sql-server-performance.com/articles_performance.asp
http://www.sql-server-performance.com/articles_audit.asp
in particular this one will show you how to use profiler for catching the biggest pigs in your database:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
and elsewhere on that site.
Be aware, however, sql is not the only process that can kill your perf. compiled code can be poorly written too. for compiled code you need to use a different profiling tool (I usually use perf studio in VS).|||I trace all the queries to a table, as Brett mentioned. Then query it for the total number of reads grouped by say the first 50 or 100 characters of the textdata field. I find it is usually better to tune the average running query that runs 1,000's of times per day than the one pig that runs once at midnight. With all the variations that the textdata field can have, getting that aggregate right is a challenge, though.|||...
Downgrading as you type.
Are you saying that you should assume something about someone's experience?|||Nope - but if you take a poster on a forum from not knowing what google is to being a hero dba then I will eat all my hats.
Anyway yes - the OP needs to identify the sprocs to tune and then needs to learn the skills to tune them. Or post them here of course.
i'm working in a database, i'm not the creater of it, and was assigned to optimize the stored procedures running against this database.
can someone tell me what steps should i follow to check these SP's and to what should i look at and what can be done to optimise them.
thanks expertsIt might be a two line question but it is a massive subject. People make their careers out of this.
read stuf here:
http://www.sql-server-performance.com/
Learn to read execution plans.
Learn about SET STATISTICS IO
Learn to use profiler.
Read BoL (section on Optimising Database Performance).
Learn your database.
Learn about indexing strategies.|||First make sure all the critical joins are indexed. Then eliminate any cursors or loops.|||I usually I just ask the QA people what parts of the app sucks the will to live out of them and then I trace those screens to identify the really big pieces of suck ass code and I look at the execution plans of those queries. I like being reactive.|||exactly. the ONLY sure way to find where the perf problems are is to measure.
ask any dev where the most time is spent in their code, and 90% will give you the wrong answer. the other 10% are right only because they have profiled it. :)|||I would trac it all to a table, th query the damn thing for the longest running pieces of code...
I guess we could make you look like a hero, sinve it seems no one else knows what's going on.
How big is this company?
And how what's your experience level
And do you have SQL Server client tools installed
Do you know what books online is?
Do you know what Google is?|||I guess we could make you look like a hero
...
Do you know what Google is?Downgrading as you type.|||There are some good articles about perf tuning here:
http://www.sql-server-performance.com/articles_performance.asp
http://www.sql-server-performance.com/articles_audit.asp
in particular this one will show you how to use profiler for catching the biggest pigs in your database:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
and elsewhere on that site.
Be aware, however, sql is not the only process that can kill your perf. compiled code can be poorly written too. for compiled code you need to use a different profiling tool (I usually use perf studio in VS).|||I trace all the queries to a table, as Brett mentioned. Then query it for the total number of reads grouped by say the first 50 or 100 characters of the textdata field. I find it is usually better to tune the average running query that runs 1,000's of times per day than the one pig that runs once at midnight. With all the variations that the textdata field can have, getting that aggregate right is a challenge, though.|||...
Downgrading as you type.
Are you saying that you should assume something about someone's experience?|||Nope - but if you take a poster on a forum from not knowing what google is to being a hero dba then I will eat all my hats.
Anyway yes - the OP needs to identify the sprocs to tune and then needs to learn the skills to tune them. Or post them here of course.
Optimizing Store Procedure and Avoiding Cursors....
I'm trying to optimize an ETL batch procedure which takes an extremely long
time to run. I was using cursors for most part of the batch script on
different Store Procedures. Now I'm trying to remove the cursors and replace
them with alternative methods such as using temp tables.
I'm really puzzled as to why the cursor procedure runs much faster than
using temp tables... can anyone point out what is wrong to me?
Below is a snippet of 2 test SPs I've compared against.
TestTable holds 200000 records with 3 columns
1. Using Cursors (This procedure took 4 - 8 secs to run)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare test_cursor cursor for select header, body, footer from testTable
order by header
open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
set @.test = 0
while @.@.fetch_status = 0
begin
set @.test = @.test + 1
fetch next from test_cursor into @.header, @.body, @.footer
end
select @.test
close test_cursor
deallocate test_cursor
2. Using Temp Tables (This procedure took > 1 min)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare @.count int, @.loopcount int
select identity(int,1,1) id, header, body, footer into #temp from testtable
select @.count = @.@.rowcount
set @.loopcount = 1
while @.loopcount <= @.count
begin
select @.header = header, @.body = body, @.footer = footer from
#temp where id = @.loopcount
set @.loopcount = @.loopcount + 1
end
drop table #tempLooking at the code I think it maybe the the where clause in the tempdb,
rember that it has to search through all 200000 records whereas with the
cursor your going through them from start to finish, no searching.
Peter
"Happiness is nothing more than good health and a bad memory."
Albert Schweitzer
"Nestor" wrote:
> I'm trying to optimize an ETL batch procedure which takes an extremely lon
g
> time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and repla
ce
> them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from testtab
le
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>
>|||Both 1 and 2 are cursors! Looping through a temp table is just a cursor
in disguise. Unfortunately your sample code doesn't do anything useful
so it's impossible to suggest an alternative set-based solution without
using cursors. Your goal should be to avoid processing one row at a
time by ANY method.
If you need more help please post DDL, sample data INSERTs and show
your required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello Nestor,
Going through your code, I dont see any reason why the #temp based approach
should be any better
than the Cursor based approach (if it is supposed to be).
The #temp based approach, Loops anyway with a while Loop which is simillar
to Cursor based method.
Normally, when we say we have to get rid of the cursor, we mean to replace
the Cursor based approach
with a SET BASED APPROACH and your #temp based approach still does record by
record. And as Peter says
for each iteration a select is executed against the table.
Hope this helps.
Gopi
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Although I haven't tested it, I'd expect the pseudo-cursor would perform
much better if you add a unique clustered index on the #temp id column.
However, as David pointed out, changing a cursor to another iterative
technique isn't the ideal solution and might not be any faster than the
cursor.
We can't make any specific recommendations because the example you posted
does no productive work. If your actual process does something like execute
another proc for each row returned, the set-based solution would be to use
in-line set-based processing instead. This technique is often orders of
magnitude faster than an iterative approach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Dan,
Could you please share with me what "in-line set-based " means.
I was looking at a Stored Proc which has a nested cursors (One cursor inside
another cursor)
and the While loop of the second a SP is called with the two parameters
(each of them coming
from each of the Cursor code.
Here is the PSeudo-Code :
Declare Cursor Cursor1 Select * from table1
Open Cursor1
Fetch into @.var1
While @.@.FETCH_STATUS <> 1
Declare Cursor Cursor2 Select * from table2
Open Cursor2
Fetch into @.var2
While @.@.FETCH_STATUS <> 1
Exec Spname @.var1, @.var2
........
.....
DEALLOCATE Cursor1
DEALLOCATE Cursor2
I have noticed that the two cursors can be combined into one using
appropriate Joins.
However, I was under the impression I will still have to use a Cursor for
executing the SP
Could you please point me in the right direction if there is a way out.
Thanks,
Gopi
<< If your actual process does something like execute another proc for each
row returned, the set-based
solution would be to use in-line set-based processing instead>>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
> Although I haven't tested it, I'd expect the pseudo-cursor would perform
> much better if you add a unique clustered index on the #temp id column.
> However, as David pointed out, changing a cursor to another iterative
> technique isn't the ideal solution and might not be any faster than the
> cursor.
> We can't make any specific recommendations because the example you posted
> does no productive work. If your actual process does something like
> execute another proc for each row returned, the set-based solution would
> be to use in-line set-based processing instead. This technique is often
> orders of magnitude faster than an iterative approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||> However, I was under the impression I will still have to use a Cursor
for
> executing the SP
Yes, using an SP in this way forces you to use the iterative row-by-row
approach. The set-based solution is to perform whatever logic is
contained in SPname using a query against your tables so that the
entire operation is performed against the data set rather than one row
at a time. For data manipulation operations that usually has advantages
over the cursor in terms of performance, scalability and
maintainability.
David Portas
SQL Server MVP
--|||> Here is the PSeudo-Code :
Your pseudo-code effectively calls the proc for each row returned by the
Cartesian product of table1 and table2. Assuming that the executed proc
simply inserts into a third table, a working code example:
CREATE TABLE table1
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table1 VALUES(1)
INSERT INTO table1 VALUES(2)
INSERT INTO table1 VALUES(3)
CREATE TABLE table2
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table2 VALUES(4)
INSERT INTO table2 VALUES(5)
INSERT INTO table2 VALUES(6)
CREATE TABLE table3
(
Col1 int NOT NULL,
Col2 int NOT NULL,
PRIMARY KEY (Col1, Col2)
)
GO
CREATE PROC Spname
@.var1 int,
@.var2 int
AS
SET NOCOUNT ON
INSERT INTO Table3(Col1, Col2)
VALUES(@.var1, @.var2)
GO
DECLARE @.var1 int,
@.var2 int
DECLARE Cursor1 CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM table1
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.var1
WHILE @.@.FETCH_STATUS <> -1
BEGIN
DECLARE Cursor2 CURSOR LOCAL FOR SELECT * FROM table2
OPEN Cursor2
FETCH NEXT FROM Cursor2 INTO @.var2
WHILE @.@.FETCH_STATUS <> -1
BEGIN
EXEC Spname @.var1, @.var2
FETCH NEXT FROM Cursor2 INTO @.var2
END
CLOSE Cursor2
DEALLOCATE Cursor2
FETCH NEXT FROM Cursor1 INTO @.var1
END
CLOSE Cursor1
DEALLOCATE Cursor1
GO
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
Exactly. That's the first step.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
Yes you will. This is why I suggested including the equivalent proc code
inline *instead of* executing the proc for each row. The entire cursor
script and proc code in my functional example could be replaced with a
single insert statement:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
CROSS JOIN table2 t2
Of course, a lot depends on exactly what the called proc does. Also, your
actual code probably isn't a CROSS JOIN. A simple INNER JOIN example:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
JOIN table2 t2 ON
t1.Col1 = t2.SomeColumn
If you need more help, you need to provide details on what the proc actually
does. Actual code will help.
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:%23Sx92CLJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Dan,
> Could you please share with me what "in-line set-based " means.
> I was looking at a Stored Proc which has a nested cursors (One cursor
> inside another cursor)
> and the While loop of the second a SP is called with the two parameters
> (each of them coming
> from each of the Cursor code.
> Here is the PSeudo-Code :
> Declare Cursor Cursor1 Select * from table1
> Open Cursor1
> Fetch into @.var1
> While @.@.FETCH_STATUS <> 1
>
> Declare Cursor Cursor2 Select * from table2
> Open Cursor2
> Fetch into @.var2
> While @.@.FETCH_STATUS <> 1
> Exec Spname @.var1, @.var2
> ........
> .....
> DEALLOCATE Cursor1
> DEALLOCATE Cursor2
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
> Could you please point me in the right direction if there is a way out.
> Thanks,
> Gopi
> << If your actual process does something like execute another proc for
> each row returned, the set-based
> solution would be to use in-line set-based processing instead>>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
>|||Thanks for pointing that out. I've got the code base from an article in
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
Am I doing it wrong with instructions from that article? In any case, what
exactly is SET BASED APPROACH? I do realise and know that SQL Server is
meant to be relational and not sequential, but I need to understand more on
how to apply the alternative first before trying to overhaul the scripts
running in the production servers.
Can anyone give me an equivalent of the cursor procedure I posted using SET
BASE APPROACH so that I can better relate?
Thanks.
Regards,
Nestor
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:e$w%23rZJJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hello Nestor,
> Going through your code, I dont see any reason why the #temp based
> approach should be any better
> than the Cursor based approach (if it is supposed to be).
> The #temp based approach, Loops anyway with a while Loop which is simillar
> to Cursor based method.
> Normally, when we say we have to get rid of the cursor, we mean to replace
> the Cursor based approach
> with a SET BASED APPROACH and your #temp based approach still does record
> by record. And as Peter says
> for each iteration a select is executed against the table.
> Hope this helps.
> Gopi
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The article demonstrates some techniques for iteration but doesn't give
any recommendations as to when to use them. 99.99% of the time
row-by-row processing isn't necessary so those techniques shouldn't be
required. The Set Based approach means that you use SQL DML statements
(basically SELECT, UPDATE, INSERT, DELETE) that operate on a SET of
rows at a time rather than ONE row at a time. That should always be the
standard, preferred way to write code.
> Can anyone give me an equivalent of the cursor procedure I posted
using SET
> BASE APPROACH so that I can better relate?
But your cursor didn't do anything except assign a few variables. How
can we show you how to replace it if we don't know what it's supposed
to do? What we need to know is what you are actually trying to achieve
then we can suggest the right solution. It may even be that there isn't
a set-based solution to your problem - that's rarely the case but we
can't tell you until we understand the problem.
The following article explains the best way to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--
time to run. I was using cursors for most part of the batch script on
different Store Procedures. Now I'm trying to remove the cursors and replace
them with alternative methods such as using temp tables.
I'm really puzzled as to why the cursor procedure runs much faster than
using temp tables... can anyone point out what is wrong to me?
Below is a snippet of 2 test SPs I've compared against.
TestTable holds 200000 records with 3 columns
1. Using Cursors (This procedure took 4 - 8 secs to run)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare test_cursor cursor for select header, body, footer from testTable
order by header
open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
set @.test = 0
while @.@.fetch_status = 0
begin
set @.test = @.test + 1
fetch next from test_cursor into @.header, @.body, @.footer
end
select @.test
close test_cursor
deallocate test_cursor
2. Using Temp Tables (This procedure took > 1 min)
declare @.test as numeric
declare @.header as nvarchar(50)
declare @.body as nvarchar(50)
declare @.footer as nvarchar(50)
declare @.count int, @.loopcount int
select identity(int,1,1) id, header, body, footer into #temp from testtable
select @.count = @.@.rowcount
set @.loopcount = 1
while @.loopcount <= @.count
begin
select @.header = header, @.body = body, @.footer = footer from
#temp where id = @.loopcount
set @.loopcount = @.loopcount + 1
end
drop table #tempLooking at the code I think it maybe the the where clause in the tempdb,
rember that it has to search through all 200000 records whereas with the
cursor your going through them from start to finish, no searching.
Peter
"Happiness is nothing more than good health and a bad memory."
Albert Schweitzer
"Nestor" wrote:
> I'm trying to optimize an ETL batch procedure which takes an extremely lon
g
> time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and repla
ce
> them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from testtab
le
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>
>|||Both 1 and 2 are cursors! Looping through a temp table is just a cursor
in disguise. Unfortunately your sample code doesn't do anything useful
so it's impossible to suggest an alternative set-based solution without
using cursors. Your goal should be to avoid processing one row at a
time by ANY method.
If you need more help please post DDL, sample data INSERTs and show
your required end result.
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello Nestor,
Going through your code, I dont see any reason why the #temp based approach
should be any better
than the Cursor based approach (if it is supposed to be).
The #temp based approach, Loops anyway with a while Loop which is simillar
to Cursor based method.
Normally, when we say we have to get rid of the cursor, we mean to replace
the Cursor based approach
with a SET BASED APPROACH and your #temp based approach still does record by
record. And as Peter says
for each iteration a select is executed against the table.
Hope this helps.
Gopi
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Although I haven't tested it, I'd expect the pseudo-cursor would perform
much better if you add a unique clustered index on the #temp id column.
However, as David pointed out, changing a cursor to another iterative
technique isn't the ideal solution and might not be any faster than the
cursor.
We can't make any specific recommendations because the example you posted
does no productive work. If your actual process does something like execute
another proc for each row returned, the set-based solution would be to use
in-line set-based processing instead. This technique is often orders of
magnitude faster than an iterative approach.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nestor" <n3570r@.yahoo.com> wrote in message
news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> I'm trying to optimize an ETL batch procedure which takes an extremely
> long time to run. I was using cursors for most part of the batch script on
> different Store Procedures. Now I'm trying to remove the cursors and
> replace them with alternative methods such as using temp tables.
> I'm really puzzled as to why the cursor procedure runs much faster than
> using temp tables... can anyone point out what is wrong to me?
> Below is a snippet of 2 test SPs I've compared against.
> TestTable holds 200000 records with 3 columns
> 1. Using Cursors (This procedure took 4 - 8 secs to run)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare test_cursor cursor for select header, body, footer from testTable
> order by header
> open test_cursor fetch next from test_cursor into @.header, @.body, @.footer
> set @.test = 0
> while @.@.fetch_status = 0
> begin
> set @.test = @.test + 1
> fetch next from test_cursor into @.header, @.body, @.footer
> end
> select @.test
> close test_cursor
> deallocate test_cursor
>
> 2. Using Temp Tables (This procedure took > 1 min)
> declare @.test as numeric
> declare @.header as nvarchar(50)
> declare @.body as nvarchar(50)
> declare @.footer as nvarchar(50)
> declare @.count int, @.loopcount int
> select identity(int,1,1) id, header, body, footer into #temp from
> testtable
> select @.count = @.@.rowcount
> set @.loopcount = 1
> while @.loopcount <= @.count
> begin
> select @.header = header, @.body = body, @.footer = footer from
> #temp where id = @.loopcount
> set @.loopcount = @.loopcount + 1
> end
> drop table #temp
>
>|||Dan,
Could you please share with me what "in-line set-based " means.
I was looking at a Stored Proc which has a nested cursors (One cursor inside
another cursor)
and the While loop of the second a SP is called with the two parameters
(each of them coming
from each of the Cursor code.
Here is the PSeudo-Code :
Declare Cursor Cursor1 Select * from table1
Open Cursor1
Fetch into @.var1
While @.@.FETCH_STATUS <> 1
Declare Cursor Cursor2 Select * from table2
Open Cursor2
Fetch into @.var2
While @.@.FETCH_STATUS <> 1
Exec Spname @.var1, @.var2
........
.....
DEALLOCATE Cursor1
DEALLOCATE Cursor2
I have noticed that the two cursors can be combined into one using
appropriate Joins.
However, I was under the impression I will still have to use a Cursor for
executing the SP
Could you please point me in the right direction if there is a way out.
Thanks,
Gopi
<< If your actual process does something like execute another proc for each
row returned, the set-based
solution would be to use in-line set-based processing instead>>
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
> Although I haven't tested it, I'd expect the pseudo-cursor would perform
> much better if you add a unique clustered index on the #temp id column.
> However, as David pointed out, changing a cursor to another iterative
> technique isn't the ideal solution and might not be any faster than the
> cursor.
> We can't make any specific recommendations because the example you posted
> does no productive work. If your actual process does something like
> execute another proc for each row returned, the set-based solution would
> be to use in-line set-based processing instead. This technique is often
> orders of magnitude faster than an iterative approach.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||> However, I was under the impression I will still have to use a Cursor
for
> executing the SP
Yes, using an SP in this way forces you to use the iterative row-by-row
approach. The set-based solution is to perform whatever logic is
contained in SPname using a query against your tables so that the
entire operation is performed against the data set rather than one row
at a time. For data manipulation operations that usually has advantages
over the cursor in terms of performance, scalability and
maintainability.
David Portas
SQL Server MVP
--|||> Here is the PSeudo-Code :
Your pseudo-code effectively calls the proc for each row returned by the
Cartesian product of table1 and table2. Assuming that the executed proc
simply inserts into a third table, a working code example:
CREATE TABLE table1
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table1 VALUES(1)
INSERT INTO table1 VALUES(2)
INSERT INTO table1 VALUES(3)
CREATE TABLE table2
(
Col1 int NOT NULL PRIMARY KEY
)
INSERT INTO table2 VALUES(4)
INSERT INTO table2 VALUES(5)
INSERT INTO table2 VALUES(6)
CREATE TABLE table3
(
Col1 int NOT NULL,
Col2 int NOT NULL,
PRIMARY KEY (Col1, Col2)
)
GO
CREATE PROC Spname
@.var1 int,
@.var2 int
AS
SET NOCOUNT ON
INSERT INTO Table3(Col1, Col2)
VALUES(@.var1, @.var2)
GO
DECLARE @.var1 int,
@.var2 int
DECLARE Cursor1 CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT * FROM table1
OPEN Cursor1
FETCH NEXT FROM Cursor1 INTO @.var1
WHILE @.@.FETCH_STATUS <> -1
BEGIN
DECLARE Cursor2 CURSOR LOCAL FOR SELECT * FROM table2
OPEN Cursor2
FETCH NEXT FROM Cursor2 INTO @.var2
WHILE @.@.FETCH_STATUS <> -1
BEGIN
EXEC Spname @.var1, @.var2
FETCH NEXT FROM Cursor2 INTO @.var2
END
CLOSE Cursor2
DEALLOCATE Cursor2
FETCH NEXT FROM Cursor1 INTO @.var1
END
CLOSE Cursor1
DEALLOCATE Cursor1
GO
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
Exactly. That's the first step.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
Yes you will. This is why I suggested including the equivalent proc code
inline *instead of* executing the proc for each row. The entire cursor
script and proc code in my functional example could be replaced with a
single insert statement:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
CROSS JOIN table2 t2
Of course, a lot depends on exactly what the called proc does. Also, your
actual code probably isn't a CROSS JOIN. A simple INNER JOIN example:
INSERT INTO Table3(Col1, Col2)
SELECT t1.Col1, t2.Col1
FROM table1 t1
JOIN table2 t2 ON
t1.Col1 = t2.SomeColumn
If you need more help, you need to provide details on what the proc actually
does. Actual code will help.
Hope this helps.
Dan Guzman
SQL Server MVP
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:%23Sx92CLJFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Dan,
> Could you please share with me what "in-line set-based " means.
> I was looking at a Stored Proc which has a nested cursors (One cursor
> inside another cursor)
> and the While loop of the second a SP is called with the two parameters
> (each of them coming
> from each of the Cursor code.
> Here is the PSeudo-Code :
> Declare Cursor Cursor1 Select * from table1
> Open Cursor1
> Fetch into @.var1
> While @.@.FETCH_STATUS <> 1
>
> Declare Cursor Cursor2 Select * from table2
> Open Cursor2
> Fetch into @.var2
> While @.@.FETCH_STATUS <> 1
> Exec Spname @.var1, @.var2
> ........
> .....
> DEALLOCATE Cursor1
> DEALLOCATE Cursor2
> I have noticed that the two cursors can be combined into one using
> appropriate Joins.
> However, I was under the impression I will still have to use a Cursor for
> executing the SP
> Could you please point me in the right direction if there is a way out.
> Thanks,
> Gopi
> << If your actual process does something like execute another proc for
> each row returned, the set-based
> solution would be to use in-line set-based processing instead>>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OXOup8KJFHA.4092@.tk2msftngp13.phx.gbl...
>|||Thanks for pointing that out. I've got the code base from an article in
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
Am I doing it wrong with instructions from that article? In any case, what
exactly is SET BASED APPROACH? I do realise and know that SQL Server is
meant to be relational and not sequential, but I need to understand more on
how to apply the alternative first before trying to overhaul the scripts
running in the production servers.
Can anyone give me an equivalent of the cursor procedure I posted using SET
BASE APPROACH so that I can better relate?
Thanks.
Regards,
Nestor
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:e$w%23rZJJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hello Nestor,
> Going through your code, I dont see any reason why the #temp based
> approach should be any better
> than the Cursor based approach (if it is supposed to be).
> The #temp based approach, Loops anyway with a while Loop which is simillar
> to Cursor based method.
> Normally, when we say we have to get rid of the cursor, we mean to replace
> the Cursor based approach
> with a SET BASED APPROACH and your #temp based approach still does record
> by record. And as Peter says
> for each iteration a select is executed against the table.
> Hope this helps.
> Gopi
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:ORU5F$IJFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The article demonstrates some techniques for iteration but doesn't give
any recommendations as to when to use them. 99.99% of the time
row-by-row processing isn't necessary so those techniques shouldn't be
required. The Set Based approach means that you use SQL DML statements
(basically SELECT, UPDATE, INSERT, DELETE) that operate on a SET of
rows at a time rather than ONE row at a time. That should always be the
standard, preferred way to write code.
> Can anyone give me an equivalent of the cursor procedure I posted
using SET
> BASE APPROACH so that I can better relate?
But your cursor didn't do anything except assign a few variables. How
can we show you how to replace it if we don't know what it's supposed
to do? What we need to know is what you are actually trying to achieve
then we can suggest the right solution. It may even be that there isn't
a set-based solution to your problem - that's rarely the case but we
can't tell you until we understand the problem.
The following article explains the best way to post your problem here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--
Optimizing store proc
I have the following store proc and was wondering if I can optimized it by using a SELECT CASE instead of all those IF? I tried but don't know how to write it.
Thanks
set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[Get_Cl_SearchMultiColumn]( @.strSearchTermColumnNamenvarchar (50),@.strSearchTermSearchTermnvarchar (200) )as if (@.strSearchTermColumnName ='Monitor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2FROM Cl_SystemsWHERE contains(Monitor1,@.strSearchTerm)orcontains(Monitor2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='MonitorSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2SerialFROM Cl_SystemsWHERE contains(Monitor1Serial,@.strSearchTerm)orcontains(Monitor2Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Microscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Microscope1,Microscope2FROM Cl_SystemsWHERE contains(Microscope1,@.strSearchTerm)orcontains(Microscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialMicroscope')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialMicroscope1,SerialMicroscope2FROM Cl_SystemsWHERE contains(SerialMicroscope1,@.strSearchTerm)orcontains(SerialMicroscope2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Controller')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Controller1,Controller2FROM Cl_SystemsWHERE contains(Controller1,@.strSearchTerm)orcontains(Controller2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ControllerFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Cont1Firmware,Cont2FirmwareFROM Cl_SystemsWHERE contains(Cont1Firmware,@.strSearchTerm)orcontains(Cont2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialController')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialController1,SerialController2FROM Cl_SystemsWHERE contains(SerialController1,@.strSearchTerm)orcontains(SerialController2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Joystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joystick1,Joystick2FROM Cl_SystemsWHERE contains(Joystick1,@.strSearchTerm)orcontains(Joystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='JoystickFirmware')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Joy1Firmware,Joy2FirmwareFROM Cl_SystemsWHERE contains(Joy1Firmware,@.strSearchTerm)orcontains(Joy2Firmware,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='SerialJoystick')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,SerialJoystick1,SerialJoystick2FROM Cl_SystemsWHERE contains(SerialJoystick1,@.strSearchTerm)orcontains(SerialJoystick2,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Camera')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1,Camera2,Camera3,Camera4FROM Cl_SystemsWHERE contains(Camera1,@.strSearchTerm)orcontains(Camera2,@.strSearchTerm)orcontains(Camera3,@.strSearchTerm)orcontains(Camera4,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='CameraSerial')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Camera1Serial,Camera2Serial,Camera3Serial,Camera4SerialFROM Cl_SystemsWHERE contains(Camera1Serial,@.strSearchTerm)orcontains(Camera2Serial,@.strSearchTerm)orcontains(Camera3Serial,@.strSearchTerm)orcontains(Camera4Serial,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='ZMotor')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,ZMotor1,ZMotor2,ZMotor3FROM Cl_SystemsWHERE contains(ZMotor1,@.strSearchTerm)orcontains(ZMotor2,@.strSearchTerm)orcontains(ZMotor3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Stage')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Stage1,Stage2,Stage3FROM Cl_SystemsWHERE contains(Stage1,@.strSearchTerm)orcontains(Stage2,@.strSearchTerm)orcontains(Stage3,@.strSearchTerm)return 0end if (@.strSearchTermColumnName ='Lens')begin SELECT CustomerID,SystemId,CompanyName,City,State,Country,Lens1,Lens2,Lens3FROM Cl_SystemsWHERE contains(Lens1,@.strSearchTerm)orcontains(Lens2,@.strSearchTerm)orcontains(Lens3,@.strSearchTerm)return 0end
I don't know why you need a WHERE clause with CONTAINS predicate but try the link below for CASE statement for an expert. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm
|||Well it seems that I am trying to use the CASE in a different situation then what explain everywhere.
I would like to use it one of the parameters sent to my store proc but I am not sure if it is possible and how to write it
Something like:
Select Case @.strSearchTermColumnName
Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end
case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...
End Select
|||Oups I made a few mistakes in my last statement, I should have been:
Well it seems that I am trying to use the CASE in a different situation than what explained everywhere.
I would like to use it on one of the parameters sent to my store proc but I am not sure if it is possible and how to write it
Something like:
Select Case @.strSearchTerm
Case 'Monitor'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1,Monitor2
FROM Cl_Systems
WHERE contains(Monitor1,@.strSearchTerm) or contains(Monitor2,@.strSearchTerm)
return 0
end
case 'MonitorSerial'
begin
SELECT CustomerID,SystemId,CompanyName,City,State,Country,Monitor1Serial,Monitor2Serial
FROM Cl_Systems
WHERE contains(Monitor1Serial,@.strSearchTerm) or contains(Monitor2Serial,@.strSearchTerm)
return 0
end
...
End Select
|||
What I am saying is I don't know if you can use the CONTAINS predicate with CASE statement. The links below one is a full text expert he knows more about fulltext and the other two are the two versions of CONTAINS. Hope this helps.
http://spaces.msn.com/jtkane/
http://msdn2.microsoft.com/en-US/library/ms189760.aspx
http://msdn2.microsoft.com/en-US/library/ms187787.aspx
sql
Subscribe to:
Posts (Atom)