Tree Grid View slow: reads whole branch on expand — improvement ideas?
Hi Aras Community Folks, I’m facing a performance issue with the Aras TGV, expanding a node is extremely slow at large data volumes (~10 minutes at 1M items), and it looks like the server reads all descendant rows even though only the first 100 are shown. Details below — would really appreciate any guidance. Environment: Aras Innovator 14.36(14 SP36). Standard TreeGridView over a recursive Query Definition. Short version of the problem: When a user expands a node, the grid shows only the first 100 children (page size 100, with a “Show More” row). But under the hood, the server reads every descendant item beneath that branch — hundreds of thousands of rows — even though only 100 are displayed and none of the descendants are returned in the response. At 1Million items this makes a single expand take ~10 minutes. Root item (starting point) │ └─ Level B item ← via root's reference property │ ├─ [B → B] Count(child B) > 0 │ └─ Level B item ⟲ "Reuse Query Element Definition" → back to B (recursion) │ └─ [B → C] Count(C) > 0 └─ Level C item (is_current = 1) │ └─ [C → C] Count(C) > 0 └─ Level C item ⟲ "Reuse Query Element Definition" → back to C (recursion) Page size (max_child_items) = 100 | Default grow depth = 2 Two recursive legs: B→B and C→C, each gated by Count(...) > 0 Two recursive legs: B→B and C→C, each via Reuse Query Element Definition. Each relationship is gated by a Count(...) > 0 child-existence filter. TGV settings: page size max_child_items = 100, default grow depth = 2. What we measured: Expand one mid-level B node at 1M items: ~8 min. Shows 100 B rows + “Show More”, 0 C rows — but SQL reads the full descendant set (hundreds of thousands of rows) twice (paging query + property read). Count(...) > 0 already compiles to a permission-aware EXISTS; the cost is the broad descendant read, tied to the expand-icon (hasChild) check. Relationship source_id/related_id indexes already exist. Questions: On expand of an item, only the first 100 children are shown — but the server reads all descendants beneath the branch (full data) first, then returns 100 items. The descendants are read in full just to show 100 rows and to decide each row’s “+” expand icon. Is there a supported way to fetch only the 100 displayed rows from the start, and to make the expand-icon check stop at the first match instead of reading everything? Regarding the global tgv_MaxFetchItemsCount variable (default 1,000,000): we’re considering temporarily lowering it (e.g. to 100) before an expand and restoring the default afterwards, to limit how much is fetched. Does it actually limit the result to that many rows (return just the first N), or does it only reject an over-limit request with an error? And is it checked before the heavy SQL runs (so it saves the work), or after (so the rows are already read)? Would really appreciate any guidance or suggestions on the queries above106Views0likes3CommentsInstallation issue : ODBC Driver 18 SSL Provider error
Hello 😊 I'm trying to install Aras Innovation (all in one server for testing purpose) and I'm stuck on this nice message while trying to connect to the DB : Just to inform that my system is under : Windows Server 2025 Datacenter Azure Edition (10.0) IIS server version 10 (10.0.26100.32860) .NET 6.0.36 installed Microsoft SQL Server Express 2025 (17.0.1000) ☝️ And I've already pushed ODBC17/18 but without success regarding the install. Thank you for your precious "lights" 💡 Regards, G.533Views0likes11CommentsInstalling Aras Innovator and SQL?
I have a question about installing Aras Innovator and setting up the environment. When I checked the installation guide, there was no mention of SQL Server during installation. Please tell me what I should do before installation and what I should do after installation. I believe that Aras Innovator indirectly manipulates SQL. There was a mention of SQL during installation in the Japanese blog. Reference URL aras.com/.../install-2023-releaseSolved269Views0likes5CommentsUsing additional custom indexes in SQL for custom ItemTypes - any best practice tips?
Hi community, for a couple of tables I use additional SQL indexes for performance reasons. I noticed that the SQL ItemType allows us to specify custom indexes too and even contains a few for the most common used ItemTypes. Aras for example typically uses them for versioned ItemTypes like classic Part and CAD. The Aras SQL ItemType has one big advantage -> it´s a visible item that can be exported. Indexes used directly in the SQL database are less obvious to see for admins. I wonder if we should use something similar in our own custom ItemTypes in general. Especially for versioned ItemTypes. Of course SQL indexes are mainly used for performance reasons. There are multiple ways to optimize performance. But has anyone some tips when custom indexes should be considered by default (!) in the context of Innovator? @ Aras: Do you have any official recommendations or guidelines regarding SQL indexes? Many thanks for any hint! Best regards Angela65Views1like0CommentsHow to use a query to get distinct values from a item table
Community, I have created a staging table capability that will be used to generate innovator eBOMs from Oracle manufacturing BOMs. My challenge is working with 1.7 million rows of data in SQL Server. I know I could create a SQL procedure to get the unique (distinct) oracle Assembly IDs but I would like to use a Query Definition. In the data model below, what is circled in RED is the Oracle Data. What is circled in Blue is where I am separating a 1) [multi-Division, multi-Ora Org, with non distinct Assemblies (by Ora Org)] : stg_Oracle_MfgItem_MfgItem INTO 2) [single-Division, multi-Ora Org, Distinct Primary Assemblies] stg_eBOM_PartBOM_Primary AND 3) [single-Division, multi-Ora Org, Other Assemblies (without Primary PartBOM)] stg_eBOM_PartBOM_Other The goal is as follows: 2) stg_eBOM_PartBOM_Primary will be used to generate the innovator [Part BOM] relationships 3) stg_eBOM_PartBOM_Other will be compared to stg_eBOM_PartBOM_Primary to derive Substitutes So I would like to use a Query Definition to gather all the Distinct Oracle Assembly IDs so I can populate the stg_eBOM_PartBOM_Primary table. Thanks in advance for your time and insight. Scott155Views0likes6CommentsConnecting to Linked Server (External SQL Database)
Hello all, After many attempts and support of Aras Support, we are still unable to execute SQL Stored Procedures on an external SQL database. Context Aras Innovator Version 26 SQL Server 2017 Goal Execute a Stored Procedure on an external SQL server (NOT the Aras SQL server itself) from a C# Server Method. Attempts Making a connection using Microsoft.Data.SqlClient and the legacy System.Data.SqlClient. This involves using a connection string and executing a plain command. Used code: [embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:f6796336-b0f8-4284-bca3-4d7d91f97dd5:type=csharp&text=Innovator%20inn%20%3D%20this.getInnovator%28%29%3B%0A%0Astring%20connectionString%20%3D%20%40%22Data%20Source%3DServerName%5CInstanceName%3BInitial%20Catalog%3DDatabaseName%3BUser%20ID%3DUserID%3BPassword%3DPassword%3BConnection%20Timeout%3DTimeoutValue%3B%22%3B%0Astring%20sqlQuery%20%3D%20%40%22Exec%20%5BServerName%5CInstanceName%5D.%5BDatabaseName%5D.%5Bdbo%5D.%5BStoredProcName%5D%20%40Variant%20%3D%20VariantValue%2C%20%40State%20%3D%20%27StateValue%27%2C%20%40ID%20%3D%20%27GUID%27%2C%20%40Test%3DTestValue%3B%22%3B%0A%0A%0Ausing%20%28Microsoft.Data.SqlClient.SqlConnection%20connection%20%3D%20new%20Microsoft.Data.SqlClient.SqlConnection%28connectionString%29%29%0A%7B%0Aconnection.Open%28%29%3B%0A%0Ausing%20%28Microsoft.Data.SqlClient.SqlCommand%20command%20%3D%20new%20Microsoft.Data.SqlClient.SqlCommand%28sqlQuery%2C%20connection%29%29%0A%7B%0Aobject%20result%20%3D%20command.ExecuteScalar%28%29%3B%20%2F%2F%20Executes%20query%20and%20returns%20the%20first%20column%20of%20the%20first%20row%0A%0Aif%28result%20%21%3D%20null%29%0A%7B%0Areturn%20inn.newError%28result.ToString%28%29%29%3B%20%2F%2F%20Logs%20the%20value%0A%7D%0Aelse%0A%7B%0Areturn%20inn.newError%28%22No%20result%20found.%22%29%3B%0A%7D%0A%7D%0A%7D] Result: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Adding the external server as 'Linked Server' to the Aras SQL server. Then using innovator.applySQL() referencing the external database. Used code: [embed:dc8ab71f-3b98-42d9-b0f6-e21e02a0f8e2:4f6ce192-841a-4134-8fa4-3bb5694c5708:type=csharp&text=Innovator%20inn%20%3D%20this.getInnovator%28%29%3B%0A%0Astring%20sqlQuery%20%3D%20%40%22Exec%20%5BServerName%5CInstanceName%5D.%5BDatabaseName%5D.%5Bdbo%5D.%5BStoredProcName%5D%20%40Variant%20%3D%20VariantValue%2C%20%40State%20%3D%20%27StateValue%27%2C%20%40ID%20%3D%20%27GUID%27%2C%20%40Test%3DTestValue%3B%22%3B%0A%0AItem%20response%20%3D%20inn.applySQL%28sqlQuery%29%3B] Result: This makes the browser client crash and we suspect another timeout in the back-end. Question Does anyone have a method (or workaround) to execute SQL on an external SQL server (NOT the Aras SQL server itself) from a C# server method? Best regards, Daan93Views0likes0CommentsLooking for equivalent of SQL select max(property) in IOM
I am looking for the equivalent to select max(property) in IOM. The below SQL query returns exactly what I want, but I understand that this should be used as a last resort and want to make sure I'm not missing something. select MAX(item_number) FROM [InnovatorServer27_Dev_HL].[innovator].[PART] where part.item_number like '1010P%' result: 1010P000002 This is the function within my method that I'm working with. string GetNextBaseNumber(Innovator inn, string PartNumberPrefix) { int BaseNumber = 1; // This query is currently returning a collection when I want it to return the max value Item Part = inn.newItem("Part", "get"); Part.setProperty("item_number", PartNumberPrefix + "%"); Part.setPropertyAttribute("item_number", "condition", "like"); Part = Part.apply(); if (!Part.isEmpty()) { BaseNumber = Int32.Parse(Part.getProperty("item_number").Substring(5,6))+1; } return PartNumberPrefix + BaseNumber.ToString().PadLeft(6, '0'); }Solved190Views0likes2CommentsAras losing data
Hi Team, I am experiencing a serious problem with my one Aras instance. We are losing data in the system, it is just disappearing without a trace. The user would add data, and then checking the next day, it is gone, without a trace, not even in the history file. My gut feeling is that transactions in SQL was being rolled back, without being committed. I pulled a report from SQL and there seems to be rollbacks aligned with the data loss. As part of this Aras instance I am creating a CAD interface in C# .NET. I am exclusively using applyAML() to talk to Aras, I am not using applySQL. Could I be doing something wrong in .NET? Kind Regards RiaanSolved246Views0likes7CommentsC# SQL Date Query
Good day all, I am trying to create a dynamic report to produce the time it take between starting an activity and ending an activity. I can get the information I am looking for by using SQL, but it will not limit the returns based on dates. I have tried a few different ways to do this, but nothing is working for me. Thanks for any help you can provide. StringBuilder sql = new StringBuilder(); sql.AppendLine( "Select " + "WP.name AS change, " + "WP.state AS status, " + "Act.name AS activity, " + "ClosedBy.keyed_name AS closed_by, " + "Act_Assign.created_on, " + "Act_Assign.closed_on, " + "Act_Assign.modified_on, " + "GETDATE() AS today, " + "DATEDIFF (d, [Act_Assign].created_on, [Act_Assign].closed_on) + 1 AS date_diff " + "FROM [Workflow_Process] AS WP " + "INNER JOIN [Workflow_Process_Activity] AS WPA ON WPA.source_id = WP.id " + "INNER JOIN [Activity] AS Act ON Act.id = WPA.related_id " + "INNER JOIN [Activity_Assignment] AS Act_Assign ON Act_Assign.source_id = Act.id " + "INNER JOIN [sm_Change] as Change ON Change.item_number = WP.name " + "LEFT OUTER JOIN [User] AS ClosedBy ON ClosedBy.id = Act_Assign.closed_by " + "WHERE WP.name = 'CR-1091' OR WP.name = 'CR-1169' " + // AND Act.name = 'ECR Approval' "AND Act_Assign.modified_on BETWEEN '2023-08-01' AND '2023-08-02' " + "ORDER BY WP.name, Act_Assign.created_on ASC, Act_Assign.closed_on DESC " );Solved325Views0likes7CommentsInnovator SQL Procedures vs. Date Format - how to prevent conversion to American date format?
Hi community, I use an Innovator SQL procedure to get some data that is merged with a second database. The SQL procedure is called from a server Method When I test the procedure in SQL, date properties like modified_on are shown as in correct regular Innovator db format: But the SQL procedure doesn´t return the date in this neutral UTC format to the Server Method. Instead of the shown date in the SQL, I get a converted date that looks like this: 7/13/2002 1:49:27 PM In my case the date is later displayed in a Form, but in my country nobody uses this date format. I am able convert the date into correct DMY-format in C# or SQL. But these steps are unnecessary complicated cause of the American date in between. I wonder why I get the American MDY-date at the first place. I use German locals for Innovator and the server itself. Does anyone know, what can cause dates to be converted to American format when using SQL procedures? Is there away to prevent it? Thanks for any hint! Angela81Views0likes0Comments