0

I have used the below stored procedure in drop down selected index changed event.

create  function get_timesheetentrydetails(tasktypeid int, projectbatchlotid int)
 returns table (workpackageid integer, workpackagename varchar(500), taskid int, taskname varchar(500), taskdesc varchar(500), previoustaskid int, 
                previoustaskname varchar(500), previoustaskdesc varchar(500), esthrs numeric, empid varchar(200))  as $$
begin
return query  select wp.id,wp.code,wp.taskid,t.name,t.description,wp.previoustaskid,t1.name as previoustaskname,t1.description as previoustaskdesc,
   wp.estimatehours ,u.name
     from workpackage wp
   inner join task t on wp.taskid=t.id 
   inner join task t1 on wp.previoustaskid = t1.id
   inner join userprofile u on wp.lastupdatedby = u.id
   and wp.projectbatchlotid=$2 and t.tasktypeid=$1 and wp.status in ('TBA','inprocess')
   and not exists (select id from timesheet where timesheet.workpackageid=wp.id and endtime is null);
end;
$$ language plpgsql;

In the database server this procedure executes in 450ms but when I am calling from user interface if numberof users and data incresses for execution it is taking 60 sec. Colud you please help me how to slove this issue as early as possible.

The below code is in dropdown selected index changed event.

ddlTask.Enabled = true;

DataTable dtbTasks = getTSTasks(rbnCurrentTask.SelectedItem.Value, ddlLotNo.SelectedItem.Value);

Master.bindDropDownList(ddlTask, dtbTasks, "taskdesc", "taskid");

// cascadeSelection(this, "ddlTask");

if (rbnCurrentTask.SelectedItem.Text == "Project Management")
{
    DataTable dtb = getPMTasks();
    Master.bindDropDownList(ddlTask, dtb, "taskcode", "taskid");
}
if (rbnCurrentTask.SelectedItem.Text == "Training")
{
    DataTable dtbTraining = getTrainingTasks();
    Master.bindDropDownList(ddlTask, dtbTraining, "taskcode", "taskid");
}
lebelinoz
  • 4,890
  • 10
  • 33
  • 56
srinu
  • 51
  • 1
  • 1
  • 3
  • Is it slow in the same way when you execute the used query on your own in a database tool? Then you are probably searching for some indices to be used – Nico Haase Mar 23 '18 at 13:36
  • Hi Nico Haase, Thanks for your quik reply. Can you please let me know what is indices and how to be used it to my stored procedure or in my application. – srinu Mar 23 '18 at 13:48
  • 1
    As said, you need to see whether you can speed the query performance with some indices. See this SO answer for some postgres-related profiling stuff https://stackoverflow.com/questions/2430380/is-there-a-postgresql-equivalent-of-sql-server-profiler – GPW Mar 23 '18 at 13:48
  • 1
    @srinu indices are things defined in the database to help it know how to relate and search information. correctly defined indices can make queries hundreds of times faster in some cases. I've not personally used PostGres so can't give much more info, but all Databases have the concept. – GPW Mar 23 '18 at 13:50
  • Thanks Nico, I have used PGAdmin4. Request you to update if you got the concept for PGAdmin4 indices. Thanks in advance – srinu Mar 23 '18 at 13:53
  • I have not used any indices concept to my stored procedures. Is this possible to create this time to my stored procedures. – srinu Mar 23 '18 at 13:57
  • You can't just use them in a stored procedure. They are defined as part of the table. Profiling (which you may be able to do with PGAdmin4, but I don't have any experience with this tool) *should* be able to inform you of where an index may help, but a full description of this is well outside the scope of a Stack Overflow Question – GPW Mar 23 '18 at 14:30
  • You said the query takes 450ms on the database server. Is this the same server as is being used for the UI, and is the database the same size? you should probably start with verifying that it is definitely running the query on the database that takes the time, rather than something else in your code. – GPW Mar 23 '18 at 14:33

0 Answers0