-2

I have a problem with a slow running query.

I have analysed the query with the sql server profiler but I can't seem to find a good solution.

I did find why it is running slow. If ask the full dataset it returns me 150 rows in 10s, if I added the sums and calculations it returns me 18 rows but it takes 8min.

In SQL Server Profiler I found out that the full dataset does about 1 000 000 reads and the sum query about 82 000 000. But I don't know how or why he is getting some many records. Because the full dataset is the sub select from the sum query.

the below query is an example not the real query(real query also has union all's in the sub query but the sum query is basically the same)

SELECT Sum(x) * 0.1,
       Sum(y),
       a
FROM   (SELECT x,
               y
        FROM   tx
        INNER JOIN ty ON tx.a = ty.a 
        WHERE  x = 1 --this returns 150 rows in 10s 
       ) sub 
GROUP  BY a -- sum returns 18row 8min 

Can somebody help me with some more test I have to do or a solution a could test? First Piece execute plan Second Piece Third Piece

4
  • We are not scared by big queries, you can show us the monster :)
    – Serpiton
    Jun 20, 2014 at 10:30
  • Can you post an actual (not estimated) .sqlplan file somewhere? A screen shot is a good start but still largely useless for analysis. Jun 20, 2014 at 11:00
  • 2
    That posted example is not even valid syntax. You cannot know that example represents the performance issues the real query if it does not even run.
    – paparazzo
    Jun 20, 2014 at 12:06
  • 3
    In the future, please don't copy the question and re-ask it here, just flag or vote to close as off-topic > belongs on another site on the stack exchange network > dba.se. Jun 20, 2014 at 15:02

2 Answers 2

0

It appears you have several Clustered Index Scans on the far right that take up the bulk of the query cost.

You will have to give some more detail about these (I am struggling to read it), basically, we need to check if we can't put better indexes on it.

Also, if you are using SQL Server 2012 or 2014, with Enterprise or Dev edition, try putting a non-clustered columnstore index on the aggregation column(s).

1
  • Thanks looked at the scans and there was a bad link and a bad where clause. Now the sum query runs under 10s
    – user41366
    Jun 20, 2014 at 12:27
0

It looks to me like you don't need the derived table. Is there any reason you couldn't write it like this:

    SELECT Sum(x) * 0.1,
       Sum(y),
       a
    FROM tx
        INNER JOIN ty ON tx.a = ty.a 
    WHERE  x = 1
    GROUP  BY a

This probably won't solve all of your performance issues, but if you set statistics IO on and look at the logical reads, and the execution plan, it may be able to help you narrow down the bottlenecks and suggest if/where you need an index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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