Post reply

Index Selection and Overreading of Rows

  • February 7, 2018 at 9:00 pm

    #410222

    Firstly, this is actually being run on Azure, but I couldn't find an Azure TSQL group.

    I have a rather complex query  that is being run - this question concerns only a smaill part of that query, which is

    FROM  common.contactRoleConnection
    INNER JOIN [common].contactRole AS RDO ON RDO.gidContactRoleId IN ( contactRoleConnection.gidContactRoleIdFrom, contactRoleConnection.gidContactRoleIdTo )

    WHERE contactRoleConnection.gidListItemIdConnectionStatus = 'C80367B5-45A4-49D1-A027-EE0C03D929BD'
        AND contactRoleConnection.chrRecordStatus = 'A'

    SELECT COUNT(*)
    FROM common.contactRoleConnection     returns 40292
    SELECT COUNT(*)
    FROM common.contactRoleConnection
    WHERE gidListItemIdConnectionStatus = 'C80367B5-45A4-49D1-A027-EE0C03D929BD'
       AND contactRoleConnection.chrRecordStatus = 'A';     returns 37732

    There are three relevent indexes on this table - they are: -

    /****** Object: Index [IX_contactRoleConnection_gidContactRoleIdFrom_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdTo]  Script Date: 8/02/2018 2:38:54 PM ******/
    CREATE NONCLUSTERED INDEX [IX_contactRoleConnection_gidContactRoleIdFrom_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdTo] ON [common].[contactRoleConnection]
    (
        [gidContactRoleIdFrom] ASC,
        [gidListItemIdConnectionStatus] ASC,
        [chrRecordStatus] ASC,
        [gidContactRoleIdTo] ASC
    )
    INCLUDE (     [gidContactRoleConnectionId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    GO

    /****** Object: Index [IX_contactRoleConnection_gidContactRoleIdTo_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdFrom]  Script Date: 8/02/2018 2:38:14 PM ******/
    CREATE NONCLUSTERED INDEX [IX_contactRoleConnection_gidContactRoleIdTo_gidListItemIdConnectionStatus_chrRecordStatus_gidContactRoleIdFrom] ON [common].[contactRoleConnection]
    (
        [gidContactRoleIdTo] ASC,
        [gidListItemIdConnectionStatus] ASC,
        [chrRecordStatus] ASC,
        [gidContactRoleIdFrom] ASC
    )
    INCLUDE (     [gidContactRoleConnectionId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    GO

    /****** Object: Index [IX_contactRoleConnection_gidListItemIdConnectionStatus_chrRecordStatus]  Script Date: 8/02/2018 2:58:54 PM ******/
    CREATE NONCLUSTERED INDEX [IX_contactRoleConnection_gidListItemIdConnectionStatus_chrRecordStatus] ON [common].[contactRoleConnection]
    (
        [gidListItemIdConnectionStatus] ASC,
        [chrRecordStatus] ASC
    )
    INCLUDE (     [gidContactRoleConnectionId],
        [gidContactRoleIdFrom],
        [gidContactRoleIdTo]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
    GO

    If I run the following query,
    SELECT gidContactRoleIdFrom,
       gidContactRoleIdTo
    FROM common.contactRoleConnection
    WHERE gidListItemIdConnectionStatus = 'C80367B5-45A4-49D1-A027-EE0C03D929BD'
       AND contactRoleConnection.chrRecordStatus = 'A';
    It will use the last index to return the records, with the stats as

    (Results from SQL Sentry Plan Exporer - sorry for the image, but it seemed the best way to get all details across.)

    If, however, I run the much more complex query , then most of the time, instead of using the third index, it will use the first two indexes, concatinate and then distinct sort the results and then inner join to the contactRole table.  This is fine, because it is virtually instantanious, so I have no complaints about this.  I say mostly, because occasionally, for no conceivable reason that I can see, it will switch to using the third index, which is probably what it should use because then it does not have to concatinate or distinct sort.  The problem then is that it seems to do an enourmous amount of reads, as per

    For some reason, it seems to be doing the read that it should do, over 21000 times.  And I have no idea why.  And this blows the query time out to about 12 minutes.
    And then, again for no discernable reason, at some point in time it will switch back to using the dual index seek with concatination and distinct sort, and the query time will return to a couple of seconds.  Mainly 'cos it only does that process once.
    If there is any further information that I should provide, let mwe know (apart from full schemas, as it is rather complex), but at this point, I'm still not fully sure what the question should be, apart from what the hell is going on, and what should I do?

  • February 7, 2018 at 10:29 pm

    #1978980

    Actually folks, don't worry about this one.  I didn't figure out why it was happenning, but I managed to get around it by splitting the complex query up into segments and using temporary tables.

Viewing 2 posts - 1 through 1 (of 1 total)

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