We are currently performing maintenance on Appian Community. As a result, discussions posts and replies are temporarily unavailable. We appreciate your patience.
  • State Not Answered
  • Replies 6 replies
  • Subscribers 5 subscribers
  • Views 1970 views
  • Users 0 members are here
Related Discussions
Home » Discussions » Process

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

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

OriginalPostID-81719

OriginalPostID-81719

  Discussion posts and replies are publicly visible

Parents
  • 0
    Appian Employee
    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.
Reply
  • 0
    Appian Employee
    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.
Children
No Data
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.