-1 RecordCount / Long script execution time

Hi all,

I've recently started working with a new piece of software written in ASP with a Microsoft SQL database on the backend. There's one page which pulls records from the DB through a stored procedure which is taking over 10 seconds to run. This is causing a problem for our users because they access the page frequently and it's really starting to slow things down now! When the stored procedure is run in ASP, it takes around 4.5 seconds (hence the timers). There is also some code to manually count the records in the rs if the recordcount is -1 (which it is) - this takes another 6-6.5 seconds (actually counting through all records, then refreshing the rs).

Does anyone know how I can speed this up? The SP and the ASP code are pasted below. Ideally, a slightly faster stored procedure along with a true recordcount property being returned would be a perfect solution (bringing execution time to somewhere in the region of 2 seconds) - although this is still slow, it would be significantly faster than our current solution. Of course, any ideas for speeding things up further would be hugely appreciated!

Thanks,

Steve

PS Extra points are being offered for this one because I'm after a quick solution!

<%
StartBlock4 = Timer

set spAList = Server.CreateObject("ADODB.Command")
spAList.ActiveConnection = MM_LeeNightingale_STRING
spAList.CommandText = "dbo.spA_List"
spAList.Parameters.Append spAList.CreateParameter("@RETURN_VALUE", 3, 4)
spAList.Parameters.Append spAList.CreateParameter("@U", 200, 1,50,spAList__U)
spAList.Parameters.Append spAList.CreateParameter("@T", 200, 1,50,spAList__T)
spAList.Parameters.Append spAList.CreateParameter("@C", 200, 1,50,spAList__C)
spAList.Parameters.Append spAList.CreateParameter("@P", 200, 1,50,spAList__P)
spAList.Parameters.Append spAList.CreateParameter("@G", 200, 1,50,spAList__G)
spAList.Parameters.Append spAList.CreateParameter("@FA", 200, 1,50,spAList__FA)
spAList.Parameters.Append spAList.CreateParameter("@FK", 200, 1,50,spAList__FK)
spAList.Parameters.Append spAList.CreateParameter("@FC", 200, 1,50,spAList__FC)
spAList.CommandType = 4
spAList.CommandTimeout = 0
spAList.Prepared = true
set rsAList = spAList.Execute()

rsAList_numRows = 0

Block4Time = Timer - StartBlock4

%>





CREATE PROCEDURE dbo.spA_List(@U nvarchar(50),
@T nvarchar(50),
@C nvarchar(50),
@P nvarchar(50),
@G nvarchar(50) = 0,
@FA nvarchar(50),
@FK nvarchar(50),
@FC nvarchar(50))
AS SELECT DISTINCT
                      dbo.AOrders.AID, dbo.AOrders.Username, dbo.AOrders.OrderDate, dbo.AOrders.OrderType, dbo.AOrders.JobDescription,
                      dbo.CustomerNotes.ACCOUNT_REF, dbo.qryCancelA.Cancelled, dbo.qryProceedA.Proceed, dbo.CustomerNotes.NAME
FROM         dbo.AOrders INNER JOIN
                      dbo.qryProceedA ON dbo.AOrders.AID = dbo.qryProceedA.AID INNER JOIN
                      dbo.qryCancelA ON dbo.AOrders.AID = dbo.qryCancelA.AID INNER JOIN
                      dbo.CustomerNotes ON dbo.AOrders.Client = dbo.CustomerNotes.ACCOUNT_REF LEFT OUTER JOIN
                      dbo.KOrders ON dbo.AOrders.AID = dbo.KOrders.AID
WHERE     (dbo.AOrders.Username LIKE @U) AND (dbo.qryProceedA.Proceed LIKE @P) AND (dbo.AOrders.OrderType LIKE @T) AND
                      (dbo.qryCancelA.Cancelled LIKE @C) AND (@G = 0) AND (dbo.AOrders.OrderType <> N'Composite') AND (dbo.AOrders.OrderType <> N'Consortium') AND
                       (CONVERT(nvarchar(50), dbo.AOrders.AID) LIKE @FA) AND (CONVERT(nvarchar(50), dbo.KOrders.KID) LIKE @FK) AND
                      (dbo.CustomerNotes.ACCOUNT_REF LIKE @FC) OR
                      (dbo.AOrders.Username LIKE @U) AND (dbo.qryProceedA.Proceed LIKE @P) AND (dbo.AOrders.OrderType LIKE @T) AND
                      (dbo.qryCancelA.Cancelled LIKE @C) AND (@G = 0) AND (dbo.AOrders.OrderType <> N'Composite') AND (dbo.AOrders.OrderType <> N'Consortium') AND
                       (CONVERT(nvarchar(50), dbo.AOrders.AID) LIKE @FA) AND (CONVERT(nvarchar(50), dbo.KOrders.KID) IS NULL) AND
                      (dbo.CustomerNotes.ACCOUNT_REF LIKE @FC) AND (@FK = '%') OR
                      (dbo.AOrders.Username LIKE @U) AND (dbo.qryProceedA.Proceed LIKE @P) AND (dbo.AOrders.OrderType LIKE @T) AND
                      (dbo.qryCancelA.Cancelled LIKE @C) AND (@G = 1) AND (dbo.AOrders.OrderType <> N'Composite') AND (dbo.AOrders.OrderType <> N'Legal') AND
                      (CONVERT(nvarchar(50), dbo.AOrders.AID) LIKE @FA) AND (CONVERT(nvarchar(50), dbo.KOrders.KID) LIKE @FK) AND
                      (dbo.CustomerNotes.ACCOUNT_REF LIKE @FC) AND (dbo.AOrders.OrderType <> N'Recruitment') AND (dbo.AOrders.OrderType <> N'Web') AND
                      (dbo.AOrders.OrderType <> N'Creative') AND (dbo.AOrders.OrderType <> N'Consortium') OR
                      (dbo.AOrders.Username LIKE @U) AND (dbo.qryProceedA.Proceed LIKE @P) AND (dbo.AOrders.OrderType LIKE @T) AND
                      (dbo.qryCancelA.Cancelled LIKE @C) AND (@G = 1) AND (dbo.AOrders.OrderType <> N'Composite') AND (dbo.AOrders.OrderType <> N'Legal') AND
                      (CONVERT(nvarchar(50), dbo.AOrders.AID) LIKE @FA) AND (CONVERT(nvarchar(50), dbo.KOrders.KID) IS NULL) AND
                      (dbo.CustomerNotes.ACCOUNT_REF LIKE @FC) AND (@FK = '%') AND (dbo.AOrders.OrderType <> N'Recruitment') AND
                      (dbo.AOrders.OrderType <> N'Web') AND (dbo.AOrders.OrderType <> N'Creative') AND (dbo.AOrders.OrderType <> N'Consortium')
ORDER BY dbo.AOrders.OrderDate DESC

GO