New in the Community? Get started here

Schneider Electric Exchange Community

Discuss and solve problems in energy management and automation. Join conversations and share insights on products and solutions. Co-innovate and collaborate with a global network of peers.

Register Now
Geo SCADA Expert Forum
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Lt. Commander

[import] 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.

 

 

 

Reply 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 User: KylePaynter123, posted: 2019-01-08 11:37:06
Great Thanks will investigate


Reply 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 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 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 User: KylePaynter123, posted: 2019-01-15 17:30:10
Guys I go it to work!

Thanks a million using COPCPointDigital did the trick