anshuman

PI OLEDB Data retrieval too slow

Discussion created by anshuman on Apr 16, 2013
Latest reply on Apr 24, 2013 by 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();
        }
    }
}
 

 

 

 

 

 

 

 

 

 

 

 

Outcomes