|
SHMP
Starting Member
1 Post |
 Posted - 2011-12-09 : 01:42:16
|
| 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 advanceMP |
|
Database Hang