ASP/SQL concurrent user issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ASP/SQL concurrent user issue

I have developed an ASP Application to access several databases that reside in Sql Server 2000 Standard Edition via a stored procedure. A typical database has 1.5 – 3.5 million records in each yearly database. The typical search time is around 2 seconds. The problem however comes into play when two or more users try to perform searches. All users can submit their search at one time, but only one user at a time will get their results back (i.e. third user has to wait 6 seconds for results). Below is how I am connecting to the sql server and executing the stored procedure. Then building results in table using disconnected recordsets. Can someone help me figure out what is happening.<br /><br />’ASP code<br />Dim strConn, cn, cmdStoredProc, rs, sql<br />strConn = "Provider=SQLOLEDB.1<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />assword=PASSWORD<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />ersist Security Info=False;User ID=USERID;Initial Catalog=DATABASENAME;Data Source=IPADDRESS"<br />’Create a connection object<br />Set cn = Server.CreateObject("ADODB.Connection")<br />cn.Open strConn<br />sql = "EXEC GetSortedPage ‘WarrantTable’,’WarrantNo’,’" & u_input_sort & "’,’" & u_input_numperpage & "’,’" & pagecounter & "’,’WarrantNo,WarrantYear,WarrantDay,WarrantMonth,WarrantAmount,VendorName,FundNo,CenterNo,ObjectNo,AgencyNo,PayEntity,BapCode,PONo,POLineNo,InvoiceNo,InvoiceDate,CntlGroupNo’,’" & u_input_vendorname & "%’,’" & u_input_vendorno & "%’,’" & u_input_vendoraddress & "%’,’" & u_input_zipcode & "%’,’" & u_input_amountmin & "’,’" & u_input_amountmax & "’,’" & u_input_warrantno & "%’,’" & u_input_month & "%’,’" & u_input_warrantday & "%’,’" & u_input_pono & "%’,’" & u_input_invoiceno & "%’,’" & u_input_fundno & "%’,’" & u_input_centerno & "%’,’" & u_input_objectno & "%’,’" & u_input_agencyno & "%’,’" & u_input_bapcode & "%’,’" & u_input_payentity & "%’"<br /><br />Set rs = Server.CreateObject("ADODB.Recordset")<br />rs.Open sql, strConn<br />alldata = rs.getrows<br />rs.close<br />cn.close<br />set rs = nothing<br />set cn = nothing<br /><br />’stored procedure code<br />CREATE PROCEDURE GetSortedPage<br />(<br /> @TableName VARCHAR(50),<br /> @PrimaryKey VARCHAR(25),<br /> @SortField VARCHAR(50),<br /> @PageSize INT,<br /> @PageIndex INT = 1,<br /> @SelectFields VARCHAR(1000) = NULL,<br /> @vendornameVARCHAR (30),<br /> @vendoridVARCHAR (9),<br /> @vendoraddress1VARCHAR (30),<br /> @vendorzipcodeVARCHAR (10),<br /> @warrantminNVARCHAR(20),<br /> @warrantmaxNVARCHAR (20),<br /> @warrantnoVARCHAR (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,<br /> @warrantmonthVARCHAR (2),<br /> @warrantdayVARCHAR (2),<br /> @ponoVARCHAR (10),<br /> @invoicenoVARCHAR (16),<br /> @fundnoVARCHAR (4),<br /> @centernoVARCHAR (6),<br /> @objectnoVARCHAR (6),<br /> @agencynoVARCHAR (3),<br /> @bapcodeVARCHAR (3),<br /> @payentityVARCHAR (4)<br />)<br /> AS<br />SET NOCOUNT ON<br /><br />DECLARE @SizeString AS VARCHAR(5)<br />DECLARE @PrevString AS VARCHAR(5)<br /><br />SET @SizeString = CONVERT(VARCHAR, @PageSize)<br />SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex – 1))<br /><br /> EXEC(<br /> ‘SELECT ‘ + @SelectFields + ‘ FROM ‘ + @TableName + ‘ WITH (NOLOCK) WHERE ‘ + @PrimaryKey + ‘ IN<br /> (SELECT TOP ‘ + @SizeString + ‘ ‘ + @PrimaryKey + ‘ FROM ‘ + @TableName + ‘ WITH (NOLOCK) WHERE VendorName LIKE "’ + @vendorname + ‘" AND VendorTaxID LIKE "’ + @vendorid + ‘" AND WarrantNo LIKE "’ + @warrantno + ‘" AND WarrantMonth LIKE "’ + @warrantmonth + ‘" AND WarrantDay LIKE "’ + @warrantday + ‘" AND VendorAddress1 LIKE "’ + @vendoraddress1 + ‘" AND VendorZipCode LIKE "’ + @vendorzipcode + ‘" AND (WarrantAmount BETWEEN ‘ + @warrantmin + ‘ AND ‘ + @warrantmax +’) AND FundNo LIKE "’ + @fundno + ‘" AND CenterNo LIKE "’ + @centerno + ‘" AND ObjectNo LIKE "’ + @objectno + ‘" AND PointNo LIKE "’ + @pono + ‘" AND InvoiceNo LIKE "’ + @invoiceno + ‘" AND AgencyNo LIKE "’ + @agencyno + ‘" AND BAPCode LIKE "’ + @bapcode + ‘" AND PayEntity LIKE "’ + @payentity + ‘" AND ‘ + @PrimaryKey + ‘ NOT IN<br /> (SELECT TOP ‘ + @PrevString + ‘ ‘ + @PrimaryKey + ‘ FROM ‘ + @TableName + ‘ WITH (NOLOCK) WHERE VendorName LIKE "’ + @vendorname + ‘" AND VendorTaxID LIKE "’ + @vendorid + ‘" AND WarrantNo LIKE "’ + @warrantno + ‘" AND WarrantMonth LIKE "’ + @warrantmonth + ‘" AND WarrantDay LIKE "’ + @warrantday + ‘" AND VendorAddress1 LIKE "’ + @vendoraddress1 + ‘" AND VendorZipCode LIKE "’ + @vendorzipcode + ‘" AND (WarrantAmount BETWEEN ‘ + @warrantmin + ‘ AND ‘ + @warrantmax + ‘) AND FundNo LIKE "’ + @fundno + ‘" AND CenterNo LIKE "’ + @centerno + ‘" AND ObjectNo LIKE "’ + @objectno + ‘" AND PointNo LIKE "’ + @pono + ‘" AND InvoiceNo LIKE "’ + @invoiceno + ‘" AND AgencyNo LIKE "’ + @agencyno + ‘" AND BAPCode LIKE "’ + @bapcode + ‘" AND PayEntity LIKE "’ + @payentity +<br />’" ORDER BY ‘ + @SortField + ‘)<br /> ORDER BY ‘ + @SortField + ‘)<br /> ORDER BY ‘ + @SortField <br />)<br /> EXEC(‘SELECT (COUNT(‘ + @PrimaryKey + ‘) – 1)/’ + @SizeString + ‘ + 1 AS PageCount, COUNT(‘ + @PrimaryKey + ‘) AS RecordCount FROM ‘ + @TableName + ‘ WITH (NOLOCK) WHERE VendorName LIKE "’ + @vendorname + ‘" AND VendorTaxID LIKE "’ + @vendorid + ‘" AND WarrantNo LIKE "’ + @warrantno + ‘" AND WarrantMonth LIKE "’ + @warrantmonth + ‘" AND WarrantDay LIKE "’ + @warrantday + ‘" AND VendorAddress1 LIKE "’ + @vendoraddress1 + ‘" AND VendorZipCode LIKE "’ + @vendorzipcode + ‘" AND (WarrantAmount BETWEEN ‘ + @warrantmin + ‘ AND ‘ + @warrantmax + ‘) AND FundNo LIKE "’ + @fundno + ‘" AND CenterNo LIKE "’ + @centerno + ‘" AND ObjectNo LIKE "’ + @objectno + ‘" AND PointNo LIKE "’ + @pono + ‘" AND InvoiceNo LIKE "’ + @invoiceno + ‘" AND AgencyNo LIKE "’ + @agencyno + ‘" AND BAPCode LIKE "’ + @bapcode + ‘" AND PayEntity LIKE "’ + @payentity + ‘"’ )<br />GO<br />
What type of concurrency control you’re using? Take help of these articles to reduce the locking or blocking issues if you’;ve any:
http://support.microsoft.com/support/kb/articles/Q224/4/53.ASP – Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problem. http://www.sql-server-performance.com/reducing_locks.asp – Tips for Reducing SQL Server Locks http://www.sql-server-performance.com/lock_contention_tamed_article.asp – SQL Server Lock Contention Tamed Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
How can I find out what type of concurrency control I am using? Forgive me, I am new to Sql Server. I thought that by using the NOLOCK hint would take care of any concurrency issue getting around the sql locking system. By the way, I used sp_blocker_pss80 and it is showing that no blocking is taking place. All queries are SELECT’s, no updates or deletes during the day with a DTS package that imports new data during the night while no one is on the system.
The whole concept looks ugly. I expect a lot of dynamic query compilations and sp compilations and maybe a lot of table scans. However I don’t see how is this affecting concurrency. NoLock hints should be sufficient to avoid locking problems. Can you check cpu % on db server while running queries?
The CPU is only 5 to 10 %
Do you run application on the same comp as mssql server? Do you have enough RAM? This is really strange.
The they are running on different machines. The server with mssql on it has dual p-4’s with 2 GB RAM, (3) – 72 GB Raid 5 SCSI 15K drives. I am lost on this. I have done performance monitoring on it and everything and nothing seems to stand out. Is there some tool I can use to run the stored procedure on another machine to rule out the webserver causing the problem?
You can use query analyzer.
As defined above you can use PROFILER to find out the slow runnign queries and submit the trace to the INdex tuning wizard to get recommendation on optimum indexes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I couldnt get the Index tuning wizard to give me recommendation on optimum indexes, but I did reconfigure the indexes and then performed database maintenance on the databases. Now it is running like a charm execpt how can I run the scheduled maintenance from a DTS package? Or can I reorganize data and index pages and check database integrity through a dbcc statement or TSQL?
Check thishttp://www.sql-server-performance.com/tp_automatic_reindexing.asp article for further information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>