Extracting results for few records from large SQL Table

I have a SQL Table suppose XYZ which contains more than 2 Million data rows. I need information for 10k data points which I hold in an excel sheet. Now I can use Join with Primary key and get the data which takes longer time as my main table in holding huge number of data rows. Any other way which could find only those 10k records within shorter time?

Thanks in Advance

Soumen

Datasets

Accepted answers

AbhilashR

@soumen - on second thoughts you probably could get away with simply using a simple Input tool. I have attached a simple proof of concept here to help you setup your solution. In here:

Replace my Text Input tool with your excel file containing ID's
Update the Summarize tool to replace my Field1, and Concat your ID field
- make sure you mimic the Concatenate Properties shown in the screenshot below
Select the appropriate field in the dropdown in the macro
Inside the macro oracleREAD.yxmc
- setup the necessary oracle connection in the input tool
- then paste your sql in the SQL editor and add a line in the WHERE clause where you enter something like
  - AND REFERENCE_LOOKUP_ID in ('DUMMY')
Save the Macro and go back to your main workflow and run.

This should hopefully get you the solution you need.

Like most things in Alteryx, there are multiple ways to achieve the same goal - you could use In-Databse tools as well, but try this one to begin with.

sampleSolution.yxzp

All comments

AbhilashR

@soumen - you could embed table XYZ's SQL inside the Dynamic Input tool, and pass the 10k ID's (from excel file) to this SQL during run-time to fetch data only for those records. In other words, once you embed table XYZ's SQL inside the Dynamic Input tool, use its Modify SQL query option to replace a specific string of this SQL (in this case your 10k ID's).

The suggested solution is somewhat dependent on your database type (specially with Oracle), and how well is your SQL written. Oracle I think has a limit of accepting only 1000 ID's in its Where clause - in which case you can embed the Dynamic Input inside a Batch Macro and pass the 10k ID's to this macro in batches of 1000.

Hope this helps.

soumen

@AbhilashR Thanks for the solution. Your are right that Oracle does not allow more than 1000 records and I am using the same. But I cant find the Dynamic Input tool in user interface.

SS attached.Could you check why?

Thanks

Soumen

Capture.PNG

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157