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

  • 0
    ...ds of 7 seconds!

    Now, the question is, does this query actually take 7+ seconds (and if so, how do we optimise it) or is Appian misrepresenting the length of this node's execution time?

    Thank you in advance for any help!
    Chris
  • 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.
  • 0
    Hi Sathya,

    This query is ran through a Query DB node. The documents are between 20KB and 400KB. Unfortunately we can't use the DMS as we're integrating with a external web service. It's also not set up to work over FTP/http

    I raised the limit of the column size in workbench to 99999, and it does take 0.6 seconds (with cache off) to retrieve the result. In the worst case, it could return 11 rows, which is 11 *400KB = 4.4MB maximum file size. Since we're transferring between AWS instances, I expect this transfer is practically instant.

    It doesn't seem like it should take 7 seconds, but more like 2 - 3 seconds.

    I only use this query in one place, once. Unfortunately it's time critical, as the user needs to be able to preview the forms and reject or approve them.
  • 0
    Appian Employee
    Is your SQL client running on the same machine where your SQL server resides?
    Try testing the latency with a custom java app that fetches the data from a different machine.
    The latency is most likely between the app server and the database server trying to fetch the data.
  • 0
    Hi Sathya,

    Appian is actually running on the same server as MySQL as this is a dev environment, so latency shouldn't be an issue.
  • 0
    Appian Employee
    Can you test this by querying the same number of record where the column containing base64 is blank. By this way, we eliminate the doubt.
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.