Query taking much time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query taking much time

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2010-06-17 : 17:03:47

Following procedure is taking much time to execute how to shorten it. Can anyone suggest.????

CREATE PROCEDURE [dailypaisaus].[FetchPayForAdmin]
(
@FromDate smalldatetime,
@ToDate smalldatetime,
@bName varchar (50)
)
AS
BEGIN

DECLARE @DateTable AS TABLE (RegNo varchar (50),FName varchar(100),BankAcName varchar(100),
BankName varchar(100),
BankBranch varchar(100),
AcNo nvarchar(100),
IFSC varchar(50),
AcType varchar(50),
TotalPay int,
PaidAmt int
)

INSERT INTO @DateTable --(FName,BankAcName ,BankName ,BankBranch ,AcNo ,IFSC ,AcType ,TotalPay ,PaidAmt )
Select
(Select AppMstRegNo from dailypaisaus.appmst where appmstid = a.appmstid),
Upper((Select AppMstFname from dailypaisaus.appmst where appmstid = a.appmstid)),
upper((Select AppMstBankAcName from dailypaisaus.appmstdetail where appmstid = a.appmstid)),
upper((Select AppMstBankName from dailypaisaus.appmstdetail where appmstid = a.appmstid)),
upper((Select AppMstBankBranch from dailypaisaus.appmstdetail where appmstid = a.appmstid)),
upper((Select AppMstBankAccNo from dailypaisaus.appmstdetail where appmstid = a.appmstid)),
upper((Select txtIFSC from dailypaisaus.appmstdetail where appmstid = a.appmstid)),
upper((Select AppMstAcType from dailypaisaus.appmstdetail where appmstid = a.appmstid)),
(Select (sum(NetPay) - (sum(NetPay) *15/100)) from DailyPaisaus.Payreportsnew Where AppMstId =a.AppMstId and PayDate
>='2010-05-02 00:00:00'),
(Select sum(TotalPaidAmt) from DailyPaisaus.PayMast Where AppMstId =a.AppMstId)
from DailyPaisaus.PayReportsNew a Where PayDate >=@FromDate and PayDate <=@ToDate Group by a.AppMstID

Delete from @DateTable Where RegNo in (Select AppMstRegNo from dailypaisaus.appmst Where appMstActivate =0)

Select
RegNo,FName,BankAcName,BankName,BankBranch,AcNo,IFSC,AcType ,(TotalPay -PaidAmt) as NetPay
from @DateTable Where len(BankName) > 0 and (TotalPay -PaidAmt) > 0 and len(AcNo) > 0 Order by BankName

END

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-18 : 23:28:32

You could try something like the below for the select for the insert.
It will save you a few select statements.
Pete.

Select upper(mst.AppMstRegNo),upper(mst.AppMstFname),upper(AppMstBankAcName),
upper(AppMstBankBranch),upper(AppMstBankAccNo),upper(txtIFSC),upper(AppMstAcType),b.amt
from
(
select a.appmstid,sum(TotalPaidAmt) as TotalPaidAmt
from DailyPaisaus.PayReportsNew a
Where PayDate between @FromDate and @ToDate
Group by a.AppMstID
) a
join dailypaisaus.appmst mst on mst.appmstid = a.appmstid
join dailypaisaus.appmstdetail dtl on dtl.appmstid = a.appmstid
join
(
Select a.appmstid,(sum(NetPay) - (sum(NetPay) *15/100)) amt
from DailyPaisaus.Payreportsnew a
Where PayDate >='2010-05-02 00:00:00'
) b on b.appmstid = a.appmstid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources