0

Scenario

I have a middleware software product called Pro2SQL by Progress, copying data from a Progress database to an SQL Server real-time. Pro2SQL manages the SQL Server schema through the middleware. The only indexes created on the SQL Server side by Pro2SQL are Unique, Non-Clustered row ID indexes. I can completely ignore this row ID in my queries, since it is exclusively used for Pro2SQL concurrency.

Some of the tables contain 100+ million rows, so I need to create indexes. In every table copied from Progress to SQL Server, there is a "domain" field. This field has one of two values of three characters each. Let's call them 'ABC' or 'XYZ'. On the Progress database side, this domain field is a part of the index.

Question

Does adding the domain field as a part of a multi-field index on the SQL Server side add any value, or could it make things worse? For example, I may have the following multi-field, Non-Clustered Index on a table (note these fields are what makes the rows in the Progress db 's table unique):

  • isb_eu_nbr (very selective)
  • isb_part (very selective)
  • isb_serial (very selective)
  • isb_ref (moderately selective)
  • isb_domain (extremely non-selective, only 'ABC' or 'XYZ', the source of my Question)

This table will have about 500,000+ rows.

Usage

All queries will be read only queries. There will be no Inserts, Updates, or Deletes. Those are handled by the Pro2SQL middleware. In all of my queries, I'm going to have to add in the WHERE clause the following:

/* WHERE <tableAbbr>_domain = 'ABC' */

/* Example */
WHERE
    /* Conditions 1 through N */
    AND isb_domain = 'ABC'

/* Another Example, with JOINed tables */
WHERE
    /* Conditions 1 through N */
    AND isb_domain = 'ABC'
    AND ls_domain = 'ABC'
    AND pt_domain = 'ABC'

Other Considerations

The vendor, Progress, indicates that since there are real-time updates constantly happening throughout the business day, all queries have to be non-locking. So they actually suggest(!!!) NOLOCK. We've enabled SNAPSHOT on the database, so all queries connected directly to the server will be run with SET TRANSATION ISOLATION LEVEL SNAPSHOT, and all queries run through linked servers will be run with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED (accepting the side effects).

UPDATED

I've found that I can create Clustered, Non-Unique indexes on the SQL Server tables. Also, with suggestions below, I can possibly create Non-Unique, Non-Clustered Filtered Indexes, where the Filter would be on field <abc>_domain within every table. So my question has morphed into:

Should I create Non-Unique, Clustered Indexes that match the Progress DB's fields for uniqueness, minus the <abc>_domain fields, and then create Non-Unique, Non-Clustered Filtered Indexes that do include the <abc>_domain field in each table?

I do understand that a hard yes/no isn't always feasible without actually seeing the database and the data. I'll still do performance testing. I'm looking more for a "I would start with..." answer.

2 Answers 2

1

The only indexes created on the SQL Server side by Pro2SQL are Unique, Non-Clustered row ID indexes.

No clustered indexes?...off to a bad start already.

In every table copied from Progress to SQL Server, there is a "domain" field. This field has one of two values of three characters each. Let's call them 'ABC' or 'XYZ'. On the Progress database side, this domain field is a part of the index.

Does adding the domain field as a part of a multi-field index on the SQL Server side add any value, or could it make things worse?

Hard to say definitively without seeing the actual queries as they run now, and their execution plans before and after the change. Basically one needs to test to find out.

One thing I'd consider, since isb_domain is not very selective, if you're always only caring about ABC and never XYZ, try making filtered indexes instead. This would have a WHERE clause in the end of their definitions like CREATE NONCLUSTERED INDEX IX_YourIndexName ON TheTable (WhateverColumnsWereAlreadyBeingIndexed) WHERE isb_domain = 'ABC';. This offers a few benefits:

  1. It prevents you from mucking with the indexes the vendor app is generating which potentially will be overwritten or cause issues in the vendor app, by decoupling it into a separate index.

  2. It doesn't end up being a completely redundant index of the index being generated by the vendor app, making it a little easier for the database system to maintain, since it only includes half the data.

  3. It may be a candidate for your queries and improve I/O performance by having to scan or seek against less records.

6
  • Good point about the filtered indexes! The vendor app doesn't create any indexes, with the exception of one Unique, Non-Clustered Index per table, for their row ID. I completely ignore their row ID field, since it is not actual data and only used for concurrency with the middleware Pro2SQL software.
    – HardCode
    Commented Jan 29 at 17:02
  • Ok, I re-read the vendor documentation. It seems that I'm not restricted from creating Clustered Indexes; I just cannot create Unique Indexes, "as that may break replication due to the order in which the triggers fire." I think I'll create Clustered Indexes based on the Progress Indexes for row uniqueness. Can I make a Clustered Index a Filtered Index? I haven't used them before.
    – HardCode
    Commented Jan 29 at 17:07
  • @HardCode "Can I make a Clustered Index a Filtered Index?" - Nope, since the clustered index defines how the entire table's data is logically sorted. But lacking clustered indexes on a table is typically a bad thing. So I think you should likely create a sensible clustered index tailored to your queries too - depending on what's the most common type of querying you're doing.
    – J.D.
    Commented Jan 29 at 17:14
  • 2
    If you're creating a clustered index anyway, and your queries will always filter on the domain column, then yes, at that point I'd include it in the clustered index definition instead of making 2 indexes.
    – J.D.
    Commented Jan 29 at 18:01
  • 1
    A non-selective leading key column (of a multi-col index) is effectively a combination of every possible filtered index on that column. So yes if that column is always filtered then it could make sense to put it first even if it's not very selective. We have some indexes with a bit field first, because we filter either 0 or 1 in most cases, so this gets us a filtered index on both sides. Commented Jan 31 at 0:55
1

in general

If queries regularly search across all of those columns, then yes, it would be beneficial to have them all in the same index, so all of the search predicates could be applied as early on in the plan as possible.

Were you to create a nonclustered index that doesn't have some of the columns being searched on in it, the optimizer would have a choice between:

  1. scanning all of the rows in your clustered index or heap and applying those filters, or
  2. seeking to some of the rows in your nonclustered index and performing a lookup back to your clustered index (key lookup) or heap (RID lookup).

The lookup can be used to retrieve columns required elsewhere in your query, like the select list, or to apply additional filtering from columns in your where clause.

The optimizer is a bit fickle in these cost-based choices, and plans may change, or be reused, in detrimental ways. Since this is a rather small number of columns, I'd probably go the safer route and have them all available in a single index for those searches.

Of course #1, any additional columns in the select list may need to be accounted for as included (not key) columns in your nonclustered index as well, but there aren't any example queries provided to make that determination.

Of course #2, if your search queries use the leading three selective columns to produce reliably small result sets you will have far fewer long-term concerns around lookups or plan reuse.

Of course #3, just because values are selective does not mean they will be searched in selective ways, or combinations. As an example, you could have a totally unique set of datetime values in a table, but if someone searches from 1900-01-01 through 9999-12-31, that is not a very selective range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.