Basic Query Takes way too long to run

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)

Input

Settings

Server

Help

In Database

Database Connection

In-Database

Accepted answers

Ben_H

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

All comments

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703