slow query in sql server

[napii21]

Hi all,

i have this sql query:

 select t.TransID AS ID,t.TransItem AS summary, (select count(a.LogID) from TblAuditTrail a where a.TransID=t.TransID AND (a.UserID = 'idamanadm') AND (DatePart(Year, DateCreated) = 2013))as total   from TblTrans t where t.TransID in (2,3,4) order by t.TransID

it will take 20 sec to return the results. How can i make the query faster?

Thank you.

 

[Herman]

I would hope the T-SQL compiler would work its magic on the peculiar in-select subquery, but maybe it's not. In which case that subquery would be run once for every record retrieved, which will cause slowdowns on a large table. The query's execution plan should tell you more. Try to rewrite the query as a LEFT JOIN + GROUP BY, see if it makes a difference.

I *think* this should be functionally equivalent:

SELECT t.TransID AS ID,
       t.TransItem AS summary,
       COUNT(a.LogID) AS total
FROM TblTrans t
LEFT JOIN TblAuditTrail a ON a.TransID = t.TransID
                         AND a.UserID = 'idamanadm'
                         AND DATEPART(Year, a.DateCreated) = 2013
WHERE t.TransID IN (2, 3, 4)
GROUP BY t.TransID, t.TransItem
ORDER BY t.TransID

 

[jmcilhinney]

First things first, how about we write it such that it's easy to read therefore easy to understand what it does?

select t.TransID AS ID,
       t.TransItem AS summary,
       (
           select count(a.LogID)
           from TblAuditTrail a
           where a.TransID=t.TransID
           AND (a.UserID = 'idamanadm')
           AND (DatePart(Year, DateCreated) = 2013)
       ) as total
from TblTrans t
where t.TransID in (2,3,4)
order by t.TransID

That's a fairly horrible query. If you want to join two tables then you should actually use a join:

SELECT t.TransID AS ID, t.TransItem AS summary, COUNT(a.LogID) AS total
FROM TblTrans t INNER JOIN TblAuditTrail at
ON t.TransID = at.TransID
WHERE t.TransID IN (2, 3, 4)
AND at.UserID = 'idamandam'
AND DATEPART(year, at.DateCreated) = 2013
GROUP BY t.TransID, t.TransItem
ORDER BY t.TransID

That's untested but, by eye, it seems to do what you're trying to achieve. The fact that there's no subquery should speed things up.

 

[Herman]

I don't think an inner join is what he wants, the original query will return records from table t even if the count in table a is null. See above for left join.

 

[Herman]

An index on those tables might also make the query much faster if either of them are a bit large.

 

[napii21]

Herman's query is functionally equivalent and he is right, i want to return records even if the count is null, but sadly the query also will take long time to return results.

Please note that the table has more than 4 millions records. i know indexing will improve its performance, but after a while, it will be slow again because the records amount will keep growing.

any other idea? stored procedure?

 

[jmcilhinney]

Maybe you just need to accept that querying a lot of records is going to take some time. Instead of asking us whether a stored procedure will help, why don't you try it for yourself?

 

[napii21]

Maybe you just need to accept that querying a lot of records is going to take some time. Instead of asking us whether a stored procedure will help, why don't you try it for yourself?

yeah,you are right. i will try it for myself.

but if anyone had experienced something like this and found a way to improve the performance,maybe can share it with me.

Thanks.

 

[Herman]

What you could do that would probably save time on the query is query a broader data set and do the aggregate, grouping and sorting locally. How much time it will save depends on your server's vs your client's speed, and how efficiently you do the above. For example you could query:

SELECT t.TransID AS ID,
       t.TransItem AS summary,
       a.LogId
FROM TblTrans t
LEFT JOIN TblAuditTrail a ON a.TransID = t.TransID
                         AND a.UserID = 'idamanadm'
                         AND DATEPART(Year, a.DateCreated) = 2013
WHERE t.TransID IN (2, 3, 4)

And then re-query the results to group, count, and sort them. It really all depends on where the bottleneck is, if the server is super busy, or if you are missing critical indexes on some tables, etc.. Just from indexing properly I've reduced some queries from 45 seconds to 4-5.

 

[napii21]

What you could do that would probably save time on the query is query a broader data set and do the aggregate, grouping and sorting locally. How much time it will save depends on your server's vs your client's speed, and how efficiently you do the above. For example you could query:

SELECT t.TransID AS ID,
       t.TransItem AS summary,
       a.LogId
FROM TblTrans t
LEFT JOIN TblAuditTrail a ON a.TransID = t.TransID
                         AND a.UserID = 'idamanadm'
                         AND DATEPART(Year, a.DateCreated) = 2013
WHERE t.TransID IN (2, 3, 4)

And then re-query the results to group, count, and sort them. It really all depends on where the bottleneck is, if the server is super busy, or if you are missing critical indexes on some tables, etc.. Just from indexing properly I've reduced some queries from 45 seconds to 4-5.

thanks herman. i will try that method. thanks a lot.

 

[IanRyder]

Hi,

Here is a bit of extra advice for you. The one overriding thing in all the examples shown so far is the use of this function in either Joins or Where clauses:-

DATEPART(Year, a.DateCreated) = 2013

What you may not realise is that any use of a Function in Where Clauses or Joins negates the use of any Index's that may have been applied to the field in question. This therefore, and especially on 4M records, means you are always going to struggle to get any significant speed improvements with this table structure.

My advice then would be twofold:-

1) Make sure you have added index's to the TransID and UserID fields.
2) Change your Table structure to add a Year Field which is populated at the same time the DateCreated field is populated through whichever means you are using to enter your Data. If you do this then you will need to run a simple Update query on the existing Data Table to add the current years from the existing DateCreated field but make sure you DO NOT have an index on this field when you do the update since it will take a gazillion years to run. Once done, add an Index to this field and then change your query to use this field instead of using the function. That way, indexes will be used as efficiently as possible and you should see a permanent significant improvement in the running of the query.

Hope that helps.

Cheers,

Ian