15 Replies Latest reply on May 29, 2015 7:42 AM by Eugene Lee

    Why is a AFDatabase linked with OLEDB Enterprise so slow

    larsoleruben

      Hi

      We are trying to expose Eventframes throgh a Linkedserver, where we are using OLEDB Enterprise to to make the link.

      Unfortunately, we see a performance degradation of a factor of 1000 or more, in comparasion with the PISQLCommander?

      Why is that and what can we do to remedy it. Out users really like this way of accessing data.

        • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
          pthivierge

          Hello Lars,

           

          It is hard to point out a single specific reason for the problem you are encountering. There are multiple layers of technology to consider and I will try to give you the key points so you can investigate and report back what helped improve the performances.

           

          Sql Server

           

          How many servers do you have in your architecture? It is the same SQL Server that host the PIFD Database that has the linked server installed into it?

          I am asking this because having two separate server in this situation will require that you check the network between these machines to make sure speed is alright.

           

          Its always good to check resources for SQL server.  And especially if you have a standard edition (not express), you may need to limit the memory used by SQL Server because it will use all available memory and this may limit the available memory for OLEDB Enterprise. You should discuss this why the SQL Server Administrator if it applies.

           

          OLEDB Enterprise

           

          Installation & configuration

          How did you configure the OLEDB Provider parameters?  We do have recommendations, especially the "allow InProcess" parameter must be set to true.

          You may use this KB Article to double check that: KB01143 - How to configure a linked server with PI OLEDB or PI OLEDB Enterprise - I am talking about the "Configure OLDB provider" section only that exposes recommended Provider Options.

           

           

          Queries performed

           

          How does your SQL query looks like when querying through the linked server? can you provide a sample of your query?

           

          When using a linked server, there could be some situations when you are gathering data to SQL Server first and thus you make SQL Server make the data operations.  This is to be avoided because you want most of the operations to be carried out by the provider itself.  This is why its better to use pass through queries when using a linked server.

           

          There are two ways I know to do pass through queries, in the following examples, my linked server for AF is called AF.

          Example 1:

          select * from openquery(AF,'
          SELECT ef.Name,ef.StartTime,ef.EndTime
          FROM [Support].[EventFrame].[EventFrame] ef
          WHERE EventFrameTemplateID IN 
          (
            SELECT ID
            FROM [Support].[EventFrame].[EventFrameTemplate]
            WHERE Name = ''HourlyKPI''
          )
            AND ef.StartTime>=''2013-01-01''
            AND ef.EndTime<=''2015-05-21''
          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
          ')
          

           

           

          Example 2 - this version uses the "Exec() AT " syntax, this requires that you set the "RPC Out" option of your linked server properties to true, this is a very convenient way to pass parameters to your query, as you can see :

          declare @startTime varchar(50)='2013-01-01'
          declare @endTime varchar(50)='2015-05-21'
          declare @efTemplate varchar(50)='HourlyKPI'
          
          
          Exec(
          '
          SELECT ef.Name,ef.StartTime,ef.EndTime
          FROM [Support].[EventFrame].[EventFrame] ef
          WHERE EventFrameTemplateID IN 
          (
            SELECT ID
            FROM [Support].[EventFrame].[EventFrameTemplate]
            WHERE Name = ?
          )
            AND ef.StartTime>=?
            AND ef.EndTime<=?
          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)',@efTemplate,@startTime,@endTime) AT [AF]
          

           

          Its not obvious that performances are improved with the small query I provided, but when you are doing extensive join operation you should see dramatically improved performances.

           

          To go further

          PI OLEDB Enterprise SQL Optimization Whitepaper

           

          PI OLEDB Enterprise Documentation

           

           

          Hope this helps!

          2 of 2 people found this helpful
          • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
            bshang

            Hi Lars,

             

            I'd also like to point you to the content of our recent UC TechCon hands-on labs.

            https://techsupport.osisoft.com/Downloads/File/9f3d2d42-2f6b-4f2f-a47b-3124976cab63

             

            In particular, you may be interested in the material for the class

            Optimizing SQL Queries for Performance Using PI OLEDB Enterprise

             

            In the latter examples, it discusses how you can troubleshoot slow linked server performance by viewing the execution plan of the query from SQL Server Management Studio and checking for multiple roundtrips to/from PI OLEDB Enterprise (which can be mitigated via open or pass-through queries as Patrice mentioned above).

            2 of 2 people found this helpful
            • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
              tperovic

              Lars,

               

              I struggled with PI OLEDB Enterprise performance issues for months. Send OLEDBENT the wrong query and it'll go off wandering in the wilderness sometimes never to return. Barry is right, with OLEDBENT you'll need to be very careful how you phrase the question.

               

              I had queries similiar to the examples that Patrice provided (using a simple correlated subquery) that worked great until the number of event frames started increasing taking longer and longer to return a dozen rows then finally generated an "Internal Error". My event frames had attributes (as most useful event frames do) which added more tables into the mix.

               

              I ended up using SQL Server Stored Procedures to get data from AF in two, three or four chunks, whatever OLEDBENT can handle, stored in temporary tables or table variables then assembling the final results in the SP.

               

              You did not provide a sample of the query that is performing poorly.

              Do your event frames have attributes?

              Are you trying to retrieve those attribute values?

               

              Tony Perovic

              Compumation, Inc.

                • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
                  larsoleruben

                  Hi Tony

                  That is exactly what I am trying to do. Get event frames, get data from attributes and make them available in a report.

                  If I should make this for my self, I would use the SDK and C#, but we have customers who wants to be able to access the data from within SQL Manager.

                   

                  What puzzles me the most is that a query in PISqlCommander can run i less than a second, and when I run the exact same query through a linke server, it might take 10 og 20 seconds?

                    • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
                      tperovic

                      Can you share the SQL Server query? Obviously, I will not be able to run the query but it might provide some insight.

                       

                      Tony Perovic

                      Compumation, Inc.

                        • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
                          larsoleruben

                          Hi

                          I have this stored procedure (anonymous blok), where I need to divide the eventframes into individual days (if they expand more than one day) time wise, and do some calculations of time pr day and lost power, etc

                          It might be hard to read it from here, so I sugest you copy it into ie. "sublime text" or something

                           

                          DECLARE @_TemplateGUID uniqueidentifier
                          DECLARE @_EfaIdGUID uniqueidentifier
                          DECLARE @_Id varchar(2000)
                          DECLARE @_EventFrameAttributeID varchar( 2000 )
                          DECLARE @_ValueStr varchar(2000)
                          DECLARE @_Name varchar(200)
                          DECLARE @_StartTime datetime2
                          DECLARE @_EndTime datetime2
                          DECLARE @_FirstAlarmCode varchar(200)
                          DECLARE @_FirstAlarmCodeText varchar( 300 )
                          DECLARE @_Diff int
                          declare @_counter int
                          DECLARE @_LostProduction varchar(2000)
                          DECLARE @_timeDiffSeconds int
                          
                          set @_TemplateGUID = '3978faef-e617-49aa-814b-535019d6d432' /*Got this from PI Explorer, attempt to avoid a join*/
                          if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'evData')
                            drop table  evData;
                          create table #evData(
                             Id varchar( 2000 ),
                             EvenFrameAttributeID varchar(2000),
                             ValueStr varchar( 2000 ),
                             Name varchar( 2000 ),
                             StartTime datetime2,
                             EndTime datetime2,
                             OutageTime int,
                             FirstAlarmCode varchar(50),
                             FirstAlarmCodeText varchar( 50),
                             LostProduction varchar(2000), 
                             Diff int
                             )
                          /*Get the narrowed attributes*/
                          declare EventFrame cursor for 
                           SELECT ef.ID, efas.EventFrameAttributeID,  efas.ValueStr,ef.Name, ef.StartTime, ef.EndTime, tc.FirstAlarmCode, tc.FirstAlarmCodeText, datediff(DAY,starttime,endtime) as ddiff FROM 
                           (
                            SELECT Efs.EventFrameAttributeID, efs.ValueStr, efa.EventFrameID FROM [CSI00810_AF_LINKED].[DongEnergyWind].[Data].[EventFrameSnapshot] efs
                            inner REMOTE join  [CSI00810_AF_LINKED].[DongEnergyWind].[EventFrame].[EventFrameAttribute] efa on efa.ID = Efs.EventFrameAttributeID
                            Where efa.Name = 'OCSClassification'  AND efs.ValueStr like '1.1.%' and efs.EventFrameTemplateAttributeID = @_TemplateGUID  ) 
                            efas INNER REMOTE JOIN
                            [CSI00810_AF_LINKED].[DongEnergyWind].[EventFrame].[EventFrame] ef on ef.ID = efas.EventFrameID 
                            INNER REMOTE JOIN [CSI00810_AF_LINKED].[DongEnergyWind].[DataT].[ft_TransposeEventFrameSnapshot_EFOutageClassification] tc on tc.eventFrameId = ef.ID
                            WHERE ef.StartTime between '2015-05-01' and '2015-05-03'
                            OPTION (FORCE ORDER)
                          open EventFrame;
                           fetch next from EventFrame into @_Id, @_EventFrameAttributeID, @_ValueStr, @_Name, @_StartTime, @_EndTime, @_FirstAlarmCode,@_FirstAlarmCodeText, @_Diff
                           while @@FETCH_STATUS = 0
                            begin
                             
                             set @_EfaIdGUID = @_Id
                             select @_LostProduction = s.ValueStr  from  [CSI00810_AF_LINKED].[DongEnergyWind].[EventFrame].[EventFrameAttribute] efa  
                             INNER REMOTE JOIN [CSI00810_AF_LINKED].[DongEnergyWind].[Data].[EventFrameSnapshot] s ON s.EventFrameAttributeID = efa.ID
                             where efa.name = 'LostProductionEst1' and efa.EventFrameID = @_EfaIdGUID
                             OPTION (FORCE ORDER)
                          
                             if(  @_Diff = 0 )
                              begin
                               insert into #evData values ( @_Id, @_EventFrameAttributeID, @_ValueStr, @_Name, @_StartTime, @_EndTime, datediff(SECOND,@_StartTime,@_EndTime), @_FirstAlarmCode, @_FirstAlarmCodeText, @_LostProduction, @_Diff );
                              end;
                             if( @_Diff = 1 )
                              begin
                                insert into #evData values ( @_Id, @_EventFrameAttributeID, @_ValueStr, @_Name, @_StartTime, cast(@_StartTime as date), datediff(SECOND,@_StartTime, dateadd(DAY, 1,cast(@_StartTime as date))), @_FirstAlarmCode, @_FirstAlarmCodeText, @_LostProduction, @_Diff);
                                insert into #evData values (@_Id, @_EventFrameAttributeID, @_ValueStr, @_Name,cast(@_endtime as date), @_EndTime, datediff(SECOND,dateadd(DAY,-1,cast(@_endtime as date)),@_endtime), @_FirstAlarmCode, @_FirstAlarmCodeText,@_LostProduction,  @_Diff );
                              end;
                             if( @_Diff > 1 )
                              begin
                               insert into #evData values (@_Id, @_EventFrameAttributeID, @_ValueStr, @_Name, @_StartTime, cast(@_starttime as date),datediff(SECOND,@_StartTime, dateadd(DAY, 1,cast(@_StartTime as date))), @_FirstAlarmCode, @_FirstAlarmCodeText, @_LostProduction, @_Diff);
                               set @_counter = 1;
                               while @_Diff-1 >= @_counter
                               begin
                                insert into #evData values ( @_id, @_EventFrameAttributeID, @_ValueStr, @_Name, cast(  dateadd(d,@_counter, @_StartTime) as date), cast(  dateadd(d,@_counter, @_starttime) as date), 24*3600, @_FirstAlarmCode, @_FirstAlarmCodeText, @_LostProduction, @_Diff);
                                set @_counter = @_counter + 1
                               end;
                               insert into #evData values ( @_id, @_EventFrameAttributeID, @_ValueStr, @_Name,  cast(@_endtime as date), @_EndTime, datediff(SECOND,dateadd(DAY,-1,cast(@_endtime as date)),@_endtime), @_FirstAlarmCode, @_FirstAlarmCodeText, @_LostProduction, @_Diff );
                              end;
                            fetch next from EventFrame into @_Id, @_EventFrameAttributeID, @_ValueStr, @_Name, @_StartTime, @_EndTime, @_FirstAlarmCode, @_FirstAlarmCodeText, @_Diff
                            end;
                           close EventFrame;
                           deallocate EventFrame;
                           select * from #evData
                          --rollback
                          
                            • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
                              larsoleruben

                              PS actually I run it as an anonymous block, and not as a procedure, makes no difference though :-)

                              • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
                                tperovic

                                Lars,

                                 

                                Unless you use OPENQUERY() or EXECUTE() AT, you don't really know exactly what is being sent to the Linked Server. I know this because for a little while, tech support enabled logging on my server for troubleshooting and I could see every query SQL Server was sending to the OLEDBENT linked server. Since SQL Server is not aware of the specifics of the linked server, it must use generic OLEDB syntax and it can make no assumptions about optimization.

                                 

                                I used the following technique to control what is being sent to PI OLEDB Enterprise linked server:

                                 

                                 

                                DECLARE @starting datetime
                                
                                DECLARE @ending datetime
                                
                                DECLARE @sql nvarchar(max)
                                
                                
                                
                                SET @starting = '9-May-2015'
                                
                                SET @ending = '10-May-2015'
                                
                                
                                
                                SET @sql = '
                                
                                SELECT * FROM AP16.EventFrame.EventFrame
                                
                                WHERE StartTime > DATE(''' + CONVERT(char(10),@starting,120) + ''')
                                
                                AND StartTime < DATE(''' + CONVERT(char(10),@ending,120) + ''')'
                                
                                
                                
                                -- Uncomment the next line to see the query sent to OLEDBENT
                                
                                -- PRINT @sql
                                
                                EXECUTE(@sql) AT AFSERVER
                                

                                 

                                This allows me to control exactly what is sent to OLEDBENT and also to copy and paste the very same query into SQL Commander.

                                 

                                Next, in the nested query, you reference EventFrameAttribute and inner join between EventFrameAttribute and EventFrame comes later and the time filter in the where clause later still. Forcing the order might cause it to execute the nested query first then join the results. I don't think the optimizer is smart enough to apply a where clause to a nested query to eliminate rows. You have to make the query simpler so it can figure it out.

                                 

                                FROM
                                (
                                    SELECT
                                        Efs.EventFrameAttributeID,
                                        efs.ValueStr,
                                        efa.EventFrameID
                                    FROM
                                        .[DongEnergyWind].[Data].[EventFrameSnapshot] efs
                                            inner REMOTE join
                                        .[DongEnergyWind].[EventFrame].[EventFrameAttribute] efa on efa.ID = Efs.EventFrameAttributeID
                                    WHERE
                                        efa.Name = 'OCSClassification'
                                    AND
                                        efs.ValueStr like '1.1.%'
                                    AND efs.EventFrameTemplateAttributeID = @_TemplateGUID
                                ) efas
                                Try drilling top/down like this:
                                
                                FROM
                                    Database.EventFrame.EventFrameTemplate eft -- Get the template (one row)
                                        INNER JOIN -- Join the event frames derived from the template
                                    Database.EventFrame.EventFrame ef on eft.ID = ef.EventFrameTemplateID
                                        INNER JOIN -- Join the event frame attributes
                                    Database.EventFrame.EventFrameAttribute efa on ef.ID = efa.EventFrameID
                                        INNER JOIN -- Join the attribute snapshot values
                                    Database.Data.EventFrameSnapshot efs on efa.ID = efs.EventFrameAttributeID
                                        INNER JOIN -- Join transposed event frame attribute snapshots
                                    Database.DataT.ft_TransposeEventFrameSnapshot_EFOutageClassification tc on tc.EventFrameId = ef.ID
                                WHERE
                                    eft.Name = 'YourTemplate'
                                AND
                                    ef.StartTime between '2015-05-01' and '2015-05-03'
                                AND
                                    efa.Name = 'OCSClassification'
                                AND
                                    efs.ValueStr like '1.1.%'
                                

                                 

                                 

                                Remember that it has to go to the PI Server for each snapshot value so try to minimize the number of trips to the PI Server.

                                 

                                 

                                Good luck,

                                Tony Perovic

                                Compumation, Inc.

                                1 of 1 people found this helpful
                                • Re: Why is a AFDatabase linked with OLEDB Enterprise so slow
                                  pthivierge

                                  Hi Lars,

                                   

                                  I have modified your post and used the syntax highlighting feature form the advanced editor so you code sample is easier to read. Hope you don't mind!

                                  --

                                  From what I can see, and this is also what Tony and I said earlier you should use a pass through query (Exec At or select * from open query ...). Typically, I do usually start by creating only the part that get data from the PI Provider by using a pass through query as I have shown above, and I store the query result into a table variable.  Once you have got the data you need, you can start performing you queries with joins on the local database data.  Furthermore, this technique allows you to "trace the query that was sent to the provider and execute it in SQL Commander, Tony has shown it in his example "PRINT @sql".

                                   

                                  Other Comments

                                  Remote joins are very expensive and not very efficient because the SQL Server cannot create a proper query plan to get the data as it does not know about the remote provider. This is why you should use Pass-Through queries,

                                  You should also try to avoid to use Cursors and instead try to achieve the whole operation in one batch, you should be able to replace your ifs by making  use of CASE ...WHEN.

                                  You should be able to find very good resources in the Optimization Whitepaper and in the link that Barry has pointed out.

                                   

                                  Please keep us informed how it goes.

                                   

                                  Regards,

                                  1 of 1 people found this helpful