IWorkspace.ExecuteSQL and edit sessions

[AndyCanfield]

Is there any way to get something like ExecuteSQL to work against the edit cache? Here is my issue:

I have an app where a user is making edits in ArcMap and then based on those edits I need to update up to 600,000 records across about five to ten tables. IWorkspace.ExecuteSQL does this very quickly. However anything created in the current edit session doesn't get updated because it hasn't been saved back to the database. So what I really need is that same performance but to execute against the edit cache or wherever those features are being stored before being committed to the database where ExecuteSQL can reach them.

Also does ExecuteSQL work against versions other than default? If so how do I get it to do that?

[AlexanderGray]

Sounds like you need multi-versioned views to do this.  However, I don't think you can have multi-version views on non-saved edits.  Edits in ArcSDE get written to the delta tables.  However, in some cases the features may already be deleted, some changed, others created, the changes don't make it to the base tables until a compress.  Editing geodatabase data using sql is kind of a gamble, it is fast and works great until it corrupts your entire database.  There are ways to make bulk updates faster, (updatecursor, Itablewrite, loadonly) but none of them really come close to using an sql update.  If this is a once in a while kind of update, I suggest, closing all edit sessions, reconciling all outstanding versions, compressing the table or featureclass, then running the sql script.

[AndyCanfield]

Sounds like you need multi-versioned views to do this.  However, I don't think you can have multi-version views on non-saved edits.  Edits in ArcSDE get written to the delta tables.  However, in some cases the features may already be deleted, some changed, others created, the changes don't make it to the base tables until a compress.  Editing geodatabase data using sql is kind of a gamble, it is fast and works great until it corrupts your entire database.  There are ways to make bulk updates faster, (updatecursor, Itablewrite, loadonly) but none of them really come close to using an sql update.  If this is a once in a while kind of update, I suggest, closing all edit sessions, reconciling all outstanding versions, compressing the table or featureclass, then running the sql script.

Yes, multi-versioned views has the same issue where unsaved edits aren't operated on by anything you do to the view. If I use the edit workspace to execute the SQL does that only execute it against that version the workspace is currently pointed to or does it execute it against default only? I was under the impression it was supposed to hit the version the workspace pointed to.

In answer to your question, no this isn't a rare event it will happen a lot and the clients could have 200 or more versions across their enterprise so compressing to base is not an option.

I have the IRows in a IGrouping<ITable, IRow> so I have all the rows to update keyed by table already. With the executeSQL I can loop through them in batches of 1,000 and issue an "UPDATE currentTable SET foreignKeyField = currentGuid WHERE oidField IN (oid1,...oid1000)" and it is screaming fast. I just build the in list from that IGrouping and even having to do that is orders of magnitude faster than Update cursors etc. I really need that tpe of performance but I need it to observe versions and newly created rows in the current edit session. The boss says he doesn't want the user having to save edits before they run this if possible.

[Anonymous User · Accepted solution]

Andy,

ExecuteSQL expressions will only work on the saved state and wont see the editor delta tables within an edit session. You'll have to use ArcObjects.

You can execute sql expressions against multiversioned views to get access to versioned data. Please read SQL access to geodatabase data and its associated topics for the workflow. You'll need to execute a stored procedure to change the version before running your sql statement.
Caveat emptor, I have done this via a sql client in the past, and it works well, but I haven't tried it through the ExecuteSQL method.

[AndyCanfield]

Andy,

ExecuteSQL expressions will only work on the saved state and wont see the editor delta tables within an edit session. You'll have to use ArcObjects.

You can execute sql expressions against multiversioned views to get access to versioned data. Please read SQL access to geodatabase data and its associated topics for the workflow. You'll need to execute a stored procedure to change the version before running your sql statement.
Caveat emptor, I have done this via a sql client in the past, and it works well, but I haven't tried it through the ExecuteSQL method.

If I have a workspace that points to a specific version e.g. the edit workspace pointing to "version A" and I use executeSQL on that workspace does it hit that "version A" or does it hit the default version with the update?

I have used multi-versioned views in the past but mainly for reading versioned data from a non-map interface web client. I don't know if our clients would go for that approach because it requires quite a bit of db specific setup that is hard to shrink wrap so to speak. If executeSQL can't hit versioned data without going through a multi-versioned view I'm not sure really where to go with it. I'll have to ask the boss what they want to do at that point because telling customers that the next release is going to require them to setup multi-versioned views if they want this functionality isn't probably going to go over so well. If that is the only route though then that is what I will have to tell them and they can let me know where to go from there.

[Anonymous User]

If I have a workspace that points to a specific version e.g. the edit workspace pointing to "version A" and I use executeSQL on that workspace does it hit that "version A" or does it hit the default version with the update?

It will hit the base tables. The workspace reference is just used as a handle to the DBMS. The original intent for the method was just to submit SQL commands at the database level so you could conveniently do things like turn tracing on etc.

[AndyCanfield]

It will hit the base tables. The workspace reference is just used as a handle to the DBMS. The original intent for the method was just to submit SQL commands at the database level so you could conveniently do things like turn tracing on etc.

Yes, I just verified this, thank you. Even with open workspace to a certain version it still hits the base tables. I have all the info I need now to be able to take this to the boss and our users and give them their options of a slower IRow.Store with update cursors solution that requires no additional setup or a much faster solution using multi versioned views but it will require a more intensive initial setup by the DBA. Thank you to all who responded, my question has been fully answered.