Map and Transaction - long running query

[stephenho]

Hi all, I'm running a query and the results are taking too long to appear, so I was wondering if you can help me out.

I've got a set of events that start and stop based on a transaction.

eg.
user1 - starts
event = 0
user1 - ends

There's a bit more to it, but essentially that is it. So i run the transaction command to grab the 3 events and pump out the username into a file. This part works fine.

I then use the input lookup file and get the usernames to run a query using the map command.

|inputlookup mylookupfile 
|map search="search
    index=myindex process=ProcessType
    |transaction myusername  startswith=\"starts\" endswith=\"end\"
    |where event=1 and username=$usernameFromLookupFile$"

My understanding is that map will iteratively go through the values in the inputlookup file and run the query. I know it's very heavy, but are there any alternatives for what I want to do ? The inputlookup file contains approximately 10000 usernames.

Thanks in advance,

Steve

[martin_mueller · Accepted solution]

You're essentially doing a full transaction over your entire index for every single on of the 10000 users, and after building the transactions you filter by username=$foo$ - that's highly inefficient.

You say you want to dump the usernames into a file - have you considered dropping the inputlookup, running the transaction once, dropping the where username=$foo$, and dumping those usernames into the file? This way you would lose 9999 of your 10000 full transaction queries.

[martin_mueller]

What you do then depends on your specific use case. If you want to make sure only users in your lookup file make it through you can add a |lookup after the transaction and filter out those that don't exist in the lookup - still only one transaction.

[stephenho]

Hi Martin, thanks for the response. Now that you mention it, it does sound like a good idea. So what you're saying is, run another transaction search with event=1 dump those names into a file and do a NOT IN join over the 2 look up files?