Add a very simple index that is missing on the Generated Market Segment tables.

This index would reduce system resource consumption.

Below is an example query captured with the index:

select @TESTSEGMENTID = MD.TESTSEGMENTID,
@SEGMENTID = MD.SEGMENTID from MKTSEGMENTATIONDATA_90C0B288_6FD8_45B8_AE64_5AB89B2EA21C MD where MD.DONORID=@CONSTITUENTID


CREATE INDEX IX_MKTSEGMENTATIONDATA_90C0B288 ON MKTSEGMENTATIONDATA_90C0B288_6FD8_45B8_AE64_5AB89B2EA21C(DONORID);

 

This improvement would help everyone on the system as it reduces database system resource consumption (IO and CPU)

  • Guest
  • Mar 31 2017
  • Reviewed: Voting Open
Blackbaud CRM / System Admin Utilities
Organization Name (Please enter full organization name) Father Flanagan's Boys' Home
Reported Version 4.0
  • Attach files
  • John Schenck commented
    March 31, 2017 20:28

    This is my find / suggestion.  Our environment has over 6000 market segment tables, some with millions of rows in them.  I've captured queries that are querying against the DONORID column, but there is no index for that column.  My suggestion is to create a single column index on the DONORID column along with the other indexes that are defined as part of these MKTSEGMENTATIONDATA_<GUID> table creation - simple add for a big query cost reduction for any WHERE DONORID =  type queries

    Attachments Open full size

Related ideas