Not using Index

Question

Not using Index

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

psangeetha (9/22/2008)
Thanks for all your comments. I've changed the code to use between and the execution time has reduced. THe application team is currently happy with it now. If they come back again, I am planning on executing the query by forcing the index.
Thanks again.

Considering that BETWEEN is reevaluated by the optimizer as an AND similar to the original query in this thread, I'm really surprised. I think it may be more because you changed the operands to be in the correct order when you made the BETWEEN.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jeff Moden (9/22/2008)
Considering that BETWEEN is reevaluated by the optimizer as an AND similar to the original query in this thread, I'm really surprised. I think it may be more because you changed the operands to be in the correct order when you made the BETWEEN.

I wonder if anyone will notice that the query's now returning the opposite data. :hehe:

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

GilaMonster (9/23/2008)
Jeff Moden (9/22/2008)
Considering that BETWEEN is reevaluated by the optimizer as an AND similar to the original query in this thread, I'm really surprised. I think it may be more because you changed the operands to be in the correct order when you made the BETWEEN.
I wonder if anyone will notice that the query's now returning the opposite data. :hehe:

Yup I was thinking that myself, that perhaps I was missing something. Perhaps a NOT BETWEEN would have been a bit more appropriate.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 4

You mean this?

select * from sales where (sales_id = 1123013 AND sales_in_no between 199901 AND 199701)

This query returns no data. I suggested using between as it's more obvious that bounds allow no data.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 5

The OP specified this in the WHERE statement

sales_in_no >= 199901 AND sales_in_no <= 199701

Reordered that becomes

EDIT: modified due to GT LT tags getting stripped out.sales_in_no <= 199701 AND sales_in_no >= 199901

In Other words all rows not in between 199701 and 199901 OR

sales_in_no NOT BETWEEN 199701 AND 199901

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

newbieuser

SSCrazy Eights

Points: 9423

September 23, 2008 at 7:36 am

#875636

Yes, we actually wanted not between.

Thanks, all.

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Actually, the result of NOT BETWEEN and the previously stated pair of inequalities would be different.

1.) LTE 199701 AND GTE 199901 would produce no records, as it's not possible for any value to meet BOTH of those criteria. I used LTE and GTE to avoid having to figure out how to include the actual symbols in this post.

2.) BETWEEN is an INCLUSIVE situation, so asking for BETWEEN 199701 AND 199901 would include both ending values in the acceptable range, whereas NOT BETWEEN would thus EXCLUDE both values (and all points in between). Compared to the pair of inequalities, this is not quite the same thing, as the appearance is that the OP is seeking to have 199701 or lower OR 199901 or higher.

FYI...

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 8

Luke L (9/23/2008)
The OP specified this in the WHERE statement
sales_in_no >= 199901 AND sales_in_no <= 199701
Reordered that becomes
EDIT: modified due to GT LT tags getting stripped out.sales_in_no <= 199701 AND sales_in_no >= 199901
In Other words all rows not in between 199701 and 199901 OR
sales_in_no NOT BETWEEN 199701 AND 199901
-Luke.

The reordered statement is exactly the same as original - removes all data from criteria.

If you replace AND with OR, you have completely different expression and that would be equal to NOT BETWEEN.

sgmunson SSC Guru Points: 110640 More actions · Answer 9

Robert,

I stand by my post, as NOT BETWEEN 199701 and 199901 would never allow either of the stated values of 199701 or 199901 to meet the criteria, whereas simply placing an OR between the LTE and GTE ends up allowing both of those values as valid to meet the criteria. Thus your statement (the bold, underlined, italic part):

If you replace AND with OR, you have completely different expression and that would be equal to NOT BETWEEN.

is not correct. They are NOT the same thing.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 10

Robert,

Here's some simple code to demonstrate the results:

DECLARE @test-2 TABLE (

N int

PRIMARY KEY(N)

)

INSERT INTO @test-2

SELECT 199000 UNION ALL

SELECT 199700 UNION ALL

SELECT 199701 UNION ALL

SELECT 199702 UNION ALL

SELECT 199800 UNION ALL

SELECT 199825 UNION ALL

SELECT 199850 UNION ALL

SELECT 199875 UNION ALL

SELECT 199900 UNION ALL

SELECT 199901 UNION ALL

SELECT 199902

SELECT *

FROM @test-2

WHERE N NOT BETWEEN 199701 AND 199901

SELECT *

FROM @test-2

WHERE N = 199901

The results from this are as follows:

Result Set 1:

N

199000

199700

199902

Results Set 2:

N

199000

199700

199701

199901

199902

Note the additional two records in the second set.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)