Editor - Query Performance

kmbonin

Having a new issue with a controller that I've been using for a while. In my query controller I have several fields and a few left joins. I also have 5 database functions that the controller calls and uses. I've noticed that after recently adding the 5th function, performance has significantly worsened on my API call - 1.9 seconds in the past to 11.49 seconds now for only 65 records. I've isolated it to this 5th function as the issue. When I run the full query taken from the DTE debug output in the API in Sql Server Management Studio, the whole query (the same 65 records) runs in less than one millisecond.

This function differs from the other four in that it creates a temporary variable table, inserts the records, does a calculation, returns the float result. The other four do a basic lookup.

I'm running DTE 1.10.4 / ASP.NET/C#. Below is the controller code with the problematic function isolated.

using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "logan_dvpr", "dvprID")
                    .Debug(true)
                    .Model<DVPRDataModel>()
                    .Field(new Field("logan_dvpr.dvprID"))
                    .Field(new Field("logan_dvpr.dvprNumber"))
                    .Field(new Field("logan_dvpr.jobNumber"))
                    .Field(new Field("logan_dvpr.customerDesc"))
                    .Field(new Field("logan_dvpr.percentReleased"))
                    .Field(new Field("logan_dvpr.percentProcured"))
                    .Field(new Field("logan_dvpr.rd"))
                    .Field(new Field("logan_dvpr.dvprCompleted"))                    
                    .Field(new Field("dbo.fn_getDVPRTimeE(dvprID) as estHours"))
                    .Field(new Field("dbo.fn_getDVPRTimeC(dvprID) as compHours"))
                    .Field(new Field("dbo.fn_getDVPRColorYN(dvprID) as colorYN"))
                    .Field(new Field("dbo.fn_getTaskStatus(dvprID) as taskStatus"))
                    
                    .Field(new Field("dbo.fn_getDVPRTime(dvprID) as pcomp"))
                    
                    .Field(new Field("logan_dvpr.totalHoursAccrued"))
                    .Field(new Field("logan_dvpr.estTime"))
                    .Field(new Field("logan_dvpr.deliveryDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_dvpr.estStartDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))
                    .Field(new Field("logan_dvpr.engTeamLead")                        
                        .Options("logan_users_master", "userID_DVPR", "fullName", q=>q.Where("deptID_DVPR", "2"))
                        .Validator(Validation.DbValues()))
                     .Field(new Field("logan_dvpr.projEngineer")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "2"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.leadDesignEng")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "2"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.hydSupport")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "2"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.elecSupport")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "2"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.mechSupport")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "2"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.projectManager")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "9"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.salesman")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "12"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.qualityRep")
                        .Options("logan_users_master", "userID_DVPR", "fullName", q => q.Where("deptID_DVPR", "10"))
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_dvpr.statusID")
                        .Options("logan_status_dvpr", "statusID", "status")
                        .Validator(Validation.DbValues()))                    
                    .Field(new Field("logan_dvpr.estCompDate")
                        .GetFormatter(Format.DateSqlToFormat("MM/d/yyyy"))
                        .SetFormatter(Format.DateFormatToSql("MM/d/yyyy")))                    
                    .Field(new Field("etl.fullName as etlName"))
                    .Field(new Field("pe.fullName as peName"))
                    .Field(new Field("lde.fullName as ldeName"))
                    .Field(new Field("hs.fullName as hsName"))
                    .Field(new Field("es.fullName as esName"))
                    .Field(new Field("ms.fullName as msName"))
                    .Field(new Field("pm.fullName as pmName"))
                    .Field(new Field("logan_status_dvpr.status"))
                    .Field(new Field("logan_status_dvpr.displayColor"))
                    .Field(new Field("logan_dvpr.lqlID")
                        .Options("logan_DocQualityLevel", "levelID", "levelName")
                        .Validator(Validation.DbValues()))
                    .Field(new Field("logan_DocQualityLevel.levelName"))
                    
                    .LeftJoin("logan_status_dvpr", "logan_status_dvpr.statusID", "=", "logan_dvpr.statusID")
                    .LeftJoin("logan_DocQualityLevel", "logan_DocQualityLevel.levelID", "=", "logan_dvpr.LQLID")
                    .LeftJoin("logan_users_master etl", "etl.userID_DVPR", "=", "logan_dvpr.engTeamLead")
                    .LeftJoin("logan_users_master pe", "pe.userID_DVPR", "=", "logan_dvpr.projEngineer")
                    .LeftJoin("logan_users_master lde", "lde.userID_DVPR", "=", "logan_dvpr.leaddesigneng")
                    .LeftJoin("logan_users_master hs", "hs.userID_DVPR", "=", "logan_dvpr.hydsupport")
                    .LeftJoin("logan_users_master es", "es.userID_DVPR", "=", "logan_dvpr.elecsupport")
                    .LeftJoin("logan_users_master ms", "ms.userID_DVPR", "=", "logan_dvpr.mechsupport")
                    .LeftJoin("logan_users_master pm", "pm.userID_DVPR", "=", "logan_dvpr.projectmanager")
                    .Where("logan_dvpr.statusID", "10", "<>")
                    .Where("logan_dvpr.dvprCompleted", "0", "=");

Here is the code for the function:

DECLARE @Result float

    DECLARE @tempTable table (taskID int, pcomp float, estTime int);
    
    --Grab %Complete for each task in this dvpr
    insert into @tempTable (taskID, pcomp, estTime)
    Select ts.taskID,
        CASE 
            WHEN taskCompleteYN = 'Y' THEN 1
            WHEN estTime = 0 or estTime IS NULL THEN 0
            WHEN taskCompleteYN <> 'Y' AND esttime is not null AND (SUM(datediff(second,timein,timeout)) / 3600.0)/ esttime < .75 THEN (SUM(datediff(second,timein,timeout)) / 3600.0) / esttime
            WHEN taskCompleteYN <> 'Y' AND esttime is not null AND (SUM(datediff(second,timein,timeout)) / 3600.0)/ esttime >= .75 THEN .75         
            ELSE 0
        END AS Task_PCOMP,      
        ISNULL(ts.estTime, 1) as estTime        
        from  logan_dvprTasks ts
        left join logan_dvpr_timelog t on t.taskID = ts.taskID
        where ts.statusID <> 10 
        and ts.dvprID = @dvprID
        group by ts.taskID, ts.EstTime, ts.taskCompleteYN, ts.timed_yn

    --Now calculate the average of the %complete values
    SELECT @Result = ROUND( (SUM(estTime * pcomp)) / (SUM(estTime)) * 100 , 0) 
    from @tempTable
    where estTime <> 0 AND estTime is not null

    RETURN @Result

Why is this function causing so much of a headache when it runs quickly in the database? Thanks in advance.

allan

I'm not actually clear on which line is calling the function as I don't know what the function is called. Could you clarify that for me please?

Also, have you added .Debug(true) to your Editor method chain (just before the .Process() call)? If not, could you do so and then show me the SQL that the server is returning as part of the JSON.

Allan

kmbonin

No problem. The calling function is:

.Field(new Field("dbo.fn_getDVPRTime(dvprID) as pcomp"))

Basically, the controller is puling a list of task lists. Each list has its own list of tasks. This function takes the tasks for a given list and computes the percent complete of the overall list.

Here's the debug query:

"debugSql":[{"Query":"SELECT  [logan_dvpr].[dvprID] as 'logan_dvpr.dvprID', [logan_dvpr].[dvprID] as 'logan_dvpr.dvprID', [logan_dvpr].[dvprNumber] as 'logan_dvpr.dvprNumber', [logan_dvpr].[jobNumber] as 'logan_dvpr.jobNumber', [logan_dvpr].[customerDesc] as 'logan_dvpr.customerDesc', [logan_dvpr].[percentReleased] as 'logan_dvpr.percentReleased', [logan_dvpr].[percentProcured] as 'logan_dvpr.percentProcured', [logan_dvpr].[rd] as 'logan_dvpr.rd', [logan_dvpr].[dvprCompleted] as 'logan_dvpr.dvprCompleted', dbo.fn_getDVPRTimeE(dvprID) as 'dbo.fn_getDVPRTimeE(dvprID)', dbo.fn_getDVPRTimeC(dvprID) as 'dbo.fn_getDVPRTimeC(dvprID)', dbo.fn_getDVPRColorYN(dvprID) as 'dbo.fn_getDVPRColorYN(dvprID)', dbo.fn_getTaskStatus(dvprID) as 'dbo.fn_getTaskStatus(dvprID)', dbo.fn_getDVPRTime(dvprID) as 'dbo.fn_getDVPRTime(dvprID)', [logan_dvpr].[totalHoursAccrued] as 'logan_dvpr.totalHoursAccrued', [logan_dvpr].[estTime] as 'logan_dvpr.estTime', [logan_dvpr].[deliveryDate] as 'logan_dvpr.deliveryDate', [logan_dvpr].[estStartDate] as 'logan_dvpr.estStartDate', [logan_dvpr].[engTeamLead] as 'logan_dvpr.engTeamLead', [logan_dvpr].[projEngineer] as 'logan_dvpr.projEngineer', [logan_dvpr].[leadDesignEng] as 'logan_dvpr.leadDesignEng', [logan_dvpr].[hydSupport] as 'logan_dvpr.hydSupport', [logan_dvpr].[elecSupport] as 'logan_dvpr.elecSupport', [logan_dvpr].[mechSupport] as 'logan_dvpr.mechSupport', [logan_dvpr].[projectManager] as 'logan_dvpr.projectManager', [logan_dvpr].[salesman] as 'logan_dvpr.salesman', [logan_dvpr].[qualityRep] as 'logan_dvpr.qualityRep', [logan_dvpr].[statusID] as 'logan_dvpr.statusID', [logan_dvpr].[estCompDate] as 'logan_dvpr.estCompDate', [etl].[fullName] as 'etl.fullName', [pe].[fullName] as 'pe.fullName', [lde].[fullName] as 'lde.fullName', [hs].[fullName] as 'hs.fullName', [es].[fullName] as 'es.fullName', [ms].[fullName] as 'ms.fullName', [pm].[fullName] as 'pm.fullName', [logan_status_dvpr].[status] as 'logan_status_dvpr.status', [logan_status_dvpr].[displayColor] as 'logan_status_dvpr.displayColor', [logan_dvpr].[lqlID] as 'logan_dvpr.lqlID', [logan_DocQualityLevel].[levelName] as 'logan_DocQualityLevel.levelName', [jobNumber] as 'jobNumber', [customerDesc] as 'customerDesc', [deliveryDate] as 'deliveryDate', [RD] as 'RD', [engTeamLead] as 'engTeamLead', [projEngineer] as 'projEngineer', [leadDesignEng] as 'leadDesignEng', [hydSupport] as 'hydSupport', [elecSupport] as 'elecSupport', [mechSupport] as 'mechSupport', [ProjectManager] as 'ProjectManager' FROM  logan_dvpr LEFT JOIN [logan_status_dvpr] ON [logan_status_dvpr].[statusID] = [logan_dvpr].[statusID]  LEFT JOIN [logan_DocQualityLevel] ON [logan_DocQualityLevel].[levelID] = [logan_dvpr].[LQLID]  LEFT JOIN logan_users_master etl ON [etl].[userID_DVPR] = [logan_dvpr].[engTeamLead]  LEFT JOIN logan_users_master pe ON [pe].[userID_DVPR] = [logan_dvpr].[projEngineer]  LEFT JOIN logan_users_master lde ON [lde].[userID_DVPR] = [logan_dvpr].[leaddesigneng]  LEFT JOIN logan_users_master hs ON [hs].[userID_DVPR] = [logan_dvpr].[hydsupport]  LEFT JOIN logan_users_master es ON [es].[userID_DVPR] = [logan_dvpr].[elecsupport]  LEFT JOIN logan_users_master ms ON [ms].[userID_DVPR] = [logan_dvpr].[mechsupport]  LEFT JOIN logan_users_master pm ON [pm].[userID_DVPR] = [logan_dvpr].[projectmanager] WHERE [logan_dvpr].[statusID] <> @where_0 AND [logan_dvpr].[dvprCompleted] = @where_1 ","Bindings":[{"Name":"@where_0","Value":"10","Type":null},{"Name":"@where_1","Value":"0","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"2","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"2","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"2","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"2","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"2","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"2","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"9","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"12","Type":null}]},{"Query":"SELECT DISTINCT  [fullName] as 'fullName', [userID_DVPR] as 'userID_DVPR' FROM  logan_users_master WHERE ([deptID_DVPR] = @where_1 )","Bindings":[{"Name":"@where_1","Value":"10","Type":null}]},{"Query":"SELECT DISTINCT  [status] as 'status', [statusID] as 'statusID' FROM  logan_status_dvpr ","Bindings":[]},{"Query":"SELECT DISTINCT  [levelName] as 'levelName', [levelID] as 'levelID' FROM  logan_DocQualityLevel ","Bindings":[]}]}

kmbonin

So interestingly enough, the same query is now running in 892ms in the API as of this morning. I haven't changed anything on my end - was an update made?

kmbonin

Hi, the query is back to running slow. Same query as before. It was fixed last time I'm thinking by someone on your side because I didn't do anything on mine. Is there anything we can do to speed it up again? It's taking 20-30 seconds to load.

allan

was an update made?

Nope. And we certainly wouldn't be able to change anything on your server!

Possibly its due to load on the server?

Allan