Improving count(*) performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Improving count(*) performance

Author

Topic

jinchuriki
Starting Member

1 Post

Posted - 2008-02-24 : 21:19:19

Hi guys,

I'm facing a performance issue with select count(*) table_name when performing pagination to my results. My actualy query joins 4 tables with proper PK and FK contrainsts and indices applied.

select top 100 * from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')

The query above takes 200 ms to 600 ms to execute.

select count(*) from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')

The query above takes 2000ms to 4000 ms to run.

The total records that fulfill the where clause is approximately 5000 to 6000 records. I checked the execution plan but found that the server is actually utilizing the indices with operations like index scan and index seek.

Are there any other things that i can do to improve the counting of total records?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-24 : 22:11:03

Put the Query in Database tuning Advisor.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-25 : 19:46:00

There is not much you can do without changing the nature of the query. I am fairly sure you will gain nothing by using the between operator but it is about the only thing that might work without changing the count.
If you know things about your data you might be able to use exists instead of outer join(assuming only 1 child per row) but no guarantees it will go faster. The other thing is to make sure your index stats are up to date so it's picking the right table to drive the query

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-02-26 : 01:03:08

quote:
Originally posted by jinchuriki

Hi guys,

I'm facing a performance issue with select count(*) table_name when performing pagination to my results. My actualy query joins 4 tables with proper PK and FK contrainsts and indices applied.

select top 100 * from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')

The query above takes 200 ms to 600 ms to execute.

select count(*) from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')

The query above takes 2000ms to 4000 ms to run.

The total records that fulfill the where clause is approximately 5000 to 6000 records. I checked the execution plan but found that the server is actually utilizing the indices with operations like index scan and index seek.

Are there any other things that i can do to improve the counting of total records?

Thanks.

Try this...

1. Don't use count(*) as it scan the whole table. Instead get the desired columns in Select.

2. Use BETWEEN instead of >,<,>=,=< operators.

3. Use Convert function once. In your case, Convert function converting the date for every row which may/is constly. So do it in this way...

Declare @FromDate DateTime
Declare @ToDate DateTime

Set @FromDate = <Convert the FROM date here>
Set @ToDate = <Convert the TO date here>

then u can just use these variables in your query.

Thanks,

Mahesh

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-26 : 01:07:57

Hi,

try like this

DECLARE @fromdate datetime,
@Todate Datetime
SELECT @fromdate = '2008-02-20 16:16:31.000',
@Todate = '2008-02-26 09:47:02.000'

SELECT @fromdate = dateadd(day,datediff(day,0,@fromdate),0),
@Todate = dateadd(day,datediff(day,0,@Todate),0)

select count(table1.id) from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=>= @fromdate and datetime<= @Todate

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 01:59:39

quote:
Originally posted by mahesh_bote

Try this...

1. Don't use count(*) as it scan the whole table. Instead get the desired columns in Select.

2. Use BETWEEN instead of >,<,>=,=< operators.

3. Use Convert function once. In your case, Convert function converting the date for every row which may/is constly. So do it in this way...

Declare @FromDate DateTime
Declare @ToDate DateTime

Set @FromDate = <Convert the FROM date here>
Set @ToDate = <Convert the TO date here>

then u can just use these variables in your query.

Thanks,

Mahesh

1. Bullshit. Count(*) does what it says in the documentation. If you want to count some other non-null things then fine, but count(*) gives you the count of all rows in the query in the most efficient way.

2. Maybe - check the plan. I think it will equate to the same plan.

3. It's confusing because he has a column called datetime as well as a type. He is converting a constant string to a date once.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources