Can SQLExecutor be used to get around having to read in all the data from a database table into FME and then running a feature joiner.

Hi @elpone​ ,

Sounds like there are two things you could be doing here. 

First, since the first transformer is a tester, you can likely use a WHERE clause on the (LFS and SILC) reader feature type's to limit the data being read in from the database. 

Second, use the DatabaseJoiner. The DatabaseJoiner transformer is similar to the FeatureJoiner or FeatureMerger, but instead of merging two streams of features and an in-memory join, it merges one stream of features with data read from an external database. The DatabaseJoiner allows features to be joined without having to read the entire dataset into a workspace. FME can just query the database and select the individual records it needs. This can improve performance greatly. More details in linked DatabaseJoiner article above.

Also, be sure to check out the let the database do the work articles as it will provide you with more tips on how to improve performance when working with DB formats!

Hope that helps!

Why not concatanate excel features with comma separator and then use one feature in SQLExecutor?you might need some modification to aggregated attribute if it's of text type. (this should work for int)

First, you really want to avoid calling the SQLExecutor many thousands of times, it's going to take a very long time.

Secondly, seeing the size of the database table (2+million features), the best performance may actually be had by first writing the two Excel sheets to the same database, indexing the keys and the using e.g. the SQLCreator to join everything together in the database before the data is read back into FME.

I'm not sure how well that'd work with more than 270'000 Excel features...

Why not concatanate excel features with comma separator and then use one feature in SQLExecutor?you might need some modification to aggregated attribute if it's of text type. (this should work for int)

Hi @edgarrunnman​ ,

Thanks that looks an interesting proposition.   In the case of SQL server there may be a limit on the number of elements that can go into the In Clause. (https://dba.stackexchange.com/questions/228695/is-there-any-limit-for-in-results-in-sql-server) of 32,000.  This post also points to the solution which strikes me as a possible and elegant way to do it which is to create a temp table from our input and then use this as  part of  the SQLExecutor

CREATE TABLE #MyTempTable
(myeircode varchar(8) not null,
zones integer not null);
 
--This is the bit that I'm not sure how to implent in SQLExecutor
INSERT INTO #MyTempTable
(myeircode, zones)
--suspect there is a limit to the number on this  can be called (1000?)
VALUES
('p31xxxxx',1),
('p31yyyyy',1),
(p31zzzz',2);
 
--Now use the temp table
Select * 
 FROM dbo.[AddressTable] as GD
 WHERE GD.[EIRCODE] IN
  (
  t.myeircode
  FROM #MyTempTable as t
  );

Hi @david_r​ ,

I was thinking of this as a possibility, but in a production environment I don't think I want to make the database writable to random users and there also needs to be a clean up whereby the data put into the database then needs to be deleted. I'm too paranoid to think nothing can go wrong with a create-insert-delete process (🤔😔). I also will not know the scale of the number of records as that will be variable and will not be in a predicable order of magnitude for the input pipeline, it may be; 50, 500, 5000 or 50000.

A user can create a temp table even if they do not have Create Table permissions on the database which is the use case I have in mind.

Hi @edgarrunnman​ ,

Thanks that looks an interesting proposition.   In the case of SQL server there may be a limit on the number of elements that can go into the In Clause. (https://dba.stackexchange.com/questions/228695/is-there-any-limit-for-in-results-in-sql-server) of 32,000.  This post also points to the solution which strikes me as a possible and elegant way to do it which is to create a temp table from our input and then use this as  part of  the SQLExecutor

CREATE TABLE #MyTempTable
(myeircode varchar(8) not null,
zones integer not null);
 
--This is the bit that I'm not sure how to implent in SQLExecutor
INSERT INTO #MyTempTable
(myeircode, zones)
--suspect there is a limit to the number on this  can be called (1000?)
VALUES
('p31xxxxx',1),
('p31yyyyy',1),
(p31zzzz',2);
 
--Now use the temp table
Select * 
 FROM dbo.[AddressTable] as GD
 WHERE GD.[EIRCODE] IN
  (
  t.myeircode
  FROM #MyTempTable as t
  );

This was got to work in the end. Overall the run time reduced from 1'30 down to 2.5 seconds.  Thanks to @edgarrunnman​  and @david_r​  for their suggestions.  I'll answer my own question to close this off.  The use case was implemented on MS SQL server but should apply to other RDBMS as well.

The steps are firstly to create a temp table via a SQLExecutor with the SQL Statement:

FME_SQL_DELIMITER ;
create table $(targetTempTable)
(variableName varchar(7));

In this case I have used a private parameter for a temp table name which in SQL server starts with #.  This removes the need for the end user to have to worry about the name and ensures a temp table is created.   Temporary tables are dropped when the session that creates the table has closed. (Details)

Once the table is created you use your input to populate your temp table with the values you want to use as a filter on the data base joiner with an exhaustive prefetch query:

select*
from SomeDataBaseTable
WHERE eircode IN
(SELECT eircode from $(targetTempTable))

The entire workflow is below

Why not concatanate excel features with comma separator and then use one feature in SQLExecutor?you might need some modification to aggregated attribute if it's of text type. (this should work for int)