SQL 2000 - Performance suggestions

Question

SQL 2000 - Performance suggestions

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

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

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

Exactly... (and great explanation, by the way) I know that and you know that and even some other folks know that. And, you're absolutely correct. But lot's of folks don't get what you said...

The optimizer will not use indexes if it needs to access more than a very low percentage of the total data in the table.

...because lots of folks (oh, the arguments I've had a work because of the statement above) think that what you're saying is that if you access more than about 15% of the rows for any given set of columns, an index will not be used. That's why I keep coughing up the Covering Index example to show that an index can be used and that they are mistaking what you said. You're not necessarily saying it wrong... it's just that people are taking it wrong. Heck, even I took it wrong when you first posted it way back when

I will say that your explanation is a great one for not ever allowing the use of SELECT * but I also think a bit more explanation (like you did in your reply to my friendly "goading") of what is meant by "total data in the table" means just to keep people from thinking that correct indexing is a waste of time.

--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)

schleep SSChampion Points: 12583 More actions · Answer 2

Jeff/Guru,

I only use SELECT * with [NOT] EXISTS. Should I be replacing the * with some indexed column (preferably one in the WHERE clause)?

P

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

>>I only use SELECT * with [NOT] EXISTS. Should I be replacing the * with some indexed column (preferably one in the WHERE clause)?

An emphatic NO to that one for several reasons:

1) You may pick an indexed column that is less efficient to chew through than some other indexed column.

2) The index may be dropped in the future.

3) A more efficient index could be created in the future.

Leave the * in for EXISTS checks to give the optimizer the chance to do the most efficient thing always regardless of schema changes.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

You don't like the idea of SELECT 1 instead?

--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)

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 5

What about selecting a literal:

IF EXISTS (SELECT 1 FROM dbo.MyTable WHERE MyCol = SomeValue)

...

I can't see any difference in the execution plan for that compared to SELECT * but it looks like it *should* be marginally more efficient (and index/column independent). Thoughts?

Regards,

Jacob

Jacob Luebbers SSCarpal Tunnel Points: 4834 More actions · Answer 6

Whoops - Jeff beat me to it. Ignore...

Regards,

Jacob

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Nope... glad to see someone else thinks the same thing, Jacob.

--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)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

Actually, I do like the idea of select CONSTANT for exists checks. But that wasn't offered as one of the 2 possibilities the poster gave. 🙂

I saw somewhere on the web (a Frank Kalis post IIRC) that select * will cause the columns to be fetched when the query plan is created by the optimizer, which will be a "teensy" bit slower than select 1. This may have been short-circuited in sql2005 though.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service