Post reply

Accessing lookup tables

  • December 1, 2016 at 5:27 am

    #1915366

    joeroshan (12/1/2016)

    Bruce-12445 (11/30/2016)

    Joe - it might be better to do both inner joins on the asset to attribute table which returns a match of 14,538 records a 40,355 difference from returning all 54,893 records using a left join which is the total row count.

    I can't post the actual .sqlplan file as I change the db and table names because this is a public forum. send your email address and I will send them. the files I supplied are the execution plan exported to xml and saved as a txt file with only the name changes so they should reflect the true plan.

    Hi, altering to inner join is up to you based on your requirement. I understand that you cannot share actual plan. But if you can look at operation costing 60% and look at estimated rows vs actual rows to see your statistics are alright.

    And as Grant said, if your frequent queries have additional filters, then you can focus more on them.

    I'm going to echo Joe. It's hard to suggest what to do on this query since, it's not (if I understand everything) the query that's actually going to do the work. We need to focus on the query that's ultimately doing the work.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 1, 2016 at 5:33 pm

    #1915603

    The difference in the estimate and actual recordcount is the result of the combination of the inner join and the AND clause in the table join if that makes sense. The WHERE clause filters the main set of records being queried on the inner join. If I use a left join I would return the same recordcount with NULL values in the second table. There's probably a lot smarter way of approaching this but that evades me... obviously, but I am learning.

    This is the base table view for asset attributes that will link to a GIS table on a different server and database to create a spatial view I have about 70 to do. All records are required in the join to the table in the GIS db. At the moment all records will be updated into a GIS table for all the mapping applications to use both internally and the web. The update should be each night.

    I appreciate your assistance.

    Bruce.

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

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