Speed up (not like) query

Question

Post reply

Speed up (not like) query

Catcha

SSC Eights!

Points: 886
More actions
March 24, 2009 at 9:31 pm

#155462

Hi All,
Is there any tricks to improve this query not like query, like this?
where deal_no not like 'IMGATD%'
I tried using
left (deal_no,6) <> 'IMGATD'
but the performance was degraded.
Any helps will be grateful.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

HI,

1) By Increase the where condition

You want increase the speed of response?

Any how show you statement

ARUN SAS

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

Pls post the whole query, table structure and some sample data.

"Keep Trying"

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 3

Catcha (3/24/2009)
Hi All,
Is there any tricks to improve this query not like query, like this?
where deal_no not like 'IMGATD%'
I tried using
left (deal_no,6) <> 'IMGATD'
but the performance was degraded.
Any helps will be grateful.
Thanks

do not use any functions in where clause like (left, right, min, max, etc..). Use first method.

FYI: http://www.sql-server-performance.com/tips/t_sql_where_p1.aspx

Catcha SSC Eights! Points: 886 More actions · Answer 4

[Code]

select top 100 u58.deal_type_cde , ucb.*

from u58_deal_hist u58

join ucb_exp_hist ucb on u58.deal_id = ucb.deal_id

where ucb.batch_id = 297326

and u58.batch_id = 297325

and ucb.deal_id like 'IMGATD%'

[/code]

There are indexes in batch_id for u58_deal_hist and ucb_exp_hist.

Mando2 SSChasing Mays Points: 609 More actions · Answer 5

There are indexes in batch_id for u58_deal_hist and ucb_exp_hist.

This is a very generic, open-ended question; pretty hard to make any reasonable suggestions with so few details.

Having said that, here is what I would try as a first step - extend the index on ucb_exp_hist to include deal_id column if it is not already there.

As an FYI - as a second step, I would try converting the NOT LIKE WHERE clause into a NOT IN with a co-related sub-query.

NOTE: I am not suggesting that this is the best thing to do - you would still need to follow engineering principles, measure and validate every suggestion on your own.

HTH.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 6

Posting the table structure would help.

You also ask NOT LIKE at the start, but LIKE in the example query.

Is there any order by in the query?

Greg E

Wesley Norton SSCrazy Points: 2827 More actions · Answer 7

I have run into a similar problem in the past, if you can get rid of the "not" in the statement, it should speed up considerably. I realize it takes some doing, but changing just that could make a huge difference. The way I went about doing it was to delete the rows after I had inserted them into the table (I was populating a table at the time). Another way, probably better, is to put all the values you do want in an "in" statement. Hope you are having the same problem I had and that this helps.

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 8

Catcha (3/25/2009)
[Code]
select top 100 u58.deal_type_cde , ucb.*
from u58_deal_hist u58
join ucb_exp_hist ucb on u58.deal_id = ucb.deal_id
where ucb.batch_id = 297326
and u58.batch_id = 297325
and ucb.deal_id like 'IMGATD%'
[/code]
There are indexes in batch_id for u58_deal_hist and ucb_exp_hist.

I'm also curious as to the offset here of 1

where ucb.batch_id = 297326

and u58.batch_id = 297325

I would try reversing the order of the tables, especially if the ubc table is smaller.

Are there any indexes on the tables?

And EXISTS / NOT EXISTS types of queries can be very good to try.

http://msdn.microsoft.com/en-us/library/ms188336.aspx

Greg E

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

I doubt whether reversing the tables could help a lot here.. anyway its worth a try. Mostly SQL will come up with a good execution plan irrespective of the order of tables.

"Keep Trying"

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 10

Catcha (3/25/2009)
[Code]
select top 100 u58.deal_type_cde , ucb.*
from u58_deal_hist u58
join ucb_exp_hist ucb on u58.deal_id = ucb.deal_id
where ucb.batch_id = 297326
and u58.batch_id = 297325
and ucb.deal_id like 'IMGATD%'
[/code]
There are indexes in batch_id for u58_deal_hist and ucb_exp_hist.

Few remarks – First of all you are using top 100 without specifying which records you want to see. If later you’ll need to show the next 100 records, there won’t be a way to achive that. Consider adding order by clause so you’ll have control on what records will be sent to the client.

Without knowing the query plan, the table’s structure (including indexes), number of records in each table and selectivity of the columns batch_id and deal_id (on both tables) no one will be able to give you a solid advice on how to improve the performance. We could all just give you an educated (?) guess about indexes that could be used and might improve performance.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/