look for the most efficient method

Question

Post reply

look for the most efficient method

samirca007

SSC-Addicted

Points: 417

November 22, 2019 at 4:04 pm

#3700048

Hello everyone ,

Who can explain to me please the difference between these two index creation syntaxes

the first creating indexes by putting all the columns in the same index

CREATE NONCLUSTERED INDEX [ix_manquant_GLOBBAL] ON [dbo].[Base_versions]
(
  Client_ID ASC,
  Product_ID,ASC,
   sor_ident  ASC

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO

or create it in a separate way

USE [RemonteeHenry]
GO
 
/****** Object:  Index [ix_Contract_ID]    Script Date: 22/11/2019 16:41:58 ******/


USE [RemonteeHenry]
GO

/****** Object:  Index [ix_Contract_ID]    Script Date: 22/11/2019 16:41:58 ******/
CREATE NONCLUSTERED INDEX [ix_Client_ID] ON [dbo].[Base_versions]
(
  Client_ID ASC,


 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO


  /****** Object:  Index [ix_Contract_ID]    Script Date: 22/11/2019 16:41:58 ******/
CREATE NONCLUSTERED INDEX [ix_sor_ident] ON [dbo].[Base_versions]
(
 sor_ident  ASC

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO

  CREATE NONCLUSTERED INDEX [ix_Product_ID] ON [dbo].[Base_versions]
(
Product_ID  ASC,

 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  GO

thank you for your feedback

Viewing 4 posts - 1 through 3 (of 3 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

I don't think anyone can give you a good answer unless we have your table definition and a copy of the query that you think needs these indexes . even then we would need to know data quantities. (and i'm guessing no-one on here has time to bulk up a test database)

there's also a technique you can try -

create the first index and run the query with an actual execution plan

then drop the index and put the individual indexes on and do the same thing

compare the times and execution costs, even better, look at the execution plan and see if making that index forces the plan in a certain way - look for scans and just keep trying... if it gets better then keep it... sorry, query tuning can really be trial and error sometimes

MVDBA

Lowell SSC Guru Points: 323504 More actions · Answer 2

i think the definitions of the index need to match the queries hitting the server. the multi column index is not equivalent to indexing three columns individually.

a single index on these columns:

Client_ID ASC,

Product_ID,ASC,

sor_ident ASC

would be very useful with a WHERE statement like

WHERE Client_ID = @clientId AND Product_ID = @productId AND sor_ident = @sorident.

it would not be useful if the leading column Client_ID was NOT included in the WHERE statement; the leading/first column is what the core indicator of whet her the index is useful.

if the queries hit the server with only a single column in the where statement, then those three separate indexes might be more useful;

if you can, add all four indexes in development, let them sit for a while, and look to see how often they are used for the index stats, or look at the missing index recommendations, and see if the indexes are already recommended, maybe with include columns.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

to go along with what the other folks have written...

The biggest difference is that it will not be possible to get index seeks on the 3 column index if the only criteria used is based on any combination of Product_ID and/or Sor_Ident because they are not the leading columns. The other thing to know is that having a non-clustered index on every column in a table is usually a complete was of time and resources. The obvious things are that every index will need action taken on it for every INSERT and DELETE. Updates will affect the indexes only if the key or included columns are affected.

I'll also state that the creation of indexes should not be treated in a willy nilly or even partially informed manner. Remember that all indexes except the leaf level of the clustered index are a duplication of data. That means more work for Inserts, Deletes, and some Updates, more work on the log file, more work for backups (and restores), more work for index maintenance (which is a whole 'nuther story that most people get wrong), more statistics updates, more page splits, and (sometimes much) more blocking (you're looking at the guy that brought Expedia.com to its knees about 12 or 13 years ago for about 3 minutes after I created an index).

There are times when consolidating indexes into a single composite index (like your first one) is a great idea, times when single column indexes are fine, times when both are justifiable, and much more.

The reason I'm telling you this is because there's a hell of a lot more to the question you asked than many people might think.

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