DataSource Indexes Vs. DataAdapter Select Statements

[Bob Day]

Using vs 2003, vb.net, sql msde

Using Server Explorer, Design Table design a table with only a Primary
Index.
Example with 4 columns (but no index created for them, except primary key).
Table PINS
Primary Key, autoincrement
IX_PINS, string
IX_Role_Number, String
IX_Outbound_Calling_Status, string

Using Component Designer, I will Create a DataAdatapter with a 'covering'
select statement. I can selectively drop % into the statement below to
'ignore' any one of the three @variables.
Example of where section:
WHERE (PINS.fld_IX_PIN LIKE @PIN) AND (PINS.fld_IX_Role_Number LIKE
@ROLE_NUMBER) AND (PINS.fid_IX_Outbound_Calling_Status_Number LIKE
@OUTBOUND_CALLING_STATUS_NUMBER)

Here is my question. The above works fine with no indexes defined in Design
Table (i.e. the underlying datasource), but I suspect I am taking a
performance hit. When I design the table, how should the indexes be
designed to maximize the performance of the DataAdapter?

Scenario one: Design 3 different indexes, each one being on one indexed
column. This is easy to do, but I am not sure it helps the DataAdapter if
it uses multiple columns. But it seems the only possible option, because it
would allow me to define PIN as Unique Index, while the other two as not
unique.

Scenario two: Design 1 index with all index columns, starting with the
narrower columns first. This would amount to a 'covering' index (i.e
something like Outbound_Calling_StatusNumber+Role_Number+PIN). Logically,
this would be best for the DataAdapter (both covering), but doesn't seem
possible since I could not define PIN alone as a Unique index with the other
two not being unique.

Scenario three: Design as many indexes as possible, one each to match a
possible combinations of the 'covering' data adapter. Each index would have
multiple columns. For example, one index might be PIN, another PIN + Role,
another PIN+Role+Outbound Calling Status Number. This doesn't seem
practical since it would require predicting all possible combinations of the
use of the DataAdapater in advance.

Please advise.

Bob Day

 

[Kevin Sun [MS]]

How to add index is based on the business requirement.

In this case, if the three columns(IX_PINS, X_Role_Number,
IX_Outbound_Calling_Status) are always used together in your application,
you can simply create an index for the three columns. Otherwise, you may
need to create an index for each column, because, you could use only of the
column separately (in WHERE) in other statement

Also, if you add too many index for the columns, it will be fast to query
the data; however, the performance will be slow when inserting and updating
the data, becuase, the data engine has to update the index information when
changing one record.

Therefore, you need to decide how the index impacts the application based
on your scenario.

For more information, I suggest you to have a look at SQL Book Online.

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "Bob Day" <[email protected]>
| Subject: DataSource Indexes Vs. DataAdapter Select Statements
| Date: Thu, 9 Oct 2003 12:53:44 -0500
| Lines: 51
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: dhcp065-029-072-239.indy.rr.com 65.29.72.239
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63337
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Using vs 2003, vb.net, sql msde
|
| Using Server Explorer, Design Table design a table with only a Primary
| Index.
| Example with 4 columns (but no index created for them, except primary
key).
| Table PINS
| Primary Key, autoincrement
| IX_PINS, string
| IX_Role_Number, String
| IX_Outbound_Calling_Status, string
|
| Using Component Designer, I will Create a DataAdatapter with a 'covering'
| select statement. I can selectively drop % into the statement below to
| 'ignore' any one of the three @variables.
| Example of where section:
| WHERE (PINS.fld_IX_PIN LIKE @PIN) AND (PINS.fld_IX_Role_Number LIKE
| @ROLE_NUMBER) AND (PINS.fid_IX_Outbound_Calling_Status_Number LIKE
| @OUTBOUND_CALLING_STATUS_NUMBER)
|
| Here is my question. The above works fine with no indexes defined in
Design
| Table (i.e. the underlying datasource), but I suspect I am taking a
| performance hit. When I design the table, how should the indexes be
| designed to maximize the performance of the DataAdapter?
|
| Scenario one: Design 3 different indexes, each one being on one indexed
| column. This is easy to do, but I am not sure it helps the DataAdapter if
| it uses multiple columns. But it seems the only possible option, because
it
| would allow me to define PIN as Unique Index, while the other two as not
| unique.
|
| Scenario two: Design 1 index with all index columns, starting with the
| narrower columns first. This would amount to a 'covering' index (i.e
| something like Outbound_Calling_StatusNumber+Role_Number+PIN). Logically,
| this would be best for the DataAdapter (both covering), but doesn't seem
| possible since I could not define PIN alone as a Unique index with the
other
| two not being unique.
|
| Scenario three: Design as many indexes as possible, one each to match a
| possible combinations of the 'covering' data adapter. Each index would
have
| multiple columns. For example, one index might be PIN, another PIN +
Role,
| another PIN+Role+Outbound Calling Status Number. This doesn't seem
| practical since it would require predicting all possible combinations of
the
| use of the DataAdapater in advance.
|
| Please advise.
|
| Bob Day
|
|
|
|
|

 

[Bob Day]

Thanks for your help, but the point of a 'covering' select statement is that
the columns will be used in various combinations (in fact, I think that 3
columns would results in 9 possible combinations, such as value,%,%, value,
value1,% and so on). Adding each possible combination as an index to the
table (9 indexes) would probably not be good. Does adding 3 indexes, one
for each column, help in a select statement like we are talking about? I
would think 3 indexes is the best option.

Bob Day

 

[Kevin Sun [MS]]

Hi Bob,

Yes, the best option is to create an index for each column.

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| From: "Bob Day" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: Re: DataSource Indexes Vs. DataAdapter Select Statements
| Date: Thu, 16 Oct 2003 13:00:47 -0500
| Lines: 125
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: dhcp065-029-072-019.indy.rr.com 65.29.72.19
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:63788
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Thanks for your help, but the point of a 'covering' select statement is
that
| the columns will be used in various combinations (in fact, I think that 3
| columns would results in 9 possible combinations, such as value,%,%,
value,
| value1,% and so on). Adding each possible combination as an index to the
| table (9 indexes) would probably not be good. Does adding 3 indexes, one
| for each column, help in a select statement like we are talking about? I
| would think 3 indexes is the best option.
|
| Bob Day
|
| | > How to add index is based on the business requirement.
| >
| > In this case, if the three columns(IX_PINS, X_Role_Number,
| > IX_Outbound_Calling_Status) are always used together in your
application,
| > you can simply create an index for the three columns. Otherwise, you may
| > need to create an index for each column, because, you could use only of
| the
| > column separately (in WHERE) in other statement
| >
| > Also, if you add too many index for the columns, it will be fast to
query
| > the data; however, the performance will be slow when inserting and
| updating
| > the data, becuase, the data engine has to update the index information
| when
| > changing one record.
| >
| > Therefore, you need to decide how the index impacts the application
based
| > on your scenario.
| >
| > For more information, I suggest you to have a look at SQL Book Online.
| >
| > Sincerely,
| >
| > Kevin
| > Microsoft Support
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| > Get Secure! - www.microsoft.com/security
| >
| > --------------------
| > | From: "Bob Day" <[email protected]>
| > | Subject: DataSource Indexes Vs. DataAdapter Select Statements
| > | Date: Thu, 9 Oct 2003 12:53:44 -0500
| > | Lines: 51
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <[email protected]>
| > | Newsgroups: microsoft.public.dotnet.framework.adonet
| > | NNTP-Posting-Host: dhcp065-029-072-239.indy.rr.com 65.29.72.239
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.framework.adonet:63337
| > | X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
| > |
| > | Using vs 2003, vb.net, sql msde
| > |
| > | Using Server Explorer, Design Table design a table with only a Primary
| > | Index.
| > | Example with 4 columns (but no index created for them, except primary
| > key).
| > | Table PINS
| > | Primary Key, autoincrement
| > | IX_PINS, string
| > | IX_Role_Number, String
| > | IX_Outbound_Calling_Status, string
| > |
| > | Using Component Designer, I will Create a DataAdatapter with a
| 'covering'
| > | select statement. I can selectively drop % into the statement below
to
| > | 'ignore' any one of the three @variables.
| > | Example of where section:
| > | WHERE (PINS.fld_IX_PIN LIKE @PIN) AND (PINS.fld_IX_Role_Number LIKE
| > | @ROLE_NUMBER) AND (PINS.fid_IX_Outbound_Calling_Status_Number LIKE
| > | @OUTBOUND_CALLING_STATUS_NUMBER)
| > |
| > | Here is my question. The above works fine with no indexes defined in
| > Design
| > | Table (i.e. the underlying datasource), but I suspect I am taking a
| > | performance hit. When I design the table, how should the indexes be
| > | designed to maximize the performance of the DataAdapter?
| > |
| > | Scenario one: Design 3 different indexes, each one being on one
indexed
| > | column. This is easy to do, but I am not sure it helps the
DataAdapter
| if
| > | it uses multiple columns. But it seems the only possible option,
| because
| > it
| > | would allow me to define PIN as Unique Index, while the other two as
not
| > | unique.
| > |
| > | Scenario two: Design 1 index with all index columns, starting with the
| > | narrower columns first. This would amount to a 'covering' index (i.e
| > | something like Outbound_Calling_StatusNumber+Role_Number+PIN).
| Logically,
| > | this would be best for the DataAdapter (both covering), but doesn't
seem
| > | possible since I could not define PIN alone as a Unique index with the
| > other
| > | two not being unique.
| > |
| > | Scenario three: Design as many indexes as possible, one each to
match a
| > | possible combinations of the 'covering' data adapter. Each index
would
| > have
| > | multiple columns. For example, one index might be PIN, another PIN +
| > Role,
| > | another PIN+Role+Outbound Calling Status Number. This doesn't seem
| > | practical since it would require predicting all possible combinations
of
| > the
| > | use of the DataAdapater in advance.
| > |
| > | Please advise.
| > |
| > | Bob Day
| > |
| > |
| > |
| > |
| > |
| >
|
|
|