Performance problem for back dated entry

Question

Performance problem for back dated entry

chandrashekar.2512

SSC Eights!

Points: 965
More actions
December 21, 2009 at 11:35 pm

#228640

Hi every one,
I am maintaining web application which is on banking domain and it is in classic asp technology.
now we have around 1.5 lakhs of recorsds or data in single transaction table.
When ever i make back dated entry, means if i make an entry for 1/1/2007 then it has to insert into the table and after that from 1/1/2007 to till date what ever the entries have been made some of the fields needs to be updated.
So that mean from 1/1/2007 to till date it will come across nearly 2000 records, all these records to be updated.
All this calculation and updation are happening in same page so to do this it is taking nearly 6 to 8 min, which is causing the draw back in application performance.
Please somebody help me in improving this performance as this is banking domain clients are everyday tells about the performance of application
Below is the coding for updating the fields after insertion to transaction table
strSql="Select * from Tbl_Transaction where txt_StateCode="& txt_StateCode &" and txt_DistrictCode="& txt_DistrictCode &" and txt_AccountNO='"& txt_AccountNo &"' and int_DPCode='"& txt_DPCode &"' and dat_Date >'"& txt_Date &"' order by dat_Date,int_RID"
response.write "
strSql" & strSql
response.end
Set adoRs=Server.CreateObject("ADODB.Recordset")
adoRs.ActiveConnection = DBCon
adoRs.Source = strSql
adoRs.CursorLocation = 3'adUseClient
adoRs.CursorType = 0'adOpenForwardOnly
adoRs.Locktype = 1'adLockReadOnly
adoRs.Open
Set adoRs.ActiveConnection = Nothing
FOR J=1 TO adoRs.RECORDCOUNT
int_OpenningBln=int_ClosingBln
chr_OpenningType=chr_ClosingType
txt_TransCode=adoRs(5)
txt_TransType=adoRs(6)
txt_Amount=adoRs(7)
ResultStr=CallBalance(txt_TransType,txt_Amount,chr_OpenningType,int_OpenningBln)
Temp=split(ResultStr,":::")
int_ClosingBln=CDBL("0"& Temp(0))
chr_ClosingType=Temp(1)
int_OpenIntstBal=int_CloseIntstBal
int_CloseIntstBal=CallInterestBalance(txt_Amount,int_OpenIntstBal,txt_TransCode)
SQLUPDATE="UPDATE Tbl_Transaction set int_OpenningBln="& int_OpenningBln &",chr_OpenningType='"& chr_OpenningType &"',int_ClosingBln="& int_ClosingBln &",chr_ClosingType='"& chr_ClosingType &"',int_Open_Intrst='"& int_OpenIntstBal &"',int_Close_Intrst='"& int_CloseIntstBal &"'" &_
" WHERE int_SequenceNo="& adoRs(0)
DBCon.Execute(SQLUPDATE)
adoRs.MOVENEXT
NEXT
set adoRs = nothing
FUNCTION CallBalance(txt_TransType,txt_Amount,chr_OpenningType,int_OpenningBln)
DIM int_ClosingBln
DIM chr_ClosingType
IF ucase(trim(txt_TransType))="C" AND UCASE(TRIM(chr_OpenningType))="C" THEN
int_ClosingBln=Cdbl(int_OpenningBln)+CDBL(txt_Amount)
chr_ClosingType="C"
ELSEIF ucase(trim(txt_TransType))="D" AND UCASE(TRIM(chr_OpenningType))="D" THEN
int_ClosingBln=Cdbl(int_OpenningBln)+CDBL(txt_Amount)
chr_ClosingType="D"
ELSE
IF Cdbl(int_OpenningBln) > CDBL(txt_Amount) THEN
int_ClosingBln=Cdbl(int_OpenningBln)-CDBL(txt_Amount)
chr_ClosingType=UCASE(TRIM(chr_OpenningType))
ElseIF Cdbl(int_OpenningBln) < CDBL(txt_Amount) THEN
int_ClosingBln=CDBL(txt_Amount)-Cdbl(int_OpenningBln)
chr_ClosingType=UCASE(TRIM(txt_TransType))
Else
int_ClosingBln=0
chr_ClosingType="C"
End IF
END IF
CallBalance=int_ClosingBln &":::"& chr_ClosingType
END FUNCTION
Please some body help me how can improve this applications performance,
I think that instead of doing this entire process in asp file i need to write stored procedure or views or user defined function which will improve the perofrmance. i dont know how to handle it please somebody give me some solutions.
Thanks in Advance,
Chandrashekar

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

What level of experience do you have with SQL Server?

If none or limited, I recommend to either get some training or to have a 3rd party involved doing the conversion for you.

There is a fine line between helping someone to get his job done and actually doing the job for that person. From my point of view, the line is crossed in this case.

I think hire a SQL Server consultant would be more applicable in this case...

Edit: Additionally, we're talking about a solution for a financial system. How will you handle the consequences of lost data due to a wrong procedure?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

chandrashekar.2512 SSC Eights! Points: 965 More actions · Answer 2

Thanks for the reply,

i have limited knowledge in sql server,i can write quires , simple stored procedure and views, not much experience in sql. Unfortunately here no one is there to handle this project, i mean no sql server guiders are there.

And my self learning and implementing this application.

I need help from experts like you guys, and as i am ready to implement, only i need clear suggestion how to do it and i have not worked on looping in stored procedure and complex functions.

So i request you to suggest me and guide me in this scenaroio.

Thanks,

Chandrashekar

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

First thing, move the SQL into a stored procedure and call that procedure from your ASP (that's classic ASP?)

Once you've done that, please post the proc, table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

You shouldn't be trying to loop in stored procedures. There's nothing in this that I can see that needs a loop when done in SQL. It should be an update using the UPDATE ... SET ... FROM ... WHERE syntax (check Books Online for details) where the query following the FROM joins the table that's been updated to the one that you're using in the top select.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Also, note that using the [ code ] tags can help us a lot by making it easier to read your code:

strSql="Select * from Tbl_Transaction where txt_StateCode="& txt_StateCode &" and txt_DistrictCode=" _

& txt_DistrictCode &" and txt_AccountNO='"& txt_AccountNo &"' and int_DPCode='"& txt_DPCode _

&"' and dat_Date >'"& txt_Date &"' order by dat_Date,int_RID"

response.write "

strSql" & strSql

response.end

Set adoRs=Server.CreateObject("ADODB.Recordset")

adoRs.ActiveConnection = DBCon

adoRs.Source = strSql

adoRs.CursorLocation = 3'adUseClient

adoRs.CursorType = 0'adOpenForwardOnly

adoRs.Locktype = 1'adLockReadOnly

adoRs.Open

Set adoRs.ActiveConnection = Nothing

FOR J=1 TO adoRs.RECORDCOUNT

int_OpenningBln=int_ClosingBln

chr_OpenningType=chr_ClosingType

txt_TransCode=adoRs(5)

txt_TransType=adoRs(6)

txt_Amount=adoRs(7)

ResultStr=CallBalance(txt_TransType,txt_Amount,chr_OpenningType,int_OpenningBln)

Temp=split(ResultStr,":::")

int_ClosingBln=CDBL("0"& Temp(0))

chr_ClosingType=Temp(1)

int_OpenIntstBal=int_CloseIntstBal

int_CloseIntstBal=CallInterestBalance(txt_Amount,int_OpenIntstBal,txt_TransCode)

SQLUPDATE="UPDATE Tbl_Transaction set int_OpenningBln="& int_OpenningBln _

&",chr_OpenningType='"& chr_OpenningType &"',int_ClosingBln="& int_ClosingBln &",chr_ClosingType='" _

& chr_ClosingType &"',int_Open_Intrst='"& int_OpenIntstBal &"',int_Close_Intrst='"& int_CloseIntstBal &"'" & _

" WHERE int_SequenceNo="& adoRs(0)

DBCon.Execute(SQLUPDATE)

adoRs.MOVENEXT

NEXT

set adoRs = nothing

FUNCTION CallBalance(txt_TransType,txt_Amount,chr_OpenningType,int_OpenningBln)

DIM int_ClosingBln

DIM chr_ClosingType

IF ucase(trim(txt_TransType))="C" AND UCASE(TRIM(chr_OpenningType))="C" THEN

int_ClosingBln=Cdbl(int_OpenningBln)+CDBL(txt_Amount)

chr_ClosingType="C"

ELSEIF ucase(trim(txt_TransType))="D" AND UCASE(TRIM(chr_OpenningType))="D" THEN

int_ClosingBln=Cdbl(int_OpenningBln)+CDBL(txt_Amount)

chr_ClosingType="D"

ELSE

IF Cdbl(int_OpenningBln) > CDBL(txt_Amount) THEN

int_ClosingBln=Cdbl(int_OpenningBln)-CDBL(txt_Amount)

chr_ClosingType=UCASE(TRIM(chr_OpenningType))

ElseIF Cdbl(int_OpenningBln) < CDBL(txt_Amount) THEN

int_ClosingBln=CDBL(txt_Amount)-Cdbl(int_OpenningBln)

chr_ClosingType=UCASE(TRIM(txt_TransType))

Else

int_ClosingBln=0

chr_ClosingType="C"

End IF

END IF

CallBalance=int_ClosingBln &":::"& chr_ClosingType

END FUNCTION

(If you want to see how I did this, just click the "Quote" button above my post.)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]