17 Replies Latest reply on Apr 24, 2013 11:40 AM by anshuman

    PI OLEDB Data retrieval too slow

    anshuman

      Hi,

       

      I have written a web service which connects to the PI server through its OLEDB Provider interface and fetches data from 3 different tables as is show in the below written SQL query:--

       

      "SELECT (SELECT pm.name FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid = u.moduleuid) pname, (SELECT name FROM pimodule..pimodule WHERE uid = u.moduleuid) unit, u.moduleuid, u.batchid, ual.tag, ual.name FROM pibatch..piunitbatch u INNER JOIN pimodule..pialias ual  ON ual.moduleuid= u.moduleuid"

       

      When I run this query using PI OLEDB MMC Snaap In Utility, it takes 22 seconds tie to return the data. And it returns around 100 rows. The result's snapshot is attached with this thread.

       

      But when I run the web service which also internally runs this same SQL query, it takes around 150 seconds to return the same set of results. I am also attaching the source code.

       

      Any reason???

       

       

       

      Thanks and regards,

       

      Anshuman

       

      0576.oledb_5F00_snapin.png

       

       

       
      using PIServicesWrapper.Models;
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Net;
      using System.Net.Http;
      using System.Web.Http;
      using System.Data.OleDb;
      
      namespace PIServicesWrapper.Controllers
      {
          using PIServicesWrapper.Models;
          using System;
          using System.Collections.Generic;
          using System.Linq;
          using System.Net;
          using System.Net.Http;
          using System.Web.Http;
          using System.Data.OleDb;
      
          public class PIUnitTabFiltersController : ApiController
          {
              private string connString = "";
              private OleDbCommand cmd;
              private OleDbConnection cnn;
              
              public IEnumerable<FilterDataItem> GetAllPIUnitTabFilters(PIUnitTabInputItem inputObj)
              {
                  if (inputObj == null)
                  {
                      PIUnitTabInputItem inputObj0 = new PIUnitTabInputItem { server_ip = "192.168.1.2", server_name = "simplyfeye" };
                      inputObj = inputObj0;
                  }
                  List<FilterDataItem> itemsList = new List<FilterDataItem>();
                  List<string> inArray = new List<string>();
                  List<string> inArray2 = new List<string>();
                  FilterDataItem tmpItm;
      
                  string server_ip = inputObj.server_ip;
                  string server_name = inputObj.server_name;
                     
                  connString = "Provider=PIOLEDB; Data Source=" + server_ip + "; Integrated Security=SSPI;";
                     
                  string lineSpeedSql = string.Format("SELECT (SELECT pm.name FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid = u.moduleuid) pname, (SELECT name FROM pimodule..pimodule WHERE uid = u.moduleuid) unit, u.moduleuid, u.batchid, ual.tag, ual.name FROM pibatch..piunitbatch u INNER JOIN pimodule..pialias ual  ON ual.moduleuid= u.moduleuid");
                  cnn = new OleDbConnection(connString);
                  
                  cmd = new OleDbCommand();
                  cmd.CommandTimeout =5000;
                  cmd.Connection = cnn;
      
                  try
                  {
                      cnn.Open();
                  }
                  catch (Exception ex)
                  {
                     //do nothing
                  }
      
                  try
                  {
                      cmd.CommandText = lineSpeedSql;
                      OleDbDataReader rdr = cmd.ExecuteReader();
                      try
                      {
                          while (rdr.Read())
                          {
                              string area = rdr.GetString(0);
                              string unit = rdr.GetString(1);
                              string moduleuid = rdr.GetString(2);
                              string batch = rdr.GetString(3);
                              string tag = rdr.GetString(4);
                              string name = rdr.GetString(5);
                              tmpItm = new FilterDataItem {area=area,unit=unit,moduleuid=moduleuid,batch=batch,tagalias=name,tagname=tag};
                              itemsList.Add(tmpItm);
                          }
                      }
                      finally
                      {
                          rdr.Close();
                          cnn.Close();
                          
                      }
      
                  }
                  catch (Exception ex)
                  {
                      //do nothing
                  }
                  return itemsList.ToArray();
              }
          }
      }
       
      

       

       

       

       

       

       

       

       

       

       

       

        • Re: PI OLEDB Data retrieval too slow

          Hello Anshuman,

           

          One major difference is that you are connected to PI already when executing your query in PI OLEDB MMC Snap-In.

           

          The connection string that you are using inside your Web Service contains "Integrated Security=SSPI" what means your security is either based on a PI Trust or Windows Integrated security. What authentication method do you intend to use? Have you confirmed with the PI Server log it is working properly? When your web service is finished, the connection is closed again. Hence you should see a complete trace (incoming connection attempt -> possibly failed authentication -> successful connection -> closed connection) within the PI Servers log. When looking at the timestamps, you get an idea where the time gets lost. Please also look for possible errors for the connection ID of your connection. Something like "No read access. Secure object" may indicate the PI identity used doesn't have appropriate rights.

           

          You can also try to see where the time gets lost i.e. by doing some time measurement within your code or by using a simple query that returns a result set within a second or less in PI OLEDB MMC Snap-In. If the same query takes, let's say about 130 seconds within your web service, this would confirm my suspicion time is lost when establishing connections. 

           

           

            • Re: PI OLEDB Data retrieval too slow
              anshuman

              Hi Gregor,

               

              First of all thank you of your prompt reply as usual. Actually we are doing similar queries to retrieve some other data in other web services but there the behavior is perfectly fine. Its like in those cases OLEDB MMC Snap-In takes say 1 second and our service takes 2-3 seconds. This indicates that the connection establishment part of the code is not the time consuming culprit. Also we are using trust based authentication method in all our web services including this one as well. My suspicion is that this particular query is causing some issue, but if it is so then why the same query is retrieving the same data in just 22 secs  in  MMC Snap-In whereas its taking 150 secs or so in our this particular web service? I will also try to dig into the server log if it can give more details of which part is taking so long.

                • Re: PI OLEDB Data retrieval too slow
                  jlakumb

                  In addition to the PI Server message log, you might want to consider setting up PI OLEDB logging and PI SDK tracing.  These last two should provide additional info on timing from the client side and may help to isolate which particular method(s) is taking the longest.  Please refer to the PI OLEDB and PI SDK user manuals for more information on how to configure this logging...

                    • Re: PI OLEDB Data retrieval too slow
                      anshuman

                      Hi All,

                       

                      The chief culprit was the nested INNER JOIN. PI OLEDB does not handle it very well. I split the query into two parts and separated the nested inner join part into another query and retrieved the data from the PI server in two transactions and finally aggregated both data sets to form a unified data object. The time taken was reduced to 18sec from 150 sec!!!

                        • Re: PI OLEDB Data retrieval too slow

                          Hello Anshuman,

                           

                          I cannot imagine why the same query, once executed in PI OLEDB MMC Snap-In and once in your Web Service application, should be treated differently. I've contacted one of the PI OLEDB Provider developers who is as well curios to understand where the differences are. As suggested by Jay, we would be interested in getting PI OLEDB logs with log level 2 for both of your queries (the initial and the optimized one) from both applications (your web service and OLEDB MMC Snap-In).

                           

                          To enable PI OLEDB logging in OLEDB MMC Snap-In just click the [Options >>] button in the connection dialog, enter a log file path and name and set the log level using the correspondent ComboBox.

                           

                          With your Web Service application, please enable logging within the connection string by adding the definitions separated by semicolon i.e.

                           

                          "Log File = C:\Temp\PIOLEDB01.log; Log Level = 2"   

                           

                           

                           

                           

                           

                           

                           

                           

                           

                           

                            • Re: PI OLEDB Data retrieval too slow
                              anshuman

                              Hi Gregor,

                               

                               

                               

                              I am attaching the PIOLEDBlog1.log file as requested by you with this message.

                               

                              Edit Gregor: Log file content removed

                                • Re: PI OLEDB Data retrieval too slow
                                  anshuman

                                  The mmc snap-in log file is pasted in-line below:-

                                   

                                  Edit Gregor: Log file content removed

                                    • Re: PI OLEDB Data retrieval too slow
                                      anshuman

                                       The optimized code is written below. I have split the complicated query into two separate ones.

                                       
                                      using PIServicesWrapper.Models;
                                      using System;
                                      using System.Collections.Generic;
                                      using System.Linq;
                                      using System.Net;
                                      using System.Net.Http;
                                      using System.Web.Http;
                                      using System.Data.OleDb;
                                      
                                      namespace PIServicesWrapper.Controllers
                                      {
                                          using PIServicesWrapper.Models;
                                          using System;
                                          using System.Collections.Generic;
                                          using System.Linq;
                                          using System.Net;
                                          using System.Net.Http;
                                          using System.Web.Http;
                                          using System.Data.OleDb;
                                      
                                          public class PIUnitTabFiltersController : ApiController
                                          {
                                              //private string connString = "Provider=PIOLEDB; Data Source=simplyfeye; Integrated Security=SSPI;Persist Security Info=false;";
                                              private string connString = "";
                                              private OleDbCommand cmd;
                                              private OleDbConnection cnn;
                                              private List<FilterDataItem> UnitsList = new List<FilterDataItem>();
                                      
                                              private string getItemDetails(string moduleuid)
                                              {
                                                  int arrLength = this.UnitsList.Count;
                                                  for (int i = 0; i < arrLength; i++)
                                                  {
                                                      if (moduleuid == this.UnitsList
                                      .moduleuid)
                                                      {
                                                          return this.UnitsList
                                      .area;
                                                      }
                                                  }
                                                  return null;
                                              }
                                              public IEnumerable<FilterDataItem> GetAllPIUnitTabFilters(PIUnitTabInputItem inputObj)
                                              {
                                                  if (inputObj == null)
                                                  {
                                                      PIUnitTabInputItem inputObj0 = new PIUnitTabInputItem { server_ip = "192.168.1.2", server_name = "simplyfeye", starttime = "01-Mar-2013 17:12:00", endtime = "*" };
                                                      inputObj = inputObj0;
                                                  }
                                                  List<FilterDataItem> itemsList = new List<FilterDataItem>();
                                                  List<FilterDataItem> finalOutputList = new List<FilterDataItem>();
                                                  List<string> inArray = new List<string>();
                                                  List<string> inArray2 = new List<string>();
                                                  List<string> signaturessArray = new List<string>();
                                                  FilterDataItem tmpItm;
                                      
                                                  string server_ip = inputObj.server_ip;
                                                  string server_name = inputObj.server_name;
                                                  string mystarttime = inputObj.starttime;
                                                  string myendtime = inputObj.endtime;
                                                  connString = "Provider=PIOLEDB; Data Source=" + server_ip + "; Integrated Security=SSPI;";
                                                  //string lineSpeedSql = string.Format("SELECT (SELECT pm.name FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid = u.moduleuid) pname, (SELECT name FROM pimodule..pimodule WHERE uid = u.moduleuid) unit, u.moduleuid, u.batchid, ual.tag, ual.name FROM pibatch..piunitbatch u INNER JOIN pimodule..pialias ual  ON ual.moduleuid= u.moduleuid WHERE u.starttime BETWEEN '" + mystarttime + "' AND '" + myendtime + "'");
                                                  //string lineSpeedSql = string.Format("SELECT (SELECT pm.name FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid = u.moduleuid) pname, (SELECT name FROM pimodule..pimodule WHERE uid = u.moduleuid) unit, moduleuid, batchid FROM pibatch..piunitbatch u");
                                                  string lineSpeedSql = string.Format("SELECT (SELECT name FROM pimodule..pimodule WHERE uid = u.moduleuid) unit, u.moduleuid, u.batchid, ual.tag, ual.name FROM pibatch..piunitbatch u INNER JOIN pimodule..pialias ual ON ual.moduleuid= u.moduleuid");
                                                  //string lineSpeedSql = string.Format("SELECT (SELECT pm.name FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid = u.moduleuid) pname, (SELECT name FROM pimodule..pimodule WHERE uid = u.moduleuid) unit, moduleuid, batchid FROM pibatch..piunitbatch u WHERE u.starttime BETWEEN '"+mystarttime+"' AND '"+myendtime+"'");
                                                  cnn = new OleDbConnection(connString);
                                                  
                                                  cmd = new OleDbCommand();
                                                  cmd.CommandTimeout =5000;
                                                  cmd.Connection = cnn;
                                      
                                                  try
                                                  {
                                                      cnn.Open();
                                                  }
                                                  catch (Exception ex)
                                                  {
                                                      //could not connect to the pi server
                                                      //inArray.Add("error1");
                                                      //outputItems.Add(ex.StackTrace);
                                      
                                                  }
                                      
                                                  try
                                                  {
                                                      cmd.CommandText = lineSpeedSql;
                                                      OleDbDataReader rdr = cmd.ExecuteReader();
                                                      try
                                                      {
                                                          while (rdr.Read())
                                                          {
                                                              /*string area = rdr.GetString(0);
                                                              string unit = rdr.GetString(1);
                                                              string moduleuid = rdr.GetString(2);
                                                              string batch = rdr.GetString(3);
                                                              string tag = rdr.GetString(4);
                                                              string name = rdr.GetString(5);*/
                                      
                                                              //string area = rdr.GetString(0);
                                                              string unit = rdr.GetString(0);
                                                              string moduleuid = rdr.GetString(1);
                                                              string batch = rdr.GetString(2);
                                                              string tag = rdr.GetString(3);
                                                              string name = rdr.GetString(4);
                                                             // tmpItm = new FilterDataItem {area=area,unit=unit,moduleuid=moduleuid,batch=batch,tagalias=name,tagname=tag};
                                                              tmpItm = new FilterDataItem { area = "", unit = unit, moduleuid = moduleuid, batch = batch, tagalias = name, tagname = tag };
                                      
                                                              
                                                              itemsList.Add(tmpItm);
                                                              inArray.Add(moduleuid);
                                      
                                                          }
                                                      }
                                                      finally
                                                      {
                                                          rdr.Close();
                                                          //cnn.Close();
                                                          //
                                                          string lineSpeedSql2 = string.Format("SELECT pm.name, pmh.uid FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid IN ('" + string.Join("','", inArray.ToArray().Distinct()) + "')");
                                      
                                                          //string lineSpeedSql2 = string.Format("SELECT pm.name, pmh.uid FROM pimodule..pimodule pm INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid WHERE pmh.uid IN (SELECT moduleuid pibatch..piunitbatch WHERE 1=1");
                                                          cmd.CommandText = lineSpeedSql2;
                                                          
                                                          OleDbDataReader rdr2 = cmd.ExecuteReader();
                                                          try
                                                          {
                                                              while (rdr2.Read())
                                                              {
                                                                  string area = rdr2.GetString(0);
                                                                  string moduleuid2 = rdr2.GetString(1);
                                                                  //FilterDataItem myitm = getItemDetails(moduleuid2);
                                                                  FilterDataItem myitm = new FilterDataItem();
                                                                  myitm.area = area;
                                                                  myitm.moduleuid = moduleuid2;
                                                                  //myitm.tagalias = name;
                                                                  UnitsList.Add(myitm);
                                                                  //inArray2.Add("Tag:-" + tag + " Name:- " + name + " ModuleUID:- " + moduleuid2 + " Area:- " + myitm.area+" unit:- "+myitm.unit+" Batch:-"+myitm.batch);
                                                              }
                                                          }
                                                          finally
                                                          {
                                                              rdr2.Close();
                                                              cnn.Close();
                                      
                                                              int arrLength = itemsList.Count;
                                                              string prevSignature = "";
                                                              for (int i = 0; i < arrLength; i++)
                                                              {
                                                                  string signature = itemsList
                                      .tagname + "||" + itemsList
                                      .area + "||" + itemsList
                                      .unit + "||" + itemsList
                                      .moduleuid;
                                                                  int pos = Array.IndexOf(signaturessArray.ToArray(), signature);
                                                                  if (pos > -1)
                                                                  {
                                                                      // the array contains the string and the pos variable
                                                                      // will have its position in the array
                                                                  }
                                                                  else 
                                                                  {
                                                                      FilterDataItem tmpItem = new FilterDataItem();
                                                                      tmpItem.moduleuid = itemsList
                                      .moduleuid;
                                                                      tmpItem.area = getItemDetails(tmpItem.moduleuid);
                                                                      tmpItem.unit = itemsList
                                      .unit;
                                                                      tmpItem.batch = itemsList
                                      .batch;
                                                                      tmpItem.tagname = itemsList
                                      .tagname;
                                                                      tmpItem.tagalias = itemsList
                                      .tagalias;
                                      
                                                                      finalOutputList.Add(tmpItem);
                                                                      prevSignature = signature;
                                                                      signaturessArray.Add(signature);
                                                                  }
                                                                  
                                                              }
                                      
                                                          }
                                                      }
                                      
                                                  }
                                                  catch (Exception ex)
                                                  {
                                                      //inArray2.Add("error4"+ex.Message);
                                                  }
                                                  return finalOutputList.ToArray();
                                                  //return inArray2.ToArray().Distinct();
                                              }
                                          }
                                      }
                                       
                                      

                                       

                                        • Re: PI OLEDB Data retrieval too slow
                                          anshuman

                                          And below is the web service log when i run the optimized code:-

                                           

                                          Edit Gregor: Log file content removed

                                           

                                           

                                            • Re: PI OLEDB Data retrieval too slow
                                              Ahmad Fattahi

                                              A friendly suggestion would be to attach the log file as an attachment for better readability. You can do that by using rich formatting and clicking on the Options tab.

                                                • Re: PI OLEDB Data retrieval too slow

                                                  Hello Anshuman,

                                                   

                                                  I am sorry but I cannot even grab the logs from your post. I will edit your post and remove the logs you've pasted in. Please attach the logs as suggested by Ahmad.

                                                    • Re: PI OLEDB Data retrieval too slow
                                                      anshuman

                                                      I have attached the log files. The three files are:-

                                                       

                                                      1. mmclog.log :- log file [level2] for the sql query which was causing the problem in our web service.

                                                       

                                                      2. PIOLEDB01_problem.log:- this is the OLEDB log file [level2] for the same query with nested inner join.

                                                       

                                                      3. PIOLEDB01.log:- this is the oledb log file for the modified code with better performance.

                                                       

                                                      --anshuman

                                                       

                                                      Edit Gregor: Log files attached and description added

                                                        • Re: PI OLEDB Data retrieval too slow
                                                          anshuman

                                                          Please send me your email ID as i can not upload the log files even after trying many browsers.

                                                           

                                                           

                                                           

                                                          regards,

                                                           

                                                          anshuman

                                                            • Re: PI OLEDB Data retrieval too slow

                                                              Hello Anshuman,

                                                               

                                                              I have attached the logs on behalf of you without seeing any issue. I've also edited the belonging post. If you are facing issues uploading files next time, please let us know of what kind these issues are.

                                                               

                                                              Please consider creating a compressed (zipped) folder next time. There are 2 major advantages.

                                                               

                                                              1. The size of log files can be dramatically reduced.

                                                               

                                                              2. By combining multiple files into a single ZIP file you need to upload a single file only.  

                                                                • Re: PI OLEDB Data retrieval too slow
                                                                  mhamel

                                                                  @Anshuman: After reading all the entries for this thread, I can notice that the test you make with the MMC Snap-in and the web server is not performed against the same server; one is one the server Simplifyeye and the other to 192.168.1.2. The log files of both applications show that there is not time restriction in your search for unitbatch unit. This cost a lot for your web service has the data source used returns several thousand of unit batches. Searching with no constraints in the batch database is costly. I have rewritten your query with constraints to go faster r.

                                                                   

                                                                   

                                                                   
                                                                  SELECT 
                                                                       pim.name pname,      
                                                                       pim.name unit,      
                                                                       uba.moduleuid,      
                                                                       uba.batchid,      
                                                                       ual.tag,      
                                                                       ual.name
                                                                  FROM
                                                                       pimodule..pimoduleh pmh
                                                                       INNER JOIN pimodule..pimodule pim
                                                                            ON pim.uid = pmh.parentuid
                                                                       INNER JOIN pimodule..pialias ual
                                                                            ON ual.moduleuid = pim.uid
                                                                       INNER JOIN pibatch..piunitbatch uba
                                                                            ON uba.moduleuid = pim.uid               
                                                                  WHERE 
                                                                       pmh.path = '<path to unitbatch unit>'     
                                                                       AND uba.starttime >= '<time constraint like t-2d>'
                                                                  

                                                                   

                                                                   

                                                                  Let me know if it works better.

                                                                    • Re: PI OLEDB Data retrieval too slow
                                                                      rene

                                                                      Hello,

                                                                       

                                                                      I think that all queries are executed against the same server because of the identical GUIDs (pimodule.uid, piunitbatch.uid etc...).

                                                                       

                                                                      The problem is that the query executed in mmc plug-in without the WHERE condition calls internally only 307 PI SDK RPC calls while the query executed in apppool:"Clr4IntegratedAppPool" with the WHERE condition calls 10672 ones. It means that the provider is using wrong execution plan for the second query

                                                                       

                                                                      /*query executed in mmc:*/
                                                                      SELECT 
                                                                      (
                                                                          SELECT pm.name 
                                                                          FROM pimodule..pimodule pm 
                                                                          INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid 
                                                                          WHERE pmh.uid = u.moduleuid
                                                                      ) pname, 
                                                                      (
                                                                          SELECT name 
                                                                          FROM pimodule..pimodule 
                                                                          WHERE uid = u.moduleuid
                                                                      ) unit, u.moduleuid, u.batchid, ual.tag, ual.name 
                                                                      FROM pibatch..piunitbatch u 
                                                                      INNER JOIN pimodule..pialias ual  ON ual.moduleuid= u.moduleuid


                                                                      /*query executed in apppool:"Clr4IntegratedAppPool"*/
                                                                      SELECT 
                                                                      (
                                                                          SELECT pm.name 
                                                                          FROM pimodule..pimodule pm 
                                                                          INNER JOIN pimodule..pimoduleh pmh ON pm.uid = pmh.parentuid 
                                                                          WHERE pmh.uid = u.moduleuid
                                                                      ) pname, 
                                                                      (
                                                                          SELECT name 
                                                                          FROM pimodule..pimodule 
                                                                          WHERE uid = u.moduleuid
                                                                      ) unit, u.moduleuid, u.batchid, ual.tag, ual.name 
                                                                      FROM pibatch..piunitbatch u 
                                                                      INNER JOIN pimodule..pialias ual  ON ual.moduleuid= u.moduleuid 
                                                                      WHERE u.starttime BETWEEN '01-Mar-2013 17:12:00' AND '*'.

                                                                       

                                                                      The workaround is to not use correlated subqueries in SELECT list:

                                                                       

                                                                      SELECT
                                                                      pm1.name pname, 
                                                                      pmh.name unit, 
                                                                      u.moduleuid, u.batchid, ual.tag, ual.name
                                                                      FROM pibatch..piunitbatch u 
                                                                      INNER JOIN pimodule..pialias ual ON ual.moduleuid= u.moduleuid
                                                                      INNER JOIN pimodule..pimoduleh pmh ON pmh.uid = u.moduleuid
                                                                      INNER JOIN pimodule..pimodule pm1 ON pm1.uid = pmh.parentuid 
                                                                      WHERE u.starttime BETWEEN '01-Mar-2013 17:12:00' AND '*'
                                                                      OPTION (FORCE ORDER)

                                                                        • Re: PI OLEDB Data retrieval too slow
                                                                          anshuman

                                                                          Thank you  Mathieu and Rene for your valuable inputs and comments.

                                                                           

                                                                          @ Mathieu:- Actually we can not put time constraints as we need all the historical data for a given plant to populate a filter for further queries.

                                                                           

                                                                          @Rene :- I tried what you suggested and it improved the performance by 4 times. But still splitting the query was the best option.

                                                                           

                                                                          Anshuman.