KB-102503: Replacing Elipse E3 queries with Stored Procedures in the database.

Question:

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  Stored Procedures? Will this reduce the amount of processing taking place in E3, and then transfer it to the Database?

Solution:

Yes. According to the article Performance tips for E3, 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.

In this article, we will show you:

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

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.

SELECT Alarms.E3TimeStamp,Alarms.FullAlarmSourceName,Alarms.InTime,Alarms.OutTime,Alarms.Area,Alarms.ConditionName 
FROM Alarms 
WHERE (( Alarms.E3TimeStamp >= #<%InitialDate%># AND Alarms.E3TimeStamp <= #<%FinalDate%># ) AND ( Alarms.Area = '<%Area%>' )) 
ORDER BY Alarms.E3TimeStamp ASC

CREATE PROCEDURE SPAlarms
-- Add the parameters for the stored procedure here
@Area varchar(50), @InitialDate datetime, @FinalDate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT Alarms.E3TimeStamp,Alarms.FullAlarmSourceName,Alarms.InTime,Alarms.OutTime,Alarms.Area,Alarms.ConditionName 
FROM Alarms 
WHERE (( Alarms.E3TimeStamp >= @InitialDate AND Alarms.E3TimeStamp <= @FinalDate ) AND ( Alarms.Area = @Area )) 
ORDER BY Alarms.E3TimeStamp ASC

END

To create the Stored Procedure, you will first need to add the following syntax to a query:

EXEC SPAlarms '<%Area%>', #<%InitialDate%>#, #<%FinalDate%>#

Sub CommandButton1_Click()
set Query = Screen.Item("Query1") 
Query.SetVariableValue "Area", "Area1"
Query.SetVariableValue "InitialDate", "13/11/2018 15:52:00"
Query.SetVariableValue "FinalDate", "13/11/2018 15:52:10"
set RS = Query.GetADORecordset()

For i = 1 To RS.RecordCount
C1 = RS("E3TimeStamp")
C2 = RS("Area")
C3 = RS("FullAlarmSourceName")
RS.MoveNext()
str = str & C1 & vbTab & C2 & vbTab & C3 & vbnewline 
Next
MsgBox str
End Sub