Query Timeout 1

[enak]

I have a query that times out after about 20 seconds. I am not a DBA so that may be part of the problem. Here is the SQL that I pass to a COM object:

Select eh.EvaluatorID as id,
(select fullName from vwEmployeefull as vw where vw.TimekeeperID = Max(eh.Evaluatorid)) as Name,
(select EvalType from EvalSetup as es where es.EvalID = max(eh.Evalid)) as EvalName,
(select OfficeAbbr from vwEmployeeFull as vw where vw.timekeeperID = Max(eh.EvalueeID)) as Office,
(select Department from akDataTransfer.dbo.HRDepartment as hr where hr.DeptID = Max(eh.DepartmentID)) as Department,
(select count(*) from evalheader where evaluatorid = eh.evaluatorid and complete='Y' and evalid=53) as Complete,
(select count(*) from evalheader where evaluatorid = eh.evaluatorid and complete='I' and evalid=53) as InProgress,
(select count(*) from evalheader where evaluatorid = eh.evaluatorid and complete='N' and evalid=53) as NotStarted
from evalheader as eh, vwEmployeeFull as vw
where eh.evalid = 53 group by eh.EvaluatorID order by Name

It should return less than 1200 records.

Here is the VB code in the COM object that executes the query:

If Not getConnection(strErr) Then
GoTo ErrHdl
Else
cnn.Execute strSQL
dbNoReturnRecordset = True
End If


This all takes place in an ASP form. The data is displayed on the ASP page.

If I filter the data so that less records are returned everything works fine.

Any help would be appreciated.

TIA
Nate

 

[RobHudson]

Hi Nate!

How long does the query take to run in Query Analyzer? I believe the default timeout for an ADO connection in VB is 30 seconds. You could try setting your timeout higher and then resetting it back once the job has completed??

Hope that helps
Rob

 

[enak]

I was looking at that and it is taking about 33 seconds. I increased the connection timeout and that fixed the problem for now.

However, I am concerned that the query is not very well written. Is there anything that I can do to optimize the query?

There are indexes on the id fields which are indentities.

Thanks
Nate

 

[RobHudson]

Sorry Nate, no magical ideas
All I can say is that I find views really slow. So try to limit there use as much as poss!!

Might it be quicker to write all the sub queries as User Defined Functions? Maybe other users of this forum may know if that would be quicker???

Unless I'm missing the plot in your main FROM clause [from evalheader as eh, vwEmployeeFull as vw] is there any need for vwEmployeeFull?

Good luck
Rob

 

[enak]

I did find one error that significantly increased the speed. That was I had forgotten to join the two tables in the main statement.

 

[nirs]

try to use the command object to run the sql statment

Dim oCmd As New ADODB.Command
Dim oConn As New ADODB.Connection
Dim strSQL As String

oConn.Open "DSN=;UID=;PWD=;"
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = strSQL
oCmd.CommandType = adCmdText
oCmd.CommandTimeout = 40
Set oRSRate = oCmd.Execute()


now the time out is set to 40
i think that the connection time-out is the max time that the client will try to connect to the db and not for running commands

 

[zhavic]

As 'RobHudson' says ' [from evalheader as eh, vwEmployeeFull as vw] is there any need for vwEmployeeFull?'

I look at the statement, and I ensure that the answer is NO.
Try this, if will work, may be little faster

Select eh.EvaluatorID as id,
(select fullName from vwEmployeefull as vw where vw.TimekeeperID = Max(eh.Evaluatorid)) as Name,
(select EvalType from EvalSetup as es where es.EvalID = max(eh.Evalid)) as EvalName,
(select OfficeAbbr from vwEmployeeFull as vw where vw.timekeeperID = Max(eh.EvalueeID)) as Office,
(select Department from akDataTransfer.dbo.HRDepartment as hr where hr.DeptID = Max(eh.DepartmentID)) as Department,
SUM( CASE WHEN eh.complete='Y' THEN 1 ELSE 0 ) as Complete,
SUM( CASE WHEN eh.complete='I' THEN 1 ELSE 0 ) as InProgress,
SUM( CASE WHEN eh.complete='N' THEN 1 ELSE 0 ) as NotStarted
from evalheader as eh
where eh.evalid = 53 group by eh.EvaluatorID order by Name

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[enak]

Zhavic, you are right. I completely missed that. Thanks!