CREATE procedure spRegReport

(
@ParkingCode Varchar(255),
@Location Varchar(255),
@Department Varchar(255),
@CostCode Varchar(255),
@CurrentBan Varchar(255)
)

AS


UPDATE dbo.tCarPass SET ReinstatementDate = NULL WHERE (ReinstatementDate < GetDate())


INSERT INTO dbo.tRegReportData (PassNo, Surname, Forename, NonParkingDay, TelNo)
SELECT tCarPass.PassNo, tCarPass.Surname, tCarPass.Forename, tCarPass.NonParkingCode, tCarPass.TelNo
FROM tCarPass
WHERE ((tCarPass.NonParkingCode = @ParkingCode OR @ParkingCode = 'ALL') 
AND (tCarPass.Location = @Location OR @Location = 'ALL')  
AND (tCarPass.Department = @Department OR @Department = 'ALL')  
AND (tCarPass.CostCode = @CostCode OR @CostCode = 'ALL') 
AND (tCarPass.ReinstatementDate >= GetDate() OR @CurrentBan = 'ALL'))
GO

quote:

---

Originally posted by Gyto

1000 is roughly the total rows in tCarPass, so it will currently never be much more than that....usually much less, in fact.

The query runs almost instantly from query analyser, with or without the insert statement, but sometimes takes up to about 15 seconds when run from within the application, even though there is almost no other code on the page. Also, if you run it once, then again immediately after, the 2nd time is usually much quicker than the first.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Gyto

1000 is roughly the total rows in tCarPass, so it will currently never be much more than that....usually much less, in fact.

The query runs almost instantly from query analyser, with or without the insert statement, but sometimes takes up to about 15 seconds when run from within the application, even though there is almost no other code on the page. Also, if you run it once, then again immediately after, the 2nd time is usually much quicker than the first.

---

How many indexes do you have on table? Also have a had a look at execution plan to see what is bottleneck statement?

---

quote:

---

Originally posted by Gyto

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Gyto

1000 is roughly the total rows in tCarPass, so it will currently never be much more than that....usually much less, in fact.

The query runs almost instantly from query analyser, with or without the insert statement, but sometimes takes up to about 15 seconds when run from within the application, even though there is almost no other code on the page. Also, if you run it once, then again immediately after, the 2nd time is usually much quicker than the first.

---

How many indexes do you have on table? Also have a had a look at execution plan to see what is bottleneck statement?

---

There is just the one default index on the tCarPass table.....I have to confess I am not an expert re. indexing! Also where can I find the execution plan?

---

objConn.execute("DELETE FROM tRegReportData")

Set CmdStoredProc = Server.CreateObject("ADODB.Command")

CmdStoredProc.ActiveConnection = objConn
CmdStoredProc.CommandText = "spRegReport"
CmdStoredProc.CommandType = adCmdStoredProc

CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@ParkingCode", adVarChar, adParamInput, 255, ParkingCode)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@Location", adVarChar, adParamInput, 255, Location)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@Department", adVarChar, adParamInput, 255, Department)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@CostCode", adVarChar, adParamInput, 255, CostCode)
CmdStoredProc.Parameters.Append CmdStoredProc.CreateParameter("@CurrentBan", adVarChar, adParamInput, 255, CurrentBan)

CmdStoredProc.Execute , , adExecuteNoRecords

Set CmdStoredProc = Nothing