Basic Query Takes way too long to run

[fowlerc]

So I basic query looking at individual vouchers to basically start transforming into a Sales Analysis an Forecast is taking entirely too long.

The Query in SSMS (SQL Server Management Studio) takes less than a second.

That same query when connected In-Database in Atleryx also takes less than a second.

As soon as I try to bring it into designer, or just start with the Basic Input Tool, it takes nearly 7 minutes to run.

I have tried changing the connection types (OleDB to ODBC and back), I have tried deleting the connections and recreating them, I have tried closing and opening the software.  Nothing seems to improve this.

At a pace of 7 minutes for something this simple, the software is un-useable.

What else could be done to fix this?

NOTE:  I just updated to the most recent release of designer yesterday (3/4/21) 

[Ben_H]

Hi @fowlerc,

You need to be careful using basic input tools with database connections as they tend to want to draw down the entire table into memory. I'd double check what your query is doing and how much data you're getting when you run it.

Out of interest why can't you continue to use your In-Database connection with a Datastream out tool to convert into a normal workflow?

Regards,

Ben

[fowlerc]

I did use the datastream out tool.  I just wanted to see how long it would initially take to pull the data.

It is 146k rows of data, with 15 columns.

In the Database the query runs just like it does in SSMS, but streaming the data out of the database, to just a browse tool, takes 7 minutes.

[Ben_H]

Hi @fowlerc,

That does seem like an awfully long time for that amount of data, it sounds like it may be a more technical problem to do with the connection itself or the machine you're pulling the data onto.

Presumably you don't have similar speed issues reading similar sized sets of data from files? csv etc?

[fowlerc]

Just saved the data from SSMS as a CSV and used the file and you are correct...it pulls that just fine.

I am not sure if a server update or something caused this issue, as I wasn't having these sort of lag issues in the past.  I have been using the software for about a year now.

The machine I am on is more than up to the task.

[Ben_H · Accepted solution]

Hi @fowlerc

I've tried searching the community archive on this and there is one and haven't really found much that's useful.

Given that it's not your machine it must then be the connection or something to do with the table itself - I think you may need some help from someone more knowledgeable that me at this point.

The typical advice is to make sure that you've already filtered down to the specific data and columns you need before exporting but it seems like you've probably already done that. The examine the table itself and make sure it looks sensible.

I can only vaguely remember one time sort of thing happened to me in the past and it was something to do with a dynamically generated column in the data I was pulling. As I say I only have a vague memory of it.

Sorry I can't help any further!

Regards,

Ben

[fowlerc]

That was it...I had 1 column in the query that was, in a way, being dynamically generated.

By removing it (I can find a way to get the data back in the workflow using other table joins) it only takes about a second to run.

Thank you sir.  Didn't think to check how the columns were being put together, only that it was pulling what i thought was basic information.

[Ben_H]

Hi @fowlerc,

No problem, sorry it took so long to occur to me!

Regards,

Ben