CODEWITHSUNDEEP
selectcountperformance
feronovak
feronovak

Reputation: 2697

COUNT(*) bottlenecking whole SQL SELECT

I have quite complex SELECT with 7 inner joins and at least 13 variable WHERE conditions.

I have create stored procedure which controls this select and conditions for search. With every search I need to get number of total records for selected conditions, so I have duplicated SELECT and changed it to SELECT COUNT(*) with the same joins and conditions.

Without select COUNT(*) within stored procedure it searches within 260 000 records in 5ms.

With select COUNT(*) within stored procedure it searches withing 260 000 records in 122ms

Is there a way to speed up this process? I need to get this total count, the question is whether there is an option to make it faster.

Upvotes: 1

Views: 123

Answers (2)

Dilum Ranatunga
Dilum Ranatunga

Reputation: 13374

The reason the query without COUNT is faster is probably because the database doesn't need to actually find all results to return the first 20.

I don't think there is a good general solution to the problem... but perhaps you can limit the COUNT to also some large limit... say 1001? If you ever get 1001, then your UI could say "More than a 1000 results..." and allow user to constrain the query further?

Upvotes: 1

Niclas Lindqvist
Niclas Lindqvist

Reputation: 1432

I don't know why count would be slow in your query, but for a quick fix perhaps you're better of counting the returned rows in your program instead. If you're returning the result to an array or a list of some sort, you could use ".Count()" (if it was .NET that is..)

Edit: Make sure all joined tables are using primary keys and indexed columns for your query.

Upvotes: 0

Related Questions