Row Count function taking time

Question

question

Hemant asked May 17, '11

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-sql row-counts

6

10 |1200

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

ThomasRushton ♦♦ commented · May 17, 2011 at 01:23 AM

And why have you wrapped that query in `BEGIN TRAN/ROLLBACK`?

2 ·

Kev Riley ♦♦ commented · May 17, 2011 at 12:31 AM

How many rows?

1 ·

Fatherjack ♦♦ commented · May 17, 2011 at 01:14 AM

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 ·

Hemant commented · May 17, 2011 at 01:30 AM

The query returns some 8000 rows.

0 ·

Hemant commented · May 17, 2011 at 02:35 AM

I think there is no need to wrap the query in BEGIN TRAN/ROLLBACK.

0 ·

Kev Riley ♦♦ commented · May 17, 2011 at 02:36 AM

@Hemant - you are right, you cannot rollback a select - there is nothing to rollback!

0 ·

Answer 1 · 2011-05-17T01:34:52Z

Kev Riley answered May 17, '11

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

10 |1200

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

Hemant commented · May 17, 2011 at 02:25 AM

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 ·

Answer 2 · 2011-05-17T00:30:35Z

Magnus Ahlkvist answered May 17, '11

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

10 |1200

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

Hemant commented · May 17, 2011 at 01:41 AM

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 ·

question