Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

select like 'abc%' problem

0 views
Skip to first unread message

x.x.

unread,
Dec 13, 1999, 3:00:00 AM12/13/99
to
ms sql server 7.0 running on P III 450mhz NT4.0 machine.
databes /firstname(key), lastname, number/ with 10.000.000 records, 1.5GB
database

select * from table1 where firstname like 'dfg%'
response time : 4 seconds

select * from table1 where firstname like 'abc%'
response time : 3.5 minutes


if i enter a select statement to display names which begins with 3 any
letters, i get results in a few seconds
if i want to display results which begin with 'abc%' it takes several
minutes (the same amount of time as if i search by nonkey field)

Can anyone answer why!

regards iztok


Chris Georgiou - TZOTZIOY

unread,
Dec 13, 1999, 3:00:00 AM12/13/99
to
On Mon, 13 Dec 1999 14:51:51 +0100, rumours say that "x.x."
<tv...@mail.com> might have written:

If most of your [firstname] start with 'abc', then the optimiser
suggests a table scan. If not, try rebuilding the index.
--
I am Greeks and I speak England very best,
TZOTZIOY, ICQ# 13397953
Favourite spammers this week: ipro...@mihacienda.com expr...@bigfoot.com in...@yingleegifts.com.hk lea...@fiberia.com

x.x.

unread,
Dec 15, 1999, 3:00:00 AM12/15/99
to
hi!
thank you for answer . it may be so but i have random generated database
with quite equal firstnames started with 'ABC' or 'BCD' or 'EFV'. it is
slow only on 'ABC'
is it possible to customize this optimizer behavior. I was looking at sql
help and they dont say much about that.

regards Iztok

Chris Georgiou - TZOTZIOY wrote in message ...

Dan Guzman

unread,
Dec 15, 1999, 3:00:00 AM12/15/99
to
Optimizer hints are described in Transact-SQL help in the table hints
section of the From clause description. An Example:

Select LastName From TableName With Index(IndexName) Where LastName Like
'ABC%'


Hope this helps.

> snip <


> is it possible to customize this optimizer behavior. I was looking at sql
> help and they dont say much about that.
>

> snip <


BPMargolin

unread,
Dec 15, 1999, 3:00:00 AM12/15/99
to
Try using the WITH FULLSCAN option on the UPDATE STATISTICS command. See
section "UPDATE STATISTICS (T-SQL)" in the SQL Server 7.0 Books Online.

x.x. <tv...@mail.com> wrote in message
news:YBI54.59$2s1....@news.siol.net...


> hi!
> thank you for answer . it may be so but i have random generated database
> with quite equal firstnames started with 'ABC' or 'BCD' or 'EFV'. it is
> slow only on 'ABC'

> is it possible to customize this optimizer behavior. I was looking at sql
> help and they dont say much about that.
>

Doug Atterbury

unread,
Dec 18, 1999, 3:00:00 AM12/18/99
to
Rebuilding the index is a good idea, as they can get large and inefficient
after many inserts and updates. Another way to improve query speed is to
use UPDATE STATISTICS, which helps the optimizer. We update statistics on
all active tables every night, and rebuild indexes every weekend.
0 new messages