Query Times out.

[Gatorajc]

I need some suggestions. I have a query that keeps timing out. I need to stop but I need the best option. I tried setting the Timeout higher but Im not quite sure how high I can set without putting to much stress on the server.

Suggetions?


The table is over 8 million records and the timeout is currently set to Server.ScriptTimeout = 160 and just in case this is the query


sqlString = "SELECT A.UserID, A.DepartmentId, A.UnitID, A.ActionDate, A.ActionTaken, A.ToDepartmentID, A.ToUnitID, A.AccountNumber, A.DocumentIDs, A.UnitRecvdDate"
sqlString = sqlString & ", B.UserID, B.FirstName, B.LastName, C.DepartmentName, D.UnitName "
sqlString = sqlString & " FROM (((Users B INNER JOIN WorkObjectAuditLogFull A ON A.UserID = B.UserID) INNER JOIN Departments C ON C.DepartmentID = B.DepartmentID) Left Outer JOIN Units D ON (D.DepartmentID = C.DepartmentID) AND (D.UnitID = A.UnitID))"
sqlString = sqlString & " WHERE(A.WorkObjectID)='" & WI & "'"
sqlString = sqlString & " Order by A.ActionDate" AJ
I would lose my head if it wasn't attached.

 

[JJ26]

maybe you need to build up index in each of your table.

table Users -- indexing in UserID, DepartmentID
table WorkObjectAuditLogFull -- indexing in UserID, UnitID
table Departments -- indexing in DepartmentID
table Units -- indexing in UnitID, DepartmentID


(((Users B INNER JOIN WorkObjectAuditLogFull A ON A.UserID = B.UserID) INNER JOIN Departments C ON C.DepartmentID = B.DepartmentID) Left Outer JOIN Units D ON (D.DepartmentID = C.DepartmentID) AND (D.UnitID = A.UnitID))"