2

I had a hard time coming up with an appropriate title and it might be hard for me to explain the issue, but I'll try to be as clear as possible.

I have a dynamic query that is pulling assets based on their asset title or one of many "identifiers" that are attached to that asset. Every asset as a "snapshot" ID that is tied to a asset identifier snapshot table, that is then tied to a version table that holds the actual value. That relationship looks like this:

SELECT
    *
FROM
    vwAsset AS Asset
        INNER JOIN dbo.tblAssetIdentifierSnapshot ON tblAssetIdentifierSnapshot.AssetSnapshotId = Asset.AssetSnapshotId
            INNER JOIN dbo.tblAssetIdentifierVersion AS AssetIdentifierVersion ON AssetIdentifierVersion.AssetIdentifierVersionId = tblAssetIdentifierSnapshot.AssetIdentifierVersionId

The value in the tblAssetIdentifierVersion table is what I'm trying to get at. Here is what the full query looks like:

And here's a pastebin.

If I run this query without the two highlighted joins and remove the OR, it runs instantly. But adding these joins makes the query run for 25+ seconds.

When I run this through Sentry Plan Explorer, it shows that some 274K+ rows are being searched for each join, before it finally filters down to the 137 it returns.

This is the query columns that sentry plan explorer shows:

I'm not an expert, so take this with a grain of salt, but every looks mostly okay. I don't think anything can be done about index scans, right?

Is there any way to filter down the left joins more without knowing the snapshot ID or the versions that are tied to the asset?


Here's the execution plan: https://www.brentozar.com/pastetheplan/?id=HJtsYtGLe

2

It looks like this query is an example of a problematic use of OR according to SQLCAT:

However, when the conditions on each side of the OR operator are evaluating different tables, problems can arise:

WHERE a.col1 = @val1 OR b.col2 = @val2

The pattern represented by this query predicate is problematic. Breaking down the query into two steps produces a significantly “cheaper” plan. Note that in this pattern, there are two tables involved; on each side of the OR operator is a condition that applies to different tables.

In the query plan with the additional tables filtered out, SQL Server is able to filter out irrelevant rows quickly. Also the filter condition can be applied directly to the table.

In the query plan with the OR clause, SQL Server filters out unnecessary rows only at the very end of the plan, after doing millions of unnecessary index seeks. This is the query that you reported as being slow in your question.

In the query plan with the query that I suggested (UNION instead of OR), SQL Server is more able to directly apply the filters and less overall work is done. There is not a filtering step at the end that eliminates hundreds of thousands of rows.

Note that this approach may not work as well as it usually does because you're querying a view and the logic behind that view may be expensive. It is also true that rewriting the OR as a UNION will result in duplicated code. The best way around this is to use a derived table. Unfortunately, this may not be a good option for you depending on the possible filter conditions that end users can add. If you can use a derived table, one rewrite is as follows:

SELECT DISTINCT TOP 250
     Asset.AssetVersionId
    ,Asset.AssetSnapshotId
    ,Asset.AssetId
    ,Asset.Title
    ,Product.Title AS 'Model'
    ,Brand.Title AS 'Brand'
    ,Customer.Title AS 'Customer'
    ,Customer.CustomerId
    ,CustomerLocation.ServiceLocationId
    ,Asset.AssetSnapshotId
    ,ISNULL(AssetIdentifier_TagNumber.Value, '') AS 'TagNumber'
    ,CustomerLocation.Title AS 'CustomerLocation'
    ,Asset.TagLocationId
    ,ISNULL(TagLocation.TagLocationNumber, '') AS 'TagLocationTitle'
    ,TagLocation.RBIRequired
    ,Asset.AssetApprovalStatusId
    ,AssetManufacturerTag.SerialNumber
    ,Asset.FullModelNumber
    ,Customer.LocalTitle
FROM
    (
    SELECT 
        Asset.AssetVersionId
        ,Asset.AssetSnapshotId
        ,Asset.AssetId
        ,Asset.Title    
        ,Asset.AssetSnapshotId
        ,Asset.TagLocationId
        ,Asset.AssetApprovalStatusId
        ,Asset.FullModelNumber
        ,Asset.ProductId
        ,Asset.CustomerId
        ,Asset.CustomerLocationId
    FROM dbo.vwAsset AS Asset
    WHERE Asset.Title LIKE '%E29%'

    UNION ALL

    SELECT 
        Asset.AssetVersionId
        ,Asset.AssetSnapshotId
        ,Asset.AssetId
        ,Asset.Title    
        ,Asset.AssetSnapshotId
        ,Asset.TagLocationId
        ,Asset.AssetApprovalStatusId
        ,Asset.FullModelNumber
        ,Asset.ProductId
        ,Asset.CustomerId
        ,Asset.CustomerLocationId
        FROM dbo.vwAsset AS Asset
        INNER JOIN dbo.tblAssetIdentifierSnapshot AS AssetIdentifierSnapshot ON AssetIdentifierSnapshot.AssetSnapshotId = Asset.AssetSnapshotId
        INNER JOIN dbo.tblAssetIdentifierVersion AS AssetIdentifierVersion ON AssetIdentifierVersion.AssetIdentifierVersionId = AssetIdentifierSnapshot.AssetIdentifierVersionId
        WHERE Asset.Title NOT LIKE '%E29%' AND AssetIdentifierVersion.Value LIKE '%E29%'
    ) AS Asset    
    INNER JOIN dbo.tblProduct AS Product ON Product.ProductId = Asset.ProductId
    INNER JOIN dbo.tblBrand AS Brand ON Brand.BrandId = Product.BrandId
    INNER JOIN dbo.tblCustomer AS Customer ON Customer.CustomerId = Asset.CustomerId
    INNER JOIN dbo.tblCustomerLocation AS CustomerLocation ON CustomerLocation.CustomerLocationId = Asset.CustomerLocationId
    INNER JOIN dbo.vwUserServiceLocation AS UserServiceLocation ON UserServiceLocation.ServiceLocationId = CustomerLocation.ServiceLocationId
    LEFT JOIN dbo.vwAssetIdentifier AS AssetIdentifier_TagNumber ON AssetIdentifier_TagNumber.AssetSnapshotId = Asset.AssetSnapshotId AND AssetIdentifier_TagNumber.Title = 'Tag Number'
    LEFT JOIN dbo.tblTagLocation AS TagLocation ON TagLocation.TagLocationId = Asset.TagLocationId
    INNER JOIN dbo.tblAssetManufacturerTag AS AssetManufacturerTag ON AssetManufacturerTag.AssetId = Asset.AssetId
    LEFT JOIN dbo.tblStatementOfWork AS SOW ON SOW.AssetId = Asset.AssetId
    LEFT JOIN dbo.tblWorkOrder AS WorkOrder ON WorkOrder.WorkOrderId = SOW.WorkOrderId
    LEFT JOIN dbo.tblProject AS Project ON Project.ProjectId = WorkOrder.ProjectId      
WHERE
    1 = 1
    AND UserServiceLocation.OwnerId = '1A07E201-D832-465D-A1B1-10500A20E16A';

If you can't use a derived table and need to duplicate the full query logic that's okay. To get the TOP behavior that you're looking for you mean need to define the whole query as a CTE and do the TOP filtering as a final step.

As a final note, carefully consider if DISTINCT TOP 250 without an ORDER BY clause is what you want. With that query you will just get an arbitrary 250 rows. If you are returning data to an end user there may be a more meaningful way to order or limit the results.

  • Hi Joe. Thanks a lot for the advice. I will start testing your suggestions and see where it takes me. I've also grabbed the execution plan. It's available here, if you want to take a look: brentozar.com/pastetheplan/?id=HJtsYtGLe – ernest Jan 10 '17 at 16:22
  • Sure. Here it is. I commented out the two joins and the OR, because it relied on the joins of course. brentozar.com/pastetheplan/?id=SkWOE9fUx – ernest Jan 10 '17 at 17:06
  • I tried the UNION advice and it worked really well. It went from 25-30 seconds to just 1 second. That was just the initial test. I'll have to try other examples of course, since like I mentioned, this is a dynamic query. But it looks promising. However, I would like to avoid this way if possible because I'm essentially having to create my query two times and that makes it large and cumbersome. But knowing that the OR was essentially the problem, is there anything we can infer from that? – ernest Jan 10 '17 at 19:50
  • Yeah, of course. He's the new plan: brentozar.com/pastetheplan/?id=By_87TzIl – ernest Jan 10 '17 at 20:26
  • 1
    @ernest I cleaned up my answer a bit and added a reference. It's possible for you to implement the same TOP filtering with the UNION method. Hopefully this approach is able to get you the performance that you need. Otherwise I'm out of ideas. – Joe Obbish Jan 12 '17 at 5:46
2

I don't see any way in the immediate query to assist performance. You have indices on the requested columns and it has to do a full scan to figure out all of the data.

There are two solutions I see, both take architecture changes.

  1. Full Text Search
  2. Processing and mapping tables with foreign key implementation.

If you are able to create a mapping table and a key column on the tblAssetIdentifierVersion and tblAssetIdentifierSnapshot table, you could have a process that runs that helps map to the table. This causes processing time that would typically be done in the select to be done in the update as it does not resolve the like operator in the where clause. You'll have to weigh whether this is a good option for your database or Full Text Search is a better idea or if it's best to be left alone.

Example of the mapping table:

DECLARE @m_people TABLE
    (
        M_Job_ID INT IDENTITY(1 , 1)  
        ,M_JOB VARCHAR(255)
    )

DECLARE @people TABLE
    (
        Name VARCHAR(255)
        ,JOB VARCHAR(255)
        ,Job_ID INT 
    )


INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Timmy' , 'Data Slave' , NULL)

INSERT  INTO @people
        (Name , JOB , Job_ID)
VALUES
        ('Joann' , 'Manager' , NULL)    

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Data Slave')          

INSERT  INTO @m_people
        (M_JOB)
VALUES
        ('Manager')         

UPDATE  @people
SET Job_ID = M_job_id
FROM @m_people
WHERE JOB LIKE '%' + m_job + '%'

SELECT * FROM @people
SELECT * FROM @m_people

Note: Due to variable tables, the objects due not exist in the sys.objects table so there is no obvious foreign key constraint declares. The FK would be Job_ID and M_Job_ID if that is not obvious.

  • Hi Shaulinator. I tried making a mapping table first, but that didn't pan out. It took just as long. I'll try the full text search next and see where that gets me. Thanks a lot for the advice. – ernest Jan 9 '17 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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