Is it possible to use dynamic input for xydb files?

[Luca]

Hi All, 

I am looking for a way to dynamically select rows from a XYDB file. I don't think I am able to do it using the dynamic Input tool. 

Any other solution that does not require the use of macros?

Thanks a lot!

[jamielaird]

Hi @Luca 

What exactly do you mean by dynamically selecting rows?

If you want to select a specific range of rows you could use the Filter or Select Records tools.

It depends on what you're trying to achieve.

[Luca]

Consider a scenario like the above. 

I am trying to avoid pulling the entire XYDB file and then using the join tool. I would like to retrieve only the rows associated to the order list file.

[danilang · Accepted solution]

Hi @Luca 

There's no way to read only a portion of .yxdb file.  Any input reads the whole file.  Unless you have millions of rows in the file or you're short on RAM, the read-and-join technique that you show is the way to go.   In any case, if there was some way to filter the results on read, it would take at least the same amount of time since a logical "Join" would be performed while reading the file.  The only benefit would be less RAM used once the file was read. 

Dan

[michaelhimmel]

Hi there - question about this statement:

Unless you have millions of rows in the file or you're short on RAM, the read-and-join technique that you show is the way to go.

---

So, if you do have millions of rows (or tens of millions, or hundreds of millions), is there any alternative?  My understanding is that caching is not a feasible option if the yxdb is updated with new data in between runs, is that correct?

[danilang]

@michaelhimmel 

Just to be clear, it you have any amount of data and you need to read ALL of it, a xydb is the fastest method available.  With huge amounts of data you'll be limited by available RAM, but this would apply no matter how the data was stored 

If you do have huge amounts of data and you only want a portion of it, then using a yxdb may not be the best way to go.  In this scenario, you'd normally put the data retrieval load on a beefy data box, either SQL/no-sql/cloud, etc and just send it a query.  The results are returned to you and you can continue processing. 

If you don't have access to a back-end database, you can look at using Alteryx's Calgary toolset.  Calgary is an streamlined, very fast indexed retrieval system that doesn't require a back-end data base.  It can handle up to 2B records and allows you to set retrieval criteria.  Look here and here for more info about Calgary

Dan