Query to SQL conversion Select Attributes

JoshK131

Build Number	Database	OS
3280	MYSQL	Windows

Hello all

I have some business objects with around 200 attributes.
When i create a query that displays 5 attributes, the converted SQL query from the logs does a SELECT statement that includes all attributes, even ones not used in the query.
This is causing paylaod sizes of 1200kB to 1800kB that take up to 48s to load in.

There are other factors like query complexity and number of records that are also part of the reason it takes 48s, but I cant do much about those at the moment.

V10 has a feature where I can use ExecSP with pagination which will limit query return size which will reduce the payload and run faster.

Is it possible to only include the displayed and used attributes in the stored procedure to reduce payload size even further?

Do you guys have any optimization tips for large tables?

Thanks

Shadow_XV

JoshK131

You can definitely used a SP to return only the attributes you want. You can also use a direct SELECT in textual view for a query.

EXEC_SQL SELECT BO1 WHERE Attribute='Value' RETURN BO2

This would get all of the instances of BO1 where the attribute matches the value and return it as a “virtualized” BO2, if you do not need to use data from other BO’s then it should be fine to just return BO1. (I can give an example of what I mean regarding virtualized BO2 if it doesn’t make sense)

I strongly recommend you do NOT use direct SQL to do updates/add objects as from experience messing with how AwareIM does its thing causes massive headaches.