Indexing fields for JOINS and WHERE

Question

Post reply

Indexing fields for JOINS and WHERE

Viewing 9 posts - 16 through 23 (of 23 total)

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

Digs SSCertifiable Points: 7333 More actions · Answer 1

I used the field [Deleted] as either 0 or -1 (Type BIT)

I dont delete rows, I tag a row as deleted by TRUE or -1

Therefore [Deleted] is used a lot on its own or in combination with WHERE clauses. Its a major field, ie is data deleted or not ??

At the moment I have individual index's on fields that at WHERE on, I have no composite indexs

Should I remove index on [Deleted] ??

OR SHOULD I

Or should the composite be like this (Deleted,Name)

- Ref example above, as deleted field is very important.

Index Scan

Index Seek

Whats the diff..

Guess I better goog that !!

UPDATE..

Reading here...http://www.sql-server-performance.com/faq/graphical_query_pla_%20optimizer%20_p1.aspx

SCANS are better than SEEKS

Using the idex example above ie #tbl

A: ix_tbl : resulted in two SEEKS and one SCAN

B: ix_tbl_1 & ix_tbl_2 : Resulted in 3 SCANS

And as my database is like B:, I guess I am ok !

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

Digs (2/3/2010)

Or should the composite be like this (Deleted,Name)

- Ref example above, as deleted field is very important.

Yup, think that's the better approach.

Index Scan

Index Seek

Whats the diff..

Guess I better goog that !!

http://sqlinthewild.co.za/index.php/2007/11/15/execution-plan-operations-scans-and-seeks/

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Digs (2/3/2010)

SCANS are better than SEEKS

You might want to read again.

A scan is like taking the telephone directory and reading every single page to find the phone number for Mr M Brown.

A seek is like taking the telephone directory and going directly to the entry for Mr M Brown, not reading any more than absolutely necessary to find the entry for M Brown.

You still think a scan (a complete read of every single row on every single page) is better?

And as my database is like B:, I guess I am ok !

Umm.....

You have a LOT more reading to do. A lot. Start with this series (3 parts) - http://qa.sqlservercentral.com/articles/Indexing/68439/

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

Digs SSCertifiable Points: 7333 More actions · Answer 4

Digs

SSCertifiable

Points: 7333

February 3, 2010 at 2:10 pm

#1113192

Thanks those links are great !

Q: Can I have a combinatio of index on a table and let SQL server find the best one..??

Like (ref example above)

(Name)

(Deleted)

(Deleted,Name)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

You can, but it's a waste of space, may degrade insert/update/delete performance, increases the time of maintenance jobs. really not a good idea.

The index that you have just on deleted is redundant. Drop it. The one on name is only necessary if you filter on name and not on deleted.

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

Digs SSCertifiable Points: 7333 More actions · Answer 6

Digs

SSCertifiable

Points: 7333

February 3, 2010 at 2:41 pm

#1113221

But I do filter on Deleted

LIKE

SELECT NAME FROM <tbl>

WHERE Deleted = 0

This get all names that are not deleted.

I use deleted in every SELECT query.

As i said I dont delete records, I tag them as TO BE DELETED physically in the future..

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Digs (2/3/2010)

But I do filter on Deleted

LIKE

SELECT NAME FROM <tbl>

WHERE Deleted = 0

That's fine. There's an index with a leading column of 'deleted', so any query that filters on deleted alone will use that.

A query can use an index if the query filters on a left-based subset of the index key.

As i said I dont delete records, I tag them as TO BE DELETED physically in the future..

Yup, got that the first time you said it.

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Ohhh... be careful. I just went through a similar thing a month ago. Having an index with a leading column having only two possible states will sure speed things up for SELECTs but INSERTs are going to suffer... a lot. Check out the number of "reads" that such an index will cause when you do a single row insert.

The fix, in my case, was to alter the order of columns on the index (admittedly, not always possible) so it still worked well for the SELECTs but without having a two-state column as the lead column. I forget how many rows were being inserted every 60 seconds or so but the number of reads for those inserts were up around 330,000 every time it ran. When I changed the index, the reads dropped to 12 with the same number of inserts being done. It also kept the index from becoming fragged to the point of being useless in only 9 hours.

--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)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Indeed.

Usually I'd suggest the following indexes for something like this.

Name, Deleted

<another searched field>, Deleted

..

Deleted

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