Question

DatabaseUpdater locking table

  • 9 October 2018
  • 4 replies
  • 8 views
N

I have an issue with a DatabaseUpdater command.

It seems to initiate a SELECT * FROM MyTable before it does the update SQL command which is fine for one of my tables as I don't have masses of data so it completes quickly then carries on and does the updates. One of my tables however has millions of rows so doing a SELECT * on this is clearly never going to work and basically locks the table out and becomes unusable.

Here is my flow so a csv input passes through a tester then calls to the DatabaseUpdater

SQL Trace:

Does anyone know why it needs to do the SELECT * and how I stop it from doing it so my updates run?

4 replies

erik_jan
Rank
Userlevel 2
Badge +16

Just a guess, but are the attributes in the WHERE clause indexed?

Instead of using the DatabaseUpdater you can also use the SQLExecutor transformer to do the update on the table.

ravenkopelman
Rank
Badge

I would expect `SELECT * FROM MyTable WHERE 1=0` to be run in order to collect schema information about the table that needs to be updated. `WHERE 1=0` should make that fast regardless of the size of the table - your trace image suggests that the WHERE clause is not being included though.

Can you try with the JDBC variant of the SQL Server format and see if that improves things?

N

I would expect `SELECT * FROM MyTable WHERE 1=0` to be run in order to collect schema information about the table that needs to be updated. `WHERE 1=0` should make that fast regardless of the size of the table - your trace image suggests that the WHERE clause is not being included though.

Can you try with the JDBC variant of the SQL Server format and see if that improves things?

I did indeed have the WHERE 1=0 trace calls as well which were quick as you say, but for some reason this was doing a full table scan as well.

 

I tried the JDBC connection, but got errors on the connection stage so I must have done something wrong there (FME is very new to me!) so not sure if that will work or not.

 

Got it working using SQLExecutor as suggested by @erik_jan.

 

N

Just a guess, but are the attributes in the WHERE clause indexed?

Instead of using the DatabaseUpdater you can also use the SQLExecutor transformer to do the update on the table.

The WHERE fields are not indexed, but I can't see this as being the issue as it was running the full table scan anyway without the WHERE that was the issue.

 

Managed to take your suggestion of the SQLExecutor though and all working now so thank you!

 

 

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.