ODBC connection to oracle

[KentCampbell]

Hello,

I have been pulling data using an access file for input in the past, but I want to get away form this because I don't like being reliant on the file and because there are linked tables Alteryx can pull from.  When I take an exact copy (like copy and paste) of the query from access into Alteryx using an input tool and the Alteryx file it works in a minute or 2 (only trying to pull 282 rows).  However if I switch Alteryx to go a data source and Oracle ODBC connection it can run for literally hours (i've never gotten it to complete).  I've double checked the query many many times and the query is written correctly for oracle I understand from what I've read that the ODBC has more overhead than other connections but why would it take literally hours to pull 282 rows from a table?  I think I have to be doing something wrong, but I've tried different connections and nothing seems work.  The only option I seem to have is insert the linked tables in access to a local table then have Alteryx pull the access file.

[kuoshihyang]

Can you share how you are connecting via ODBC?  282 records is trivial for Alteryx even if connecting through ODBC. Screen shots or images would be appreciated. Thanks.

[rfoster7]

It really sounds like either a problem with your Query or with the Data Connection itself. 

Are you using a gallery connection or a local connection? 

Can you try an OCI connection?

oci:SCHEMANAME/__EncPwd1__@DATABASESERVER:61000/SERVICE_NAME

or can you send us what the odbc connection looks like? 

Also can you share the Query? Replace the tables with T1, T2, etc if you are worried about anonymity. Mostly we need to see the joins, where clause and anything that could be causing Cartesian joins or something else that could be clocking the query. 

If you can't share the query, do you have DB admins that can run explain plans on the query?

[KentCampbell]

Hopefully the screen shots above work, the essence of the query is below (simply removed specific naming)

SELECT columnName
Sum(dollars) AS SumOfDOLLARS

FROM database.tblName
GROUP BY columnName
HAVING
(
columnName Like 'AD HOC%'
OR
columnName Like 'MIN%'
OR
columnName Like 'INSERT%'
OR
columnName Like 'FEDERAL%'
OR
columnName Like 'DHL%'
OR
columnName Like 'REBILL%'
OR
columnName Like 'UPS%'
)
AND columnName Between '2022-04-01'
AND '2022-04-30'

[KentCampbell]

I shared the query above i'm pretty sure its ok.  I have the option to use an OCI connection but I don't know how to find a TNS Server Name.  Using the DSN from the ODBC connection as the TNS server name, its running but hasn't completed after 10 minutes so far

[rfoster7 · Accepted solution]

Okay yeah. 

So HAVING is the slowest clause as far as database statistics go. In ACCESS, it actually runs the sql statement without the having clause, then does a filter in access instead of running it against the DB. so that's why you are seeing this. 

You need to move your filters to the WHERE clause OR you need to pull all the data into Alteryx and filter it there. I would try both and see which works better. 

either:

select
columnName,
sum(dollars) as SumofDollars
from
database.tblname
where
idh_revenue.idh_billing_date between '2022-04-01' and '2022-04-30'
and (columnname like 'AD HOC%'
or columnanme like 'MIN%'
or columnname like 'INSERT%'
or columname like 'FEDERAL%'
or columname like 'DHL%'
or columname like 'REBILL%'
or columname like 'UPS%')
group by
columnName

This moves your filters to the where clause instead of the having clause and puts the date filter first

OR: 

select
columnName,
sum(dollars) as SumofDollars
from
database.tblname
where
idh_revenue.idh_billing_date between '2022-04-01' and '2022-04-30'

Then put a filter tool behind the input and do your string filtering there. I think that would work best for you. Alteryx is FAST in processing large volumes of data. 

[Sebastiaandb]

Hi @KentCampbell ,

TNS name is basically the database name, i understand you can't share it with us but you might want to plugin what you have now behind "DNS = " in the TNS name box.

Otherwise, if you have a query tool like Golden or SQL viewer, it should connect to the database and normally you see something like TEST@a334wr . The part behind the @ is then the database name and the name you need to fill in as TNS name. 

I really  believe in getting that OCI running, we never use ODBC for Oracle connections :-). 

Greetings,

Seb

[KentCampbell]

I tried using the where statement compared to having and so far its been running for over 40 mins (i'll let it go for a while longer just in case) so I don't think using the where statement is going to fix the issue unfortunately.  Any other ideas/tips?

[rfoster7 · Accepted solution]

Yup. just pull all the data based on date (first where clause) and then do your string filter inside the workflow. 

Alteryx is really fast at it. 

[KentCampbell]

That did it thank you!  I don't have any DBA experience and I've never had issues with how long queries took historically so this is a bit of a learning curve for me.