Count(*) performance

[Mich]

Hey all,

I've been called to a client's site that is installing a "database independent" solution. Of course, that means more work for my client. They are running into tons of performance issues and asked me to fix them. Unfortunately, most of the issues are caused by this "database independence" as the app is making a lot of select * calls, loading the return set locally, then crunching the data as they see fit. I've been asked to modify SQL only and not the application which makes the calls.

One issue is the app sends THOUSANDS of count(*) calls. No kidding, a simple lookup of one customer generates 17 count(*) calls. I guess the app is attempting to some sort of check.

Long story short (too late), does anyone know how to optimize a query such as 'select count(*) from custprice'?

-If it ain't broke, break it and make it better.

 

[bborissov]

It the table has a primary key just add:
WHERE PK > SomeImpossibleValue
that will force Index seek.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Mich]

Problem is I cannot modify the SQL queries since they come from the application. I can only modify those objects defined on database.

-If it ain't broke, break it and make it better.

 

[gmmastros]

Are there any indexes on the table? If you're not sure...

[tt][blue]sp_helpindex 'custprice'[/blue][/tt]

If there are no indexes, then I encourage you to put indexes on the tables. This should speed up the execution.

Other than that.... you're probably stuck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Mich]

There are indexes on the table. However, how would an index increase the speed of a count(*)? Does a count read every byte of a record or just that the record exists?

-If it ain't broke, break it and make it better.