[Imported] sql query takes long to generate info

[sbeadle]

>>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.

[sbeadle · Accepted solution]

>>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