Application Page/SQL running slowly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Application Page/SQL running slowly

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2007-04-24 : 05:44:20

Hi there, I have the following code in an ASP page to select and insert information from/into tables in an SQL Server 2000 database. The problem is that it runs very slowly for some reason....much slower than any of the other SQL I have embedded in ASP pages and/or in stored procedures. Can anyone see why this might be?


<%

On Error Resume Next

ContractNo=request.form("ContractNo")
CostCentre=request.form("CostCentre")

Dim objConn, ReportInfo
Set objConn = Server.CreateObject("ADODB.Connection")
Dim strConnect
strConnect="DSN=*****;UID=*****;PWD=*****"

objConn.open strConnect
objConn.execute("DELETE FROM dbo.tCostsOnlyReport")
If CostCentre = "" then
Set ReportInfo = objConn.execute("SELECT Property.dbo.tProperties.PropertyName, Engineering.dbo.tAppliance.Cost FROM Engineering.dbo.tAppliance INNER JOIN Property.dbo.tProperties ON Engineering.dbo.tAppliance.DatabaseRef = Property.dbo.tProperties.PropertyNo WHERE (tAppliance.ContractNo = '"&ContractNo&"') AND (tAppliance.LocationCode IS NULL OR tAppliance.LocationCode = '')")
else
Set ReportInfo = objConn.execute("SELECT Property.dbo.tProperties.PropertyName, Engineering.dbo.tAppliance.Cost FROM Engineering.dbo.tAppliance INNER JOIN Property.dbo.tProperties ON Engineering.dbo.tAppliance.DatabaseRef = Property.dbo.tProperties.PropertyNo WHERE tAppliance.ContractNo = '"&ContractNo&"' AND tAppliance.LocationCode = '"&CostCentre&"'")
end if

while (NOT ReportInfo.EOF)

PropertyName = ReportInfo.Fields.Item("PropertyName").Value
Cost = ReportInfo.Fields.Item("Cost").Value

PropertyName=REPLACE(PropertyName, "'", "''")

objConn.execute("INSERT INTO dbo.tCostsOnlyReport (ContractNo, LocationCode, PropertyName, Cost) VALUES ('"&ContractNo&"', '"&CostCentre&"', '"&PropertyName&"', CONVERT(money, '"&Cost&"', 1))")

ReportInfo.MoveNext()

Wend

%>

Thanks in advance :)

SQLMonkey
Starting Member

4 Posts

Posted - 2007-04-24 : 12:30:12

I think my original reply got lost but if not, ignore this.

Generally, loading a recordset into ASP, looping through and inserting one record at a time into a table will be much much slower than executing a single insert statement or a stored procedure that executes a single insert statement. Something along these lines should do it (haven't tested it):

create procedure xyz
(@ContractNo [supply data type], @CostCentre [supply data type])
as
INSERT INTO dbo.tCostsOnlyReport (ContractNo, LocationCode, PropertyName, Cost)
SELECT @ContractNo, @CostCentre, PropertyName, tAppliance.Cost
FROM Engineering.dbo.tAppliance INNER JOIN Property.dbo.tProperties ON Engineering.dbo.tAppliance.DatabaseRef = Property.dbo.tProperties.PropertyNo
WHERE tAppliance.ContractNo = @ContractNo
AND ISNULL(tAppliance.LocationCode,'') = @CostCentre

Give it a try. If that doesn't improve things, have a look at your table indexing.

Pete

Gyto
Posting Yak Master

144 Posts

Posted - 2007-04-25 : 11:22:45

Hi there,

Thanks for your reply! I have transferred it into a stored procedure which is probably for the best....although it still seemed slow initially....then I discovered that the permissions for one of the tables weren't set quite as they should've been, yet it still seemed to work....just very slowly....curious!! I thought that would thrown out an error, but anyhow, I think changing these permissions may have sorted out the problem.

Thanks very much for your help, much appreciated :)

Matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources