none
How can I speed up join

    Question

  • I have a query pulling data from several tables, based on date criteria and file number. This is populating a dataset within my Reporting Services report (rdl).

    I get 82000 + rows in 28 seconds. Not great, but not horrible either.

    I am joining to another table based on those criteria to extract data for the report, which are calculated fields in the report. The data consists of identifiers which are then to be used as filters for specific computations. There are four such fields in the report, none of which are optional.

    The table containing the identifiers has 2000000+ rows of data, which when joined to the existing data (82000 + rows) creates a mess. If the query completes at all, it takes over 26 minutes, and when used in the report causes an Out of Memory exception.

    I've tried CTE's, Temp Tables, and subqueries to pull in the data (as well as joining the data directly and computing in Reporting sServices). T-SQL is not my strongest suit, so I could use your help!

    I ran an execution plan, which tells me that a disproportionate amount of time is spent on table scans. The Temp Tables are indexed, but may not be correctly utilizing the index.

    Please help me return to some semblance of sanity.....

    Wednesday, September 25, 2013 9:13 PM

Answers

  • I ran an execution plan, which tells me that a disproportionate amount of time is spent on table scans.

    Hello Gary,

    Seems there is no suitable index for your query; you should create one for speed up performance.

    In the execution plan view, is there a "missing index" shown up? You could use that hint to create the index. Or you could use the "Database Engine Tuning Advisor" (in SSMS menu "Tools") to analyze the query and get some suggestions for indexes & statistics. 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 26, 2013 4:59 AM
  • You should be able to resolve this with either of following:

    1. add appropriate index based on columns used in query. which is not often based way to resolve performance issue

    2. re-evaluate join condition to match existing indexes

    If you need assistance in suggesting based index or rearranging query to utilize existing index then I will need to see query and index detail on related tables.

    DTA can also help you but it will guide you for particular query and does not consider all scenario which is often becomes overall bottleneck if implemented all recommendation from DTA.


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.


    Thursday, September 26, 2013 5:18 AM

All replies

  • I ran an execution plan, which tells me that a disproportionate amount of time is spent on table scans.

    Hello Gary,

    Seems there is no suitable index for your query; you should create one for speed up performance.

    In the execution plan view, is there a "missing index" shown up? You could use that hint to create the index. Or you could use the "Database Engine Tuning Advisor" (in SSMS menu "Tools") to analyze the query and get some suggestions for indexes & statistics. 


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 26, 2013 4:59 AM
  • You should be able to resolve this with either of following:

    1. add appropriate index based on columns used in query. which is not often based way to resolve performance issue

    2. re-evaluate join condition to match existing indexes

    If you need assistance in suggesting based index or rearranging query to utilize existing index then I will need to see query and index detail on related tables.

    DTA can also help you but it will guide you for particular query and does not consider all scenario which is often becomes overall bottleneck if implemented all recommendation from DTA.


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.


    Thursday, September 26, 2013 5:18 AM