{"id":8244,"date":"2019-08-13T16:06:21","date_gmt":"2019-08-13T19:06:21","guid":{"rendered":"http:\/\/kb.elipse.com.br\/en\/?p=8244"},"modified":"2019-08-16T14:02:12","modified_gmt":"2019-08-16T17:02:12","slug":"kb-102503-replacing-elipse-e3-queries-with-stored-procedures-in-the-database","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/kb-102503-replacing-elipse-e3-queries-with-stored-procedures-in-the-database\/","title":{"rendered":"KB-102503: Replacing Elipse E3 queries with Stored Procedures in the database."},"content":{"rendered":"

Question:<\/strong><\/h2>\n

I have a low performing application due to the many queries being executed cyclically in Elipse E3. In this case, can I replace these queries with\u00a0 Stored Procedures? Will this reduce the amount of processing taking place in E3, and then transfer it to the Database?<\/p>\n

Solution:<\/strong><\/h2>\n

Yes. According to the article Performance tips for E3<\/a>, you can replace queries with Stored Procedures depending on the query’s complexity, or even to its execution interval. You can create these SPs in the database specifically for this function, and Elipse E3 will only be responsible for their execution; therefore, the query will be processed in a completely different environment, and not in E3.<\/p>\n

In this article, we will show you:<\/p>\n

    \n
  1. First, how to identify a query in an Elipse E3 object.<\/li>\n
  2. Then, how to create a Stored Procedure.<\/li>\n
  3. Finally, how to replace the query with a Stored Procedure in an SQL Server database<\/a> and then execute ir from Elipse E3.<\/li>\n<\/ol>\n

    For this example, we created a query in a table using three filter variables. The database used in this application was Microsoft SQL Server 2014.<\/p>\n

    \n

    Query used in an E3Query in E3:<\/strong><\/h3>\n
    SELECT Alarms.E3TimeStamp,Alarms.FullAlarmSourceName,Alarms.InTime,Alarms.OutTime,Alarms.Area,Alarms.ConditionName \r\nFROM Alarms \r\nWHERE (( Alarms.E3TimeStamp >= #<%InitialDate%># AND Alarms.E3TimeStamp <= #<%FinalDate%># ) AND ( Alarms.Area = '<%Area%>' )) \r\nORDER BY Alarms.E3TimeStamp ASC<\/pre>\n
    Creating the Stored Procedure<\/h3>\n
    CREATE PROCEDURE SPAlarms\r\n-- Add the parameters for the stored procedure here\r\n@Area varchar(50), @InitialDate datetime, @FinalDate datetime\r\nAS\r\nBEGIN\r\n-- SET NOCOUNT ON added to prevent extra result sets from\r\n-- interfering with SELECT statements.\r\nSET NOCOUNT ON;\r\n\r\n-- Insert statements for procedure here\r\nSELECT Alarms.E3TimeStamp,Alarms.FullAlarmSourceName,Alarms.InTime,Alarms.OutTime,Alarms.Area,Alarms.ConditionName \r\nFROM Alarms \r\nWHERE (( Alarms.E3TimeStamp >= @InitialDate AND Alarms.E3TimeStamp <= @FinalDate ) AND ( Alarms.Area = @Area )) \r\nORDER BY Alarms.E3TimeStamp ASC\r\n\r\nEND\r\n<\/pre>\n
    Executing the Stored Procedure from E3<\/h3>\n<\/div>\n
    To create the Stored Procedure, you will first need to add the following syntax to a query:<\/p>\n
    EXEC SPAlarms '<%Area%>', #<%InitialDate%>#, #<%FinalDate%>#<\/pre>\n
    Likewise, to set up the variables and return data from the Stored Procedure, add the following script to Elipse E3:<\/div>\n
    <\/div>\n
    \n
    Sub CommandButton1_Click()\r\nset Query = Screen.Item(\"Query1\") \r\nQuery.SetVariableValue \"Area\", \"Area1\"\r\nQuery.SetVariableValue \"InitialDate\", \"13\/11\/2018 15:52:00\"\r\nQuery.SetVariableValue \"FinalDate\", \"13\/11\/2018 15:52:10\"\r\nset RS = Query.GetADORecordset()\r\n\r\nFor i = 1 To RS.RecordCount\r\nC1 = RS(\"E3TimeStamp\")\r\nC2 = RS(\"Area\")\r\nC3 = RS(\"FullAlarmSourceName\")\r\nRS.MoveNext()\r\nstr = str & C1 & vbTab & C2 & vbTab & C3 & vbnewline \r\nNext\r\nMsgBox str\r\nEnd Sub\r\n<\/pre>\n
    NOTE:<\/strong> In similar cases, you can replace GetADORecordset<\/em> method with GetAsyncADORRecordSet method<\/em>. The GetAsyncADORecordSet<\/a> <\/i>method creates a Query; once this query finishes, it will generate a OnAsyncQueryFinish <\/i>event in the object; the result passed in this event is therefore the generated result\u00a0 (Recordset<\/i>).<\/p>\n
     <\/p>\n
    Related article<\/h3>\n
    \n