Stored Procedure and Functions to speed up Backend Query

Stored Procedure and Functions to speed up Backend Query

Easy way to create stored procedure:-

  1. Open SQL Management Studio. Goto Database and click on Programmability. Do a right click on Stored Procedure choose stored procedure. You should see template stored procedure
  2. StoredProcedureYou notice <Procedure_Name, sysname, ProcedureName> this where u need to change as the name of stored procedure. It should be something like this dbo.NameofStoredProcedure
  3. The second bit you need to change is the query of the sql -> SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
    • This can be the actually join query.
  4. Leave the rest of the stored procedure. as same and run the query by clicking the “Execute” button. This should create your new stored procedure. One thing to note is that the stored procedure will initially have “CREATE dbo.NameofStoredProcedure “
  5. After you create your stored procedure you will see the “ALTER dbo.NameofStoredProcedure”

Same procedure is used to create a function except for few points

  1. Open SQL Management Studio
  2. Click on Functions –> you have also determined whether your function is to return scalar –> single value or table result which more than one result or table result
  3. Function
  4. After you create your function you will see the “ALTER dbo.NameofStoredProcedure”

I had to change complex join query using both stored and functions

public JsonResult GetAvailbleStaffs()
       {
 var availableStaffs =
               (from u in _userManagementService.GetAllUsers().ToList()
                join p in _personService.GetAll() on u.Id equals p.Id
                join pr in _profileService.GetAllProfile() on p.ProfileId equals pr.Id
                where u.Roles.Count > 0 && u.Roles.ToList()[0].RoleId == 3
                select new
                {
                    Id = u.Id,
                    FirstName = pr.FirstName,
                    LastName = pr.LastName
                }
                ).ToList();

           var queues =
               (from q in _queService.GetAll()
                where q.QueTypeId == 2 || q.QueTypeId == 3 || q.QueTypeId == 5
                select new
                {
                    staffId = q.Staff
                }
                ).ToList();

           var freeStaff = 
                (from s in availableStaffs
                  join q in queues on s.Id equals q.staffId
                  into a
                 from subq in a.DefaultIfEmpty()
                 where subq == null
                Select new
                {
                    Id = s.Id,
                    FirstName = s.FirstName,
                    LastName = s.LastName,
                    FullName = s.FirstName + " " + s.LastName
                    //isAvailable = subq == null
                }
                )
                .Where(x => x.isAvailable == true)
                .ToList();
               return Json(freeStaff, JsonRequestBehavior.AllowGet);

       }

this was changed to single stored procedure with 2 functions

public JsonResult GetAvailbleStaffs()
 {
     var freeStaff = _cobraEntities.AvailableStaff().
          Select(s => new
          {
              Id = s.Id,
              FirstName = s.FirstName,
              LastName = s.LastName,
              FullName = s.FirstName + " " + s.LastName
              //isAvailable = subq == null
          }
          )
          //.Where(x => x.isAvailable == true)
          .ToList();

     return Json(freeStaff, JsonRequestBehavior.AllowGet);

 }

Stored Procedure AvaliableStaff

-- =============================================
-- Author:		Jack 
-- Create date: 08 August 2017
-- Description:	Available Staff - Find the Staff that not assigned a Task
-- Uses function ListonRoleId(3) - Provide a list of all Staff
-- Uses function QueAssigned - Provide a list of Staff that assigned a Task, transfer or unresolved
-- =============================================
ALTER PROCEDURE [dbo].[AvailableStaff] 
  
AS
BEGIN

Select l.Id, l.FirstName, l.LastName from dbo.ListonRoleId(3) as l
 left join dbo.QueAssigned() as q
 on l.Id = q.Staff
 where q.Staff is null

END

Function ListonRoleId

-- =============================================
-- Author:		Jack 
-- Create date: 09 August 2017
-- Description:	ListonRoleId(3) - Function which return a list based on the roleId = 3
-- roleId = 3 --> call centre staff
-- roleId = 4 --> call centre manager
-- =============================================
ALTER FUNCTION [dbo].[ListonRoleId] (@role int)
returns table
return (
  select u.Id, pr.FirstName, pr.LastName  from 
    dbo.Login as u
  join dbo.Person as p
  on u.Id = p.Id
  join dbo.Profile as pr
  on pr.Id = p.ProfileId
  join dbo.LoginRole as lr
  on u.Id = lr.LoginId
  where lr.RoleId = @role and FirstName is not null and LastName is not null
);

Function QueAssigned

-- =============================================
-- Author:		Jack 
-- Create date: 09 August 2017
-- Description:	QueAssigned - Function which return a list based of staff who has been assigned a task, or transfer or unresolved
-- QueTypeId = 2 --> assigned
-- QueTypeId = 3 --> transfer
-- QueTypeId = 5 --> unresolved
-- =============================================
ALTER FUNCTION [dbo].[QueAssigned] ()
returns table
return (
  select q.Id, q.Staff, q.QueTypeId, q.GeoFenceId  from Que as q
  --quetype = 1 unassigned 
  where q.QueTypeId = 2 or q.QueTypeId = 3 or q.QueTypeId = 5
);

The overall results was that backend query was much faster.

Leave a Reply

Your email address will not be published. Required fields are marked *