How to improve the performance when select get few records from multi lakhs records in sql

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have nearly 3 million records in a table. when i select the records from using the below query it takes atleast 45 secs.

SQL

  select a.user_Id,b.name,a.paid_date,
 (select count(user_Id) from tbl_payment_Master 
where user_Id=a.user_Id  and paid_date=a.paid_date group by paid_date) as noofpaymnt,
(select count(user_Id) from tbl_payment_Master 
where user_Id=a.user_Id  and month(paid_Date)=MONTH(a.paid_date) and year(paid_Date)=year(a.paid_date)) as noofpaymonth 
from tbl_payment_master as a 
inner join tbl_user_registration as b on a.user_id=b.user_id 
where a.premium_amount!=0 and a.user_id=@user_id

How could be improve the performance.

Posted 1-Oct-15 23:43pm

ManojanbU

Updated 2-Oct-15 0:36am

v3

Add a Solution

Comments

Patrice T 2-Oct-15 6:10am

1lakh= 100,000 so 27 lakhs= 2,700,000 records

ManojanbU 2-Oct-15 6:19am

i have know 27 lakh=2700000 .then I need how to reduce the time when query select.

Patrice T 2-Oct-15 6:28am

My comment was not for you !
a lakh is not a used unit outside of India.

ManojanbU 2-Oct-15 6:36am

oh ok...Nearly 3 million records in a table.Hereafter i use this correctly..thanks for this. Then Any solution for this question

ManojanbU 2-Oct-15 6:21am

if i remove
(
(select count(user_Id) from tbl_payment_Master
where user_Id=a.user_Id and premium_amount!=0 and paid_date=a.paid_date group by paid_date) as noofpaymnt,
(select count(user_Id) from tbl_payment_Master
where user_Id=a.user_Id and premium_amount!=0 and month(paid_Date)=MONTH(a.paid_date) and year(paid_Date)=year(a.paid_date)) as noofpaymonth
)
this part from the query then it takes only fraction of seconds

Tomas Takac 2-Oct-15 8:45am

I guess the problem is using MONTH and YEAR functions in the join condition. This prevents the query engine from using indexes.

phil.o 2-Oct-15 6:23am

Hard to tell without knowing your database. We would need to know defined indices, for examples.

ManojanbU 2-Oct-15 6:49am

i have tables like tbl_user_registration,tbl_payment_Master.
In tbl_user_registration table have two colmns like user_id(primary key),name
and tbl_payment_mater table have four colmns like payment_id(primary key)
,paid_date,premium_amount,user_id(foreign key referenced by tbl_user_registration ).

Patrice T 2-Oct-15 8:52am

Try to Improve Question with your comments.
In the question, you comments are more likely to be read by helpers.

Richard Deeming 2-Oct-15 8:44am

month(paid_Date)=MONTH(a.paid_date) and year(paid_Date)=year(a.paid_date)

That filter looks like a good place to start. You're calling functions on the columns on both sides of the comparison, which means SQL can't use any indexes on that column.

Try changing it to:

paid_Date >= DateAdd(day, 1 - Day(a.paid_Date), a.paidDate) AND paid_Date < DateAdd(month, 1, DateAdd(day, 1 - Day(a.paid_Date), a.paid_Date))

Then check the actual execution plan to see if there are any missing indexes.

2 solutions

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)

VADIVEL.P · Answer 1 · 2015-10-03T21:51:00

Hi,

In sub-query instead of using a.userid why don't try using direct user variable, that may change execution plan.try this you will get little bit of improvement

select a.user_Id,b.name,a.paid_date,
(
select count(user_Id) from tbl_payment_Master
where user_Id=@user_id and paid_date=a.paid_date group by paid_date) as noofpaymnt,
(
select count(user_Id) from tbl_payment_Master
where user_Id=@user_id and
month(paid_Date)=MONTH(a.paid_date)
and year(paid_Date)=year(a.paid_date)
)
as noofpaymonth

from tbl_payment_master as a
inner join tbl_user_registration as b on a.user_id=b.user_id
where a.premium_amount!=0 and a.user_id=@user_id

-------and also try with CTE-----------

Patrice T · Answer 2 · 2015-10-04T03:24:00

I would optimise this clause.

SQL

where user_Id=a.user_Id  and month(paid_Date)=MONTH(a.paid_date) and year(paid_Date)=year(a.paid_date))

I would create a new field paid_Month containing yyyymm from paid_Date in each table.
Then change the clause to:

SQL

where user_Id=a.user_Id  and paid_Month=a.paid_Month

How to improve the performance when select get few records from multi lakhs records in sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0