Select Count(*) Speed Help

19 May 2006 12:57 PM

Mattbooty

Hello,

I have a piece of 3rd party software that we recently upgraded to a new
version, the new version takes a long time to load up each order
because every time it loads the order entry module it runs a Select
Count(*) From Table (I discovered through a trace). I know this is bad
programming, and I told their support staff they should change this,
but the change will not be coming any time soon. Is there anything I
can do to the Table (indexes or anything) to improve performance on
this?

Thanks

19 May 2006 1:02 PM

Aaron Bertrand [SQL Server MVP]

Are you sure this is what is taking all of the time? What is the structure
of the table? Is there a clustered index?

Show quote

"Mattbooty" <mattbo***@mattbooty.com> wrote in message
news:1148043440.288605.127680@j55g2000cwa.googlegroups.com...
> Hello,
>
> I have a piece of 3rd party software that we recently upgraded to a new
> version, the new version takes a long time to load up each order
> because every time it loads the order entry module it runs a Select
> Count(*) From Table (I discovered through a trace). I know this is bad
> programming, and I told their support staff they should change this,
> but the change will not be coming any time soon. Is there anything I
> can do to the Table (indexes or anything) to improve performance on
> this?
>
> Thanks
>

19 May 2006 1:03 PM

Mattbooty

Sorry, have more information now that may make this post irrelevant, it
is a Select Count(*) From VIEW!!! Would creating any types of indexes
on the tables that the view is selecting from help at all?

Thanks

19 May 2006 1:31 PM

Andrew J. Kelly

Take a look at the query plan for the view and see where it might need
improvement. The view is simply a sql statement just like any other select
and can be tuned accordingly. But we have no way of telling without having
all the details.

--
Andrew J. Kelly SQL MVP

Show quote

"Mattbooty" <mattbo***@mattbooty.com> wrote in message
news:1148043809.662626.62050@i40g2000cwc.googlegroups.com...
> Sorry, have more information now that may make this post irrelevant, it
> is a Select Count(*) From VIEW!!! Would creating any types of indexes
> on the tables that the view is selecting from help at all?
>
> Thanks
>

19 May 2006 2:23 PM

Mattbooty

I got it speeded up now! I took Andrew's advise and ran with it. The
view itself wasn't very complex, but i selected count (*) on the actual
query that made up the view and removed tables from it until I found
the culprit. I added a new index on the join condition for this table
and now it is running in about 1/10th of the time.

Thanks for your suggestions!