Improve Between performance

Question

Improve Between performance

cgreathouse

SSC Eights!

Points: 855
More actions
April 2, 2010 at 2:53 pm

#231376

I'm trying to find a way to improve the performance of a query that uses a between clause
Here's what the table being queried looks like...
CREATE TABLE [dbo].[EffectiveItems](
[ItemID] [int] NOT NULL, -- foreign key
[BEGIN] [int] NOT NULL,
[END] [int] NOT NULL,
[LOW] [int] NOT NULL,
[HIGH] [int] NOT NULL
)
This table has about 3 million rows and every row is unique. The LOW and HIGH columns are pretty selective (no more than 5 duplicate values). There are a lot of duplicates for the BEGIN, END and ItemID columns. This table is pretty much a read-only table. It gets updated about once every 3 months.
Here's the query I'm running...
select *
from EffectiveItems
where 982827279 between LOW and HIGH
This query takes about 300 ms and 12,000 Reads to complete. If I do a query like this...
select *
from EffectiveItems
where 982827273 = LOW
This query takes about 0 ms (doesn't even register) and 3 Reads to complete. I've tried clustered, non-clustered & covering indexes but all of the results are very similar.
What can be done to improve the performance the query using between?
Thanks!

Viewing 15 posts - 1 through 15 (of 31 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Please post your actual execution plans.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cgreathouse SSC Eights! Points: 855 More actions · Answer 2

OK, here it is

StmtText

--------

Parallelism(Gather Streams)

|--Clustered Index Seek(OBJECT:([Database].[dbo].[EffectiveItems].[IX_EffectiveItems]), SEEK:([Database].[dbo].[EffectiveItems].[LOW] <= [@1]), WHERE:([@2]<=[Database].[dbo].[EffectiveItems].[HIGH]) ORDERED FORWARD)

LutzM SSC Guru Points: 107049 More actions · Answer 3

Is there any change to have a known maximum range between LOW and HIGH?

If so, you could base your query on the LOW column to narrow down the number of columns to be checked against the actual HIGH value.

Something like

WHERE LOW >= 982827279

AND LOW < 982827279 +1000

AND HIGH <= 982827279

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

cgreathouse (4/2/2010)
OK, here it is
StmtText
--------
Parallelism(Gather Streams)
|--Clustered Index Seek(OBJECT:([Database].[dbo].[EffectiveItems].[IX_EffectiveItems]), SEEK:([Database].[dbo].[EffectiveItems].[LOW] <= [@1]), WHERE:([@2]<=[Database].[dbo].[EffectiveItems].[HIGH]) ORDERED FORWARD)

Please post the xml as shown by Gail in the post listed in my signature.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

lmu92 (4/2/2010)
Is there any change to have a known maximum range between LOW and HIGH?
If so, you could base your query on the LOW column to narrow down the number of columns to be checked against the actual HIGH value.
Something like
WHERE LOW >= 982827279
AND LOW < 982827279 +1000
AND HIGH <= 982827279

Wouldn't that just return 1 value?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cgreathouse SSC Eights! Points: 855 More actions · Answer 6

The 982827279 is just an example. The value changes and can be anywhere between 72 and 994039999

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Also, what are your indexes for that table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cgreathouse SSC Eights! Points: 855 More actions · Answer 8

Here's the plan in xml form

<?xml version="1.0" encoding="utf-16"?>

<Batch>

</OutputList>

</RunTimeInformation>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</EndRange>

</SeekPredicate>

</SeekPredicates>

</Identifier>

</ScalarOperator>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</Parallelism>

</RelOp>

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

cgreathouse SSC Eights! Points: 855 More actions · Answer 9

I've tried a number of different one. Here's what is currently being used

CREATE CLUSTERED INDEX [IX_EffectiveItems] ON [dbo].[EffectiveItems]

(

[LOW] ASC,

[HIGH] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

LutzM SSC Guru Points: 107049 More actions · Answer 10

CirquedeSQLeil (4/2/2010)
lmu92 (4/2/2010)
Is there any change to have a known maximum range between LOW and HIGH?
If so, you could base your query on the LOW column to narrow down the number of columns to be checked against the actual HIGH value.
Something like
WHERE LOW >= 982827279
AND LOW < 982827279 +1000
AND HIGH <= 982827279
Wouldn't that just return 1 value?

It depends. 😀 It also may return 1000 rows...

What I tried to do is setting an upper limit to use the clustered index more efficient.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

lmu92 (4/2/2010)
CirquedeSQLeil (4/2/2010)
lmu92 (4/2/2010)
Is there any change to have a known maximum range between LOW and HIGH?
If so, you could base your query on the LOW column to narrow down the number of columns to be checked against the actual HIGH value.
Something like
WHERE LOW >= 982827279
AND LOW < 982827279 +1000
AND HIGH <= 982827279
Wouldn't that just return 1 value?
It depends. 😀 It also may return 1000 rows...
What I tried to do is setting an upper limit to use the clustered index more efficient.

My concern is due to the ands

WHERE LOW >= 982827279

AND LOW < 982827279 +1000

AND HIGH <= 982827279

Since the High and Low are the same value - it won't matter what the middle And is doing so long as the Low is less than that end value

WHERE LOW >= 982827279

AND LOW < 982827279 +1000

AND HIGH <= 982827279

This essentially says High <= 982827279 <= LOW

But 982827279 should be > than the low value and < the high value.

Or LOW <= 982827279 <= HIGH

982827279 between LOW and HIGH.

Am I making sense - it is a flip-flop of the < >.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 12

CirquedeSQLeil (4/2/2010)
...
My concern is due to the ands
WHERE LOW >= 982827279
AND LOW < 982827279 +1000
AND HIGH <= 982827279
Since the High and Low are the same value - it won't matter what the middle And is doing so long as the Low is less than that end value
WHERE LOW >= 982827279
AND LOW < 982827279 +1000
AND HIGH <= 982827279
This essentially says High <= 982827279 <= LOW
But 982827279 should be > than the low value and < the high value.
Or LOW <= 982827279 <= HIGH
982827279 between LOW and HIGH.
Am I making sense - it is a flip-flop of the < >.

Oooopss!! Of course, you're right :blush:

Should have been

WHERE LOW <= 982827279

AND LOW > 982827279 -1000

AND HIGH >= 982827279

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

That looks better.

Thanks Lutz

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 14

Thank you Jason for detecting my simple but most relevant mistake.

Since my error rate increase dramatically at the moment (in another thread I just forgot that table variables where introduced in SS2K). I think I'm taking some time off now... It's 1:40AM over here anyway...

Good night out there. Wherever you are.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]