WFA Datasource max records limit 10000

[christoff_brand]

Hello,

I'm working on a datasource for Active Directory groups for permissions via fsecurity. The query from AD is working via powershell and the import via csv file is also working, but if I query the data in a Workflow, then it only shows a part of the groups. The total number is over 30.000 groups. When I look in the Workflow designer setup, the query test shows up to 9999 records.

- So my question is, is there any limit on the SQL select statements?

- And if so, how can I increase the limit?

Thanks in advanced for the help

[abhit]

Hi:

How are you querying the data in the workflow?

Is the select statement as part of any user input query or is it a finder.

If it is a finder, you can go to Administration->WFA Configuration->Advanced page.

There you can change the "Maximum Finder Results".

Regards

Abhi

[christoff_brand]

Hi Abhi,

I actually followed the video howto https://communities.netapp.com/videos/3351 and it seems to work fine for me. I have all groups in the mySQL DB in own schema. I use the user Input field /type query to get an single selection of an AD-Group. When I test the query, the Test results show the AD Groups and a # Field up to 9999.

I guess this is not the finder limit, whitch is currently set to 1000.

Thanks

KR

[paragp]

Hello,

  Yes, unfortunately the UserInput queries are limited to 10,000 results and there isn't a way by which you could increase the limit. We are going to make this configurable in a future release. That said, I don't have a good workaround for you but these might work.

1. Ordering your query results in the UserInput query in a such way that the one you most likely pick come up within the first 10,000.

2. Using 3 or more Query UserInputs to splits those results and using which ever one is valued in the workflow. You would need to write some mvel expressions here to check which Query user input has a value.

thanks,

Parag

[ninja]

Any update on raising this limit?

[sinhaa]

This is an old post, so I'm not sure if @christoff_brand has been able to resolve this.

@ninja

Do you really need User-Input type (Query) to display you more than 10,000 results? Its improbable that you would look through all those 20,000 for 30,000 rows one by one and chose the right one, would you?

Mostly you'll start typing in the User-input to match the desired row. So lets do that in our query itself.  

Have an additional where clause in your query which will do this filtering for you 

Example:

My original query

===

SELECT
wfa_server.hostName, wfa_server.ipv4Address

FROM
wfa_servers.wfa_server

====

Assume this returns 20,000 rows. My desired row is with hostName is : wfa_sinhaa

WFA won't display those 20,000 rows, and neither will I read through all those rows to find wfa_sinhaa and select it. It just won't happen.

So I just modify my query giving anadditional where clause and get another User Input. See how

====

SELECT
wfa_server.hostName, wfa_server.ipv4Address

FROM
wfa_servers.wfa_server

WHERE wfa_server.hostName LIKE '%${wfa_name}%'

====

Now my workflow will have another string User-Input wfa_name. Now During execution my Query will only get executed based on what I provide in wfa_name. So this allows me to easily filter the unwanted rows. And most likely the result is going to be within 10K.

Even if its not, I can narrow down query with additional clauses. See below. 

====

SELECT
wfa_server.hostName, wfa_server.ipv4Address FROM
wfa_servers.wfa_server

Where wfa_server.hostName LIKE '%${wfa_name}%'
AND
wfa_server.ipv4Address LIKE '%${wfa_ip}%'

===

So there is no need to have 30,000 rows to select from.

sinhaa

[ninja]

My question is why not? If I build a query in mysql I expect it to work as I wrote it? I keep having to do workaround already because the interface is so inflexible (that's just another down side which I won't get into). I don't care if it takes longer to populate but I need to control the users of WFA and provide them with the least amount of options as possible. I need 50K values what's wrong with that? Scrolling hurts or typing the first letter to get to it hurts? Why have more fields to populate when you can have one.

There is another post where others have expressed this concern. If the customers ask for a buffet let them have it. Don't give them a #5 at the burger shop just because you believe it's what they want to eat. (no pickles please).

[sinhaa]

@ninja

There are good reasons why this limit was set. And  if you look 10K is actually a high upper limit to have which would be fine in 95%of cases.

Bringing 50K rows all at once will cause extreme performance impact. No SQL client brings so many rows at once. Most by default have it quite low like only 50-100 rows. Right now you are saying you wouldn't care if it takes long but you would if you have to wait for 30 mins just for all rows to be loaded.

If typing the first letter to get the desired row doesn't hurt why would it hurt to type the same on another parameter. I'm not taking away any of your capability. Just do the same on another parameter.

Don't see this as a workaround. See it as query optimization. This what it really works as. The query is only executed after you have provided the first matching letter in parameter. So this actually would work faster as this is certainly going to eliminate a large no. of unwanted rows.

There are no workarounds in designing workflows. Anything that works fine for you, is valid. And if there are possible ways to get it done, its because WFA is flexible. But like programming, it can take some learning to solve complex requirements. 

If you have any queries, post it on WFA communities. We would try to solve them all, I assure you.

sinhaa

[ninja]

One thing to note is that this 10000 limit doesn't apply when you query the workflow via REST or SOAP... it will return more than 10000 fields.