Hi there, As part of a project I'm working on, we're using an

chrism854 over 11 years ago

Hi there,

As part of a project I'm working on, we're using an external service to create a document and store it as base64 in an external database. This table has three identifiers, ID, ImageId and AssessmentID. ImageID is unique and primary. One Assessment may have multiple ImageIds and one imageId may have multiple IDs.

Now, what I want is the highest ID for each imageID for a certain assessment. Here's the query I'm using to do this:

SELECT DocumentBase64, ImageId FROM (SELECT DocumentBase64, ImageId, id, assessmentid from b where assessmentid = ac!assessmentid order by id desc) as t
group by `imageid`

Basically what this does is run a subquery which returns a sorted list by ID where assessmentid = whatever I passed. Then, the primary query groups by imageid.

Now this works perfectly, and if I run it directly in MySQL workbench it executes it 0.015 seconds. However, when it runs in Appian and I monitor the process that node took upwar...

Discussion posts and replies are publicly visible

Parents

0 Sathya Srinivasan
Appian Employee
over 11 years ago

Chris, (a) how are you running this query. (b) in general, Appian doesn't do anything extra. It simply fires the query using JDBC and provides the response back to you and (c) how big are your base64 documents. What you see in the MYSQL browser is a snippet of the content (mostly!) but when you do the same through an app server, now you have the overhead of the entire content coming through the wire.
Ideally, your architecture should minimize queries like these transporting huge amount of data. For e.g. if you accidently run this without a where-clause, you are going to clog the network. The alternate option is to use Appian DMS (preferred!) or to store the document in your FS and hold the URL in the DB.
In this case, check the volume of data that's returned by the query first.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Sathya Srinivasan
Appian Employee
over 11 years ago

Chris, (a) how are you running this query. (b) in general, Appian doesn't do anything extra. It simply fires the query using JDBC and provides the response back to you and (c) how big are your base64 documents. What you see in the MYSQL browser is a snippet of the content (mostly!) but when you do the same through an app server, now you have the overhead of the entire content coming through the wire.
Ideally, your architecture should minimize queries like these transporting huge amount of data. For e.g. if you accidently run this without a where-clause, you are going to clog the network. The alternate option is to use Appian DMS (preferred!) or to store the document in your FS and hold the URL in the DB.
In this case, check the volume of data that's returned by the query first.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data