Post reply

Query Taking Exponentially Longer to Complete

  • August 15, 2019 at 8:12 pm

    Go to Answer

    #3671682

    I'm attempting to run a query that captures all documents ordered of a certain type. The query can do the top 1000 rows almost immediately, however I tried to pull the top 10000 and this gave an odd timing for how many rows were returned. The full query took 19.5 minutes, however the first 3k rows were populated in 12 seconds, the next 1.5k took 4 minutes, the next 2k took 8 minutes, and the final 3.5k took an additional 11.5 minutes. (The output was constantly being updated and I would check how many rows were available to view at random intervals, these times/# of rows is not exact, but pretty close.)

    Does this sound like a reasonable time/row? The system we have in place to routinely run this query to store/update the information has been timing out for the last month due to how long the full query is taking, the query I was running had a sub-query contained within it so I was attempting to isolate the issue.

    Any help or guidance would be greatly appreciated! I have found where the error logs are located however don't know how to view the contents of the error log (I was expecting a txt file). Thanks in advance.

  • August 16, 2019 at 9:10 pm

    #3671977

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 17, 2019 at 4:46 am

    #3671994

    What's in the data you're returning?  Any LOBS?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2019 at 3:40 pm

    #3672153

    It would be helpful if you could supply the query the DDL for the table(s) definitions and any indexes on the table(s).

    Also the execution plan of the query when selecting 1,000 and 10,000 rows.

  • August 19, 2019 at 4:39 pm

    #3672369

    I do not think any of the data fields would be LOBS, There are just a massive amount of rows in the full DocumentLog table. Last week I ran the query without constraining the number of output rows and it took 26 minutes to populate the 22,000 rows that met the search criteria. The full query requires other information to be left joined to it, but querying those tables is very fast regardless of length. To modify the query to spit out 10000 rows I just switched the 1000 to 10000.

    SELECT TOP (1000) [Id]
          ,[Company]
          ,[OrderDateUtc]
          ,[RequestedBy]
          ,[Stage]
          ,[Title]
          ,[Status]
          ,[EncompassId]
      FROM [Staging].[elliedb].[DocumentLog]  where title = 'Credit Report' and orderdateutc is not null

     

  • August 19, 2019 at 4:48 pm

    #3672373

    As far as Data Types being Returned, just strings of varying lengths and the Date-timestamp, which is why I am surprised at the length of time required to execute this.

  • August 19, 2019 at 5:02 pm

    #3672382

    It could be something as simple as there are more than 1000 near the top of the table that satisfies the query and the 10,000th row is a long way down.

    From the information you've provided, it's really impossible for anyone to say why it's taking so long. How many rows are on the table in total? Does the execution plan change when you change to 10000 rows?

  • August 19, 2019 at 5:27 pm

    #3672385

    Is there an index on Title column? If not, create one and see if it helps. You may want to add OrderDateUtc to index too.

    --Vadim R.

  • August 19, 2019 at 5:47 pm

    #3672407

    The Table has just under 2 million rows (1958393), and takes 32:08 to fully query every row and column. Does SQL go through the table line by line and cross reference the contents to the criteria I have specified?

    I don't quite understand what you're asking with the 'Does the execution plan change?' I just switched Top 1000 for Top 10000.

  • August 19, 2019 at 5:56 pm

    #3672409

    It doesn't look like there are indexes on either of those columns, I'll look into that! How would that effect the data currently in the table?

  • August 19, 2019 at 6:38 pm

    #3672426

    awalton wrote:

    The Table has just under 2 million rows (1958393), and takes 32:08 to fully query every row and column. Does SQL go through the table line by line and cross reference the contents to the criteria I have specified?

    I don't quite understand what you're asking with the 'Does the execution plan change?' I just switched Top 1000 for Top 10000.

    Changing the n in the TOP(n) can change the execution plan and can make the difference between it using an index and not using an index.

    Have you looked at the execution plans of the two queries to see if there is any difference?

    You'll have to post the DDL for the table with all the indexes and the full query with execution plans for anyone to have a better idea of what's going on.

  • August 19, 2019 at 7:01 pm

    #3672434

    Here is the full Query I need to run. I am also attaching the execution plan for the full query, I will look into if there is a functional difference between selecting top 1000 v 10000 except there being more rows to look into. (There was a 'Missing Index' message when I looked at the execution plan, so I am assuming that is the issue. If that is the case do I need to create the index every time I run the query or does creating the index permanently alter the table?)

    SELECT
    a.OrderDateUtc AS Credit_Ordered_Date,
    a.Stage AS CreditOrder_Stage,
    a.RequestedBy,
    a.Status AS CreditOrder_Status,
    a.Company,
    b.LoanNumber,
    b.BaseLoanAmount,
    b.OrganizationCode,
    b.MilestoneCurrentName,
    b.MilestoneCurrentDateUtc,
    b.InverviewerName,
    b.NmlsLoanOriginatorId AS NMLS_ID,
    c.LoanFolder

    FROM Staging.elliedb.DocumentLog a
    LEFT JOIN Staging.elliedb.Loan b ON a.EncompassId=b.EncompassId
    LEFT JOIN Staging.elliedb.LoanMetadata c ON a.EncompassId=c.EncompassId

    WHERE a.Title='Credit Report' AND a.OrderDateUTC IS NOT NULL AND c.LoanFolder NOT IN ('(Archive)','(Trash)','Training','Employee Loans')

    ORDER BY b.LoanNumber
    Attachments:
    You must be logged in to view attached files.

  • August 19, 2019 at 7:07 pm

    #3672446

    Assuming you run your query in SSMS, go to the Query menu and activate "Include Actual Execution Plan" item. Then run your query. Upon completion you'll see a new tab in the results titled "Execution Plan". Run it for both TOP 1000 and TOP 10000 and compare results. That is what Jonathan been asking.

    --Vadim R.

  • August 19, 2019 at 7:08 pm

    #3672448

    And here is the DDL (I think)

    Attachments:
    You must be logged in to view attached files.

  • August 19, 2019 at 7:13 pm

    #3672450

    SQL Server thinks the suggested index will make a big difference. There might be more than one index needed but you'll need to look at the "missing indexes" section of the query plan xml to see that. You should just create the index and see how the performance is improved and check if any more indexes are needed. Index creation is permanent. To remove an index you have to issue the DROP INDEX statement:

    I think this index will be better than the one suggested by SQL Server for your query:

    CREATE INDEX IX_DocumentLog_1 ON elliedb.DocumentLog(Title, OrderDateUtc) 
    INCLUDE (Company, RequestedBy, Stage, Status, EncompassId)

    To drop the index:

    DROP INDEX IX_DocumentLog_1 ON elliedb.DocumentLog

Viewing 15 posts - 1 through 15 (of 29 total)

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