E3 scada and sql data access

#1

Good Afternoon,
i am using elipse e3 and SQL Server 2014 Standard edition.
and now database is above 9GB.
whenever we fetching data from database scada will take much time and getting not responding .after data fetching it will get ok. and user get lots of breakdown due to this problem.
i am using this script (OnShow Event and Button)
DIM F_STR,L_STR,MYTOTQRY
DIM Mday,Mmonth,Myear,Mhour,Mminute,Msecond,StartDate,EndDate,MYP,MYT

F_STR = “”
L_STR = “”

StartDate = date

Mday = Day(StartDate)
Mmonth = Month(StartDate)
Myear = Year(StartDate)
Mhour = 07’Hour(StartDate)
Mminute = 15’Minute(StartDate)
Msecond = 00’Second(StartDate)

F_STR = Myear & “/” & Mmonth & “/” & Mday & " " & Mhour & “:” & Mminute & “:” & Msecond

Mday = Day(StartDate)
Mmonth = Month(StartDate)
Myear = Year(StartDate)
Mhour = 23’Hour(StartDate)
Mminute = 59’Minute(StartDate)
Msecond = 59’Second(StartDate)

L_STR = Myear & “/” & Mmonth & “/” & Mday & " " & Mhour & “:” & Mminute & “:” & Msecond

MyQry = “(E3TimeStamp >= '” & F_STR & “’ AND E3TimeStamp <= '” & L_STR & “’)”

Screen.Item(“E3Browser1”).Item(“Query1”).SetVariableValue “MyQry”,MyQry
Screen.Item(“E3Browser1”).Requery()

Please Help me…
thanks in advance…

#2

Hi @selva!

Is the query returning any data or displaying an error message at the end?

What is the Query1’s SQL code?

#3

hi @pgustavo ,
good morning sir.

there is no error at the end ,its returning with data only
and sql code is

SELECT (ST100.E3TimeStamp) AS InTime,ST100.InternalSerialNo,ST100.PCode,ST100.Result,PalletNo,ST100.RepairFlag,ST100.CylTm,ST100.ShaftEndPlayOK,ST100.ShaftLoadOK,ST100.ShaftEndPlayNG,ST100.ShaftLoadNG,ST100.ShaftEndPlayRT,ST100.ShaftLoadRT
FROM ST100
Where <%MyQry%>
ORDER BY ST100.E3TimeStamp Desc

#4

Please send the E3 log file to pgustavo@elipse.com.br.

#5

ok sir .i will send you as soon as possible.

#6

Good Evening sir,
as per discussion i sent the mail to you.

#7

Dear @selva ,

I suggest the following changes:

  1. SQL Code:

    SELECT (ST100.E3TimeStamp) AS InTime,ST100.InternalSerialNo,ST100.PCode,ST100.Result,PalletNo,ST100.RepairFlag,ST100.CylTm,ST100.ShaftEndPlayOK,ST100.ShaftLoadOK,ST100.ShaftEndPlayNG,ST100.ShaftLoadNG,ST100.ShaftEndPlayRT,ST100.ShaftLoadRT
    FROM ST100
    Where (E3TimeStamp >= #<%F_STR%># AND E3TimeStamp <= #<%L_STR%>#)
    ORDER BY ST100.E3TimeStamp Desc

  2. Script:

    …
    …
    'Screen.Item(“E3Browser1”).Item(“Query1”).SetVariableValue “MyQry”,MyQry
    Screen.Item(“E3Browser1”).Item(“Query1”).SetVariableValue “F_STR”, F_STR
    Screen.Item(“E3Browser1”).Item(“Query1”).SetVariableValue “L_STR”, L_STR

#8

Dear @pgustavo
Thank you for your support and i will check update to you.