How to improve a query with a like statement

Question

Post reply

How to improve a query with a like statement

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 1

BTW Mr Fritchey, I get your book two days ago, is really good.

I will reorganize all indexes ( last time I did a rebuild ) and upodate the stats to see if that helps

Grant Fritchey SSC Guru Points: 398952 More actions · Answer 2

ricardo_chicas (4/29/2010)
Yes I use sp_updatestats, The query is actually the same I am running ( just with different names)--something weird us that if I add one more number to the like contition it turns into a seek.....
Btw, about the <> '0' condition, if I convert that into a abs(column) > 0 the performance improves greatly, is ok to do that, right?

You might need to do an UPDATE STATISTICS tablename WITH FULLSCAN... I'm not sure, but I'd try it.

Yes, you add one more value and now the data is selective enough that you get a seek instead of a scan. I'm pretty sure it's either your statistics, less likely, or the selectivity of the index, more likely. A varchar might not be the best thing to use for a clustering key on a table this large.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

ricardo_chicas (4/29/2010)
How many rows are in the table ?
A\ 70 million rows
How many rows does the query above really return ?
A\query returns about 70 thousand
What do your indexes look like ?
CONSTRAINT [column_key:PrimaryKey] PRIMARY KEY CLUSTERED
(
[FB_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Tables1]
) ON [Tables1]
Thanks

This index is on the FB_ID column - not the column_key column.

The posted query plan indicates that it is scanning the non-clustered index [table1:ShipId] to satisfy the query.

Do you really have an index on column_key ?

If you have no index on column_key it is not very surprising that an index scan is used.

/SG

Stefan_G SSCertifiable Points: 6609 More actions · Answer 4

I think it would be a good idea to post the actual definition of Table1 including all its indexes.

Please do not edit the generated script in any way, just post what SSMS generates.

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 5

I just did the Update statistics and retrieve the ten million rows is two minutes, so I am happy now

Thanks to all of you

Grant Fritchey SSC Guru Points: 398952 More actions · Answer 6

ricardo_chicas (4/29/2010)
I just did the Update statistics and retrieve the ten million rows is two minutes, so I am happy now
Thanks to all of you

Ah, so the FULL SCAN did the trick? Good to know.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WayneS SSC Guru Points: 95461 More actions · Answer 7

Grant Fritchey (4/29/2010)
ricardo_chicas (4/29/2010)
I just did the Update statistics and retrieve the ten million rows is two minutes, so I am happy now
Thanks to all of you
Ah, so the FULL SCAN did the trick? Good to know.

Yes, it's very nice to get this feedback. Two minutes to retrieve all that data seems reasonable.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Place a job to update statistics nightly (if you dont have) so that you wont get this kind of problem next time

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)