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

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/

Best Practices

Optimization

Database Connection

App Builder

Accepted answers

NeoInfiniTech

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).

All comments

NeoInfiniTech

Hi @Mike_Carter,

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

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703