SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Database Hang
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SHMP
Starting Member

India
1 Posts

Posted - 12/09/2011 :  01:42:16  Show Profile  Reply with Quote
Hi,

We are facing issues in our application and database.Earlier our application having front end Classic ASP, asp.net and backend SQL Server 2000. We migrated SQL Server 2000 to SQL Server 2005 thereafter problem started appliccation & database getting hang . We facing issues of application & database getting hang during the project assignment to team. If more than 20 users login to the application that time if we edit the assignment then application and database get hangs.Is this issuse related to IIS or SQL server 2005 because if we use SQL Server 2000 then application/database not getting hang.

We doing update,delete, inserting records on 6 tables.Code we used in asp page for assignment to team.

<%
Response.Buffer = true
%>
<!--#include virtual="/includes/admin_security.asp"-->
<!--#include virtual = "/includes/db_con.asp"-->
<!--#include virtual = "/includes/dbcon1.asp"-->
<!--#include virtual="/ERROR.asp"-->
<%
field_names = split(Replace(Request.form("field_name"),"'","''"),", ")
field_id = split(Replace(Request.form("field_id"),"'","''"),", ")
field_validators = split(Replace(Request.form("field_validator"),"'","''"),", ")
name_check = split(Replace(Request.form("name_check"),"'","''"),"*")
Fld_group = split(Replace(Request.form("Fld_group"), "'", "''"),", ")
letter_case = split(Replace(Request.form("letter_case"),"'","''"),", ")
txt_exclude = split(Replace(Request.form("txt_exclude"),"'","''"),", ")
txt_include = split(Replace(Request.form("txt_include"),"'","''"),", ")
is_multiples = split(Replace(Request.form("is_multiple"),"'","''"),", ")
assign_group = split(request.Form("assignment"),", ")
project_id = Request.Form("project_id")
Mult_length= split(Replace(Request.form("Mult_length"), "'", "''"),"*")
txt_ConF=split(replace(request.Form("txt_ConF"),"'","''"),", ")

if Trim(Request.Form("QC")) <> "" Then
QCs = split(Request.Form("QC"),", ")
Else
Response.Write "<script language=javascript>alert('Select a member for Quality Team');history.back(-1);</script>"
Response.End
End if
if Trim(Request.Form("RW")) <> "" Then
RWs = split(Request.Form("RW"),", ")
Else
Response.Write "<script language=javascript>alert('Select a member for Rework Team');history.back(-1);</script>"
Response.End
End if
Dim con,con1, rs,rs1
Set con = Server.CreateObject("ADODB.Connection")
Set con1 = Server.CreateObject("ADODB.Connection")
con.open dbcon
con1.open dbcon1
con.begintrans
for z =0 to ubound(assign_group)
if Trim(Request.Form("coders_"&assign_group(z))) <> "" Then
checkCoders = checkCoders & Request.Form("coders_"&assign_group(z)) &", "
Else
Response.Write "<script language=javascript>alert('Select a coder for " & assign_group(z) & " field');history.back(-1);</script>"
Response.End
End if
next
checkCodersArray = split(checkCoders,", ")
for p = 0 to ubound(checkCodersArray)
for q = 0 to ubound(QCs)
if (checkCodersArray(p) = QCs(q)) then
Response.Write "<script language=javascript>alert('Coder can not be a member of Quality Team');history.back(-1);</script>"
Response.End
end if
next
for r = 0 to ubound(RWs)
if (checkCodersArray(p) = RWs(r)) then
Response.Write "<script language=javascript>alert('Coder can not be a member of Rework Team');history.back(-1);</script>"
Response.End
end if
next
next
for s = 0 to ubound(QCs)
for t = 0 to ubound(RWs)
if (QCs(s) = RWs(t)) then
Response.Write "<script language=javascript>alert('Quality Team member can not be a Rework Team member');history.back(-1);</script>"
Response.End
end if
next
next
CON.EXECUTE("UPDATE T_DOCUMENT SET IS_LOCKED = 0,STATUS = 0 WHERE PROJECT_ID = "&PROJECT_ID&" AND IS_LOCKED = 1 AND STATUS <> 0 AND STATUS IN (SELECT USER_ID FROM T_USER_ACCESS WHERE PROJECT_ID = "&PROJECT_ID&" AND ACCESS_ID = 2)")
con.Execute ("Delete from T_USER_ACCESS Where PROJECT_ID = "&project_id&" and ACCESS_ID = 2")
con.Execute ("UPDATE t_field SET field_status = 0 WHERE PROJECT_ID = "&project_id)
for i =0 to ubound(field_names)
if i <= Ubound(field_id) then
con.execute("UPDATE T_FIELD SET FIELD_NAME = '"&field_names(i)&"',FIELD_VALIDATOR = "&field_validators(i)&",CHOICE_LIST = '"&trim(name_check(i))&"',IS_MULTIPLE = "&is_multiples(i)&", MULTIPLE_LENGTH = "& Mult_length(i)&" ,LETTER_CASE = '"&letter_case(i)&"',INCLUDES = '"&txt_include(i)&"',EXCLUDES = '"&txt_exclude(i)&"', CONVERSION_FACTOR='" & txt_ConF(i) & "',FIELD_STATUS = 1, FIELD_GROUP = " & Fld_group(i) & ",assign_group = '"&assign_group(i)&"' WHERE FIELD_ID = "&field_id(i)&" and PROJECT_ID = "&project_id)
else
'con.execute("insert into T_FIELD(PROJECT_ID,FIELD_NAME,FIELD_VALIDATOR,CHOICE_LIST,IS_MULTIPLE,MULTIPLE_LENGTH,LETTER_CASE,INCLUDES,EXCLUDES,FIELD_STATUS,FIELD_GROUP,assign_group) values("&project_id&",'"&field_names(i)&"',"&field_validators(i)&",'"&trim(name_check(i))&"',"&is_multiples(i)&", '"& Mult_length(i) &"','"&letter_case(i)&"','"&txt_include(i)&"','"&txt_exclude(i)&"',1,"&Fld_group(i)&",'"&assign_group(i)&"')")
con.execute("insert into T_FIELD(PROJECT_ID,FIELD_NAME,FIELD_VALIDATOR,CHOICE_LIST,IS_MULTIPLE,MULTIPLE_LENGTH,LETTER_CASE,INCLUDES,EXCLUDES,CONVERSION_FACTOR,FIELD_STATUS,FIELD_GROUP,assign_group) values("&project_id&",'"&field_names(i)&"',"&field_validators(i)&",'"&trim(name_check(i))&"',"&is_multiples(i)&", '"& Mult_length(i) &"','"&letter_case(i)&"','"&txt_include(i)&"','"&txt_exclude(i)&"','" & txt_ConF(i)&"',1,"&Fld_group(i)&",'"&assign_group(i)&"')")
end if
next
con.execute("Delete from T_HR_REP where FIELD_ID in (select FIELD_ID from T_FIELD where PROJECT_ID=" & project_id & " and FIELD_STATUS=0)")
con.execute("Delete from T_DATA where FIELD_ID in (select FIELD_ID from T_FIELD where PROJECT_ID=" & project_id & " and FIELD_STATUS=0)")
con.execute("Delete from T_FIELD where PROJECT_ID=" & project_id & " and FIELD_STATUS=0")
if Ubound(field_names) > Ubound(field_id) then
con.Execute ("UPDATE t_document SET is_checked = 0 WHERE project_id = " & project_id)
end if
con.Execute ("DELETE FROM t_rep_format WHERE field_id IN (SELECT field_id FROM t_field WHERE field_status = 0)")
con.Execute ("Delete from T_USER_ACCESS Where PROJECT_ID = "&project_id&" and ACCESS_ID = 3")
for k = 0 to ubound(QCs)
con.execute("insert into T_USER_ACCESS(USER_ID,ACCESS_ID,PROJECT_ID,FIELD_ID) values("&QCs(k)&",3,"&project_id&",0)")
next
CON.EXECUTE("UPDATE T_DOCUMENT SET IS_LOCKED = 0,STATUS = 0 WHERE PROJECT_ID = "&PROJECT_ID&" AND IS_LOCKED = 1 AND STATUS <> 0 AND STATUS IN (SELECT USER_ID FROM T_USER_ACCESS WHERE PROJECT_ID = "&PROJECT_ID&" AND ACCESS_ID = 4)")
con.Execute ("Delete from T_USER_ACCESS Where PROJECT_ID = "&project_id&" and ACCESS_ID = 4")
for z = 0 to ubound(RWs)
con.execute("insert into T_USER_ACCESS(USER_ID,ACCESS_ID,PROJECT_ID,FIELD_ID) values("&RWs(z)&",4,"&project_id&",0)")
next
CON.EXECUTE("UPDATE T_DOCUMENT SET IS_LOCKED = 0,STATUS = 0, REWORKER_ID = NULL WHERE PROJECT_ID = "&PROJECT_ID&" AND QC_ID NOT IN (SELECT USER_ID FROM T_USER_ACCESS WHERE PROJECT_ID = "&PROJECT_ID&" AND ACCESS_ID = 3)")
con.committrans
set userlist = server.CreateObject("ADODB.Recordset")
userlist.open "select * from t_field where project_id = "&project_id &" order by field_id",con
while not userlist.eof
group = userlist("assign_group")
fldid = userlist("field_id")
users = split(request.Form("coders_"&group),", ")
for lvl = 0 to ubound(users)
con.execute("insert into T_USER_ACCESS(USER_ID,ACCESS_ID,PROJECT_ID,FIELD_ID) values("&users(lvl)&",2,"&project_id&","&fldid&")")
next
userlist.movenext
wend
set userlist = nothing
con.close
set con = nothing
Response.Redirect "admin_home.asp?param=projecteditSuccess"
Response.End()
%>


Thank you in advance

MP

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/09/2011 :  05:02:53  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Which statement is the one that the app hangs on?
Have you checked for blocking?
Have you checked what the wait type is?
Have you optimised the queries (added indexes as necessary)?

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000