question

Hemant avatar image
Hemant asked

Row Count function taking time

There is one simple query to return the count of rows which is taking ages to complete but once I remove the count function it returns the result in no time. Any idea about this strange behavior ? Query - BEGIN TRAN SELECT COUNT(LocationAddress) FROM WHMWarehouseDetails AS WHMWD WITH ( NOLOCK ) INNER JOIN MF_Storage AS MFS WITH ( NOLOCK ) ON WHMWD.Office = MFS.Office AND WHMWD.WHCode = MFS.Warehouse AND WHMWD.LocationAddress = MFS.RID AND WHMWD.Type = MFS.Type WHERE ( ISNULL(WHMWD.IsCancelled, 0) = 0 ) AND ( WHMWD.Office = 'PNLS;' ) AND ( WHMWD.WHCode = '11' ) AND ( WHMWD.IsEnabled = 1 ) AND EXISTS ( SELECT 1 FROM mf_storage WITH ( NOLOCK ) WHERE office = 'PNLS;' AND warehouse = '11' AND ISNULL(RID, '') = ISNULL(WHMWD.Code, '') ) ROLLBACK
t-sqlrow-counts
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

And why have you wrapped that query in `BEGIN TRAN/ROLLBACK`?
2 Likes 2 ·
How many rows?
1 Like 1 ·
please post the query plan and some idea on row counts in the tables referenced. Are statistics up to date and what columns are indexed?
1 Like 1 ·
The query returns some 8000 rows.
0 Likes 0 ·
I think there is no need to wrap the query in BEGIN TRAN/ROLLBACK.
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
Magnus is on the right track here - look at the execution plans to see what the difference is. When you do a `COUNT(column)`, the function will return the number of rows where column is not null, whereas removing the count and doing a `SELECT *`, removes the need for that check. Depending on the indexes, statistics, etc. for that column, you will get differing execution times.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think the statistics on one of the tables were not updated.Now it's working fine :-) I say thank u all for your valuable comments!!!
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Why do you use WITH NOLOCK? Regarding the overall performance: Have you tried to do Ctrl+L in Sql Server Management Studio instead of running the query? That would give you an estimated execution plan where you can look for clues, like table scans. Do you have an index on the LocationAddress column? What indexes do you have?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I checked with the query execution plan.There is nothing suspicious as such.My concern is that if I run the same query with out count function it works fine.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.