to retrive count using stored procedure

Question

0.00/5 (No votes)

See more:

Hi,

i want to retreive the count from three colmuns using like operator.in my database i had 70 lakhs of record am using the stored procedure for column1,column2,column3 using like operator i want to get the count but it is taking 5 min to exicute the query,how to get the count within a min,and i created index for column1,column2 but i didn't created for column3

cretae procedure [dbo].[getcount]
(

@keyword varchar(50),
@totalCount int OUT
)
as begin

select @totalCount=count(*) from TableName where ((Column1 Like '%'+@keyword+'%') or(Column2Like '%'+@keyword+'%') or (Column3 Like '%'+@keyword+'%'))

end

Please tell me how to solve it

Posted 11-Jun-14 0:21am

Member 10226004

Add a Solution

Comments

phil.o 11-Jun-14 6:28am

If you want performance with your queries you have to make sure that every field involved in a JOIN or a WHERE clause is indexed.
And if you want to achieve even better performance just avoid SELECT * and select just the columns you need (uncluding the primary key).
There is no other 'magic' available.

Member 10226004 11-Jun-14 7:08am

how to write query for getting count instead * slecting definite columns

W∴ Balboos, GHB 11-Jun-14 7:18am

Here's an example you can apply to your query segemens:

Select count(dogs) from TheTable where dogs='corn'
Note how count refers to the column I'm counting

Member 10226004 11-Jun-14 7:21am

that is for single column but i need the count for multiple columns

Member 10272815 11-Jun-14 9:39am

Count(*) is actually faster than count(Column_Name), because SQL will just get a row count for the first, while the second it has to verify that Column_Name is not null before counting the row. So if you're already removing NULLs by your where clause, count(*) is the way to go.

Have you tried breaking your query up in to smaller pieces and seeing where the slow down is?

Try executing
select @totalCount=count(*) from TableName where (Column1 Like '%[YourTestKeywordHere]%')

select @totalCount=count(*) from TableName where (Column2 Like '%[YourTestKeywordHere]%')

select @totalCount=count(*) from TableName where (Column3 Like '%[YourTestKeywordHere]%')

individually and seeing how long they take.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vJay Yadav · Answer 1 · 2014-06-11T03:10:00

Solution 1

simply replace * with column name. It will reduce the time for executing the query.

SQL

cretae procedure [dbo].[getcount]
(
@keyword varchar(50),
)
as 
declare 
@totalCount int OUT
begin
select @totalCount=count(column1) + count(column2) + count(column3) from TableName where ((Column1 Like '%'+@keyword+'%') or(Column2Like '%'+@keyword+'%') or (Column3 Like '%'+@keyword+'%'))
 
end

Posted 11-Jun-14 3:10am

vJay Yadav

Comments

Member 10272815 11-Jun-14 9:44am

That will result in the count being up to three times greater than expected. For every row that at least one column matches the keyword it will count any of the three columns that are not NULL, not just the ones that match.