Create a record for non primary key based table

Hi bhushanc581,

What is preventing you from generating a standard primary key field that you just use internally and do not expose to the end user? The primary key needs to be a unique identifier of a record. If your example is real, you are not using a field that is unique since the first 2 rows have the same identifier. 

Hi Pedro Simões

The table is not managed within Appian and is not part of Appian db, it is part of different software suite. hence I cannot add a primary key. the table resides in shared database space. changes to the table is not permitted as it will break other software that is utilizing it.
the requirement is just to display the data in grid with export functionality from the table.

To add to Pedro's answer - part of the reason a record requires a primary key is for export. To export data from a grid, the export will generate the data in batches to add to the grid, and the batching process needs to ensure that the data isn't duplicated in the export file.

Is it possible to create a database view that would add a column that uses a row count or calculated column as a unique field?

Do you have any way of creating a View within Appian that references the external DB table?  I'm not sure the feasibility of such an approach but if something like this were possible (or if you could create a view in that other system specifically to be consumed by Appian), then there would be easier approaches to overcoming your issue I expect.

Thanks Mike Schmitt and Peter Lewis, 

I guess I have to ask if I can get a view with a row count in the source db.

can you guys tell me the performance impact of the view with in Appian here before I propose this, cause there is lot of traffic on the table all the time for example: around 500000 rows are Inserted/updated/deleted every day on this specific table.

My (vague) understanding of views is that they have no performance impact until they're run, i.e. there is no impact for the view just existing, until you try to query it.  At that point the performance impact is essentially the same as a query into it.

yeah, I understand, the problem is we have fairly complicated query entity already with lot of querylogicalexpressions nested together.

how much performance impact I am looking at when querying directly from table vs querying from View?

Edit: to add, currently a basic query with already defined filters take almost 10 seconds from table. [yeah, that is a lot.]

If your view is simple and mainly establishes a unique column by some means (either by row index or by concatenating some of the other column values in such a way that it's guaranteed to be unique), i wouldn't expect the query from that view to be particularly worse than querying from the original table.  It wouldn't be *better*, but also might not actually be *worse*.

Have you tried to do some performance tuning on that particular database table? Are you able to check the execution plan of your query? A 10s query jeopardizes any attempt of having a good user experience

unfortunately I cannot make edits to the table directly. I have proposed view creation. I will update once I receive any updates.

I have also requested to add index to few of the filter based columns in table for the ease of loading data faster.
Can you suggest any other options that I can propose to make things faster?

That will probably help the query performance but you'll need to analyse the query execution plan to better understand how you can improve it

That will probably help the query performance but you'll need to analyse the query execution plan to better understand how you can improve it