Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84650members
353989posts

[Imported] sql query takes long to generate info

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
sbeadle
Janeway Janeway
Janeway
0 Likes
1
467

[Imported] sql query takes long to generate info

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: KylePaynter123, originally posted: 2019-01-04 15:38:33 Id:343
Hi guys !

I'm attempting to create a query to display data from my Object Database

But It Takes VERY LONG to display the Data

I have simply put a List on the Screen Display and input the following Query

Query Below

Select

CD.FULLNAME AS "Fullname", CD."Id","CD.Foreground", CD."Blink",CD. "Background", CD. "TypeDesc'', CD."MemoryUsage",

CD.CURRENTSTATEDESC AS "CurrentState"

CDOBJECT AS CD JOIN COPCALGPOINT AS CO ON CD."Blink"= CO."Blink"

WHERE (''FullName" Like '%Power%) AND ("CD"."TypeDesc"='OPC-Simple Digital Point')

is the Query we are putting into the SQL section to generate list.

 

 


Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
466

Re: [Imported] sql query takes long to generate info

>>Responses imported from previous forum


Reply From User: adamwoodland, posted: 2019-01-08 00:08:43
What are you trying to do? COPCALGPOINT is a child of CDBObject so there doesn't seem to be a need to do a JOIN, and doing the JOIN on .Blink seems very undefined (and isn't indexed so will be really slow).


Reply From User: KylePaynter123, posted: 2019-01-08 11:37:06
Great Thanks will investigate


Reply From User: KylePaynter123, posted: 2019-01-08 14:29:44
Basically I'm trying to get all the Devices on the Object Data Base that contain Power in their names.

Where do you suggest or how do you suggest the SQL Query to be adjusted.

Also where do you suggest the join being done based on the Query ?


Reply From User: sbeadle, posted: 2019-01-08 14:50:05
No join needed. All fields in CDBObject are in CDBPoint, and all fields in CDBPoint are in COPC*Point etc.


Reply From User: BevanWeiss, posted: 2019-01-09 04:42:27
Doing a non-anchored wildcard search %Power% is generally not very good performance wise, you might want to consider if you can reduce down the number of characters that might get involved.
Do you really need to use FullName if you could use Name (i.e. WHERE Name LIKE '%Power%'), or do you expect that it might be a parent group with 'Power' in the name?

As noted by Steve, you could really just do:
SELECT FullName, Id, Foreground, Blink, Background, TypeDesc, MemoryUsage, CurrentStateDesc
FROM COPCPointDigital
WHERE FullName LIKE '%Power%'


Reply From User: KylePaynter123, posted: 2019-01-15 17:30:10
Guys I go it to work!

Thanks a million using COPCPointDigital did the trick

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
467

Re: [Imported] sql query takes long to generate info

>>Responses imported from previous forum


Reply From User: adamwoodland, posted: 2019-01-08 00:08:43
What are you trying to do? COPCALGPOINT is a child of CDBObject so there doesn't seem to be a need to do a JOIN, and doing the JOIN on .Blink seems very undefined (and isn't indexed so will be really slow).


Reply From User: KylePaynter123, posted: 2019-01-08 11:37:06
Great Thanks will investigate


Reply From User: KylePaynter123, posted: 2019-01-08 14:29:44
Basically I'm trying to get all the Devices on the Object Data Base that contain Power in their names.

Where do you suggest or how do you suggest the SQL Query to be adjusted.

Also where do you suggest the join being done based on the Query ?


Reply From User: sbeadle, posted: 2019-01-08 14:50:05
No join needed. All fields in CDBObject are in CDBPoint, and all fields in CDBPoint are in COPC*Point etc.


Reply From User: BevanWeiss, posted: 2019-01-09 04:42:27
Doing a non-anchored wildcard search %Power% is generally not very good performance wise, you might want to consider if you can reduce down the number of characters that might get involved.
Do you really need to use FullName if you could use Name (i.e. WHERE Name LIKE '%Power%'), or do you expect that it might be a parent group with 'Power' in the name?

As noted by Steve, you could really just do:
SELECT FullName, Id, Foreground, Blink, Background, TypeDesc, MemoryUsage, CurrentStateDesc
FROM COPCPointDigital
WHERE FullName LIKE '%Power%'


Reply From User: KylePaynter123, posted: 2019-01-15 17:30:10
Guys I go it to work!

Thanks a million using COPCPointDigital did the trick