Using a list or csv to input a query into a database

[Mike_Carter]

Hi all,

I am fairly new to Alteryx and also dont have a great knowledge of SQL.  Here is the problem I have and any suggestions on best fix would be great.

In MS SQL I have a database of 65million VRMs,  on occasions we need to pull the vehicle details for a list of VRMs (in current example 54k VRMs) normally i would use excel to add 'VRM', to the list of VRMs then paste them into a WHERE clause.  I would use that against the DB to filter just the records I need.  in a perfect world I would want to create this process as an app so that anyone could link their file of VRMs and the process would pull back the information they needed.

When I try to do this I add an input file for the 54k VRMS and I join that list to the DB with the 65m records, but the query takes forever (sorry not on server so it has to connect).

is there a way to convert a list file and then add that to the query so I can reduce the load time?  Or is there a way to only read the DB against a pre defined list without adding to the Query/

[NeoInfiniTech · Accepted solution]

Hi @Mike_Carter,

Are you trying to do this with regular Input Tool to connect to the database and run a query?

If so, I would recommend that you use the In-DB tools. You can run queries on server memory and obtain query results much faster, in addition to being able to stream the data In-DB to join with the table on SQL server.

If you are already using the In-DB tools and still getting a slow query response, it might be due to low upload speed from where you are connecting to the server.

It would also be possible to create a query dynamically from a given list, which would require the use of Dynamic Input In-DB and the tools necessary to build the query (likely using Concatenate method in Summarize tool), which would be attached to the Dynamic Input In-DB tool with the addition of the Connection Name using a Formula tool.

If the list is too large, it might also be necessary to build multiple queries (split dynamically or split based on record ID).

[apathetichell]

@NeoInfiniTech is correct in that dynamic input-in-db is the best. having said that --- if you have write access to your db you can bring your csv/excel input in your db via datastream in --- and then do a join in-db on the VRM.

[Mike_Carter]

I was not aware of the In-DB so thank you