Direct Query performance Issue

[Baskar]

Dear Team, 

  Need help to understand what's wrong with my model  with Direct connection ( Oracle ).

Source : Oracle 

Connection Type : Direct 

Table used : Team Members ( Max : 57,879Rows ) , Address ( Max : 4,00,000 Rows )

Direction : One ( Address ) to Many ( Team Members )

Used Visual : Table

Direct Query Executing less than 700ms , But DAX Query taking  ( 54067 ms ). 

Note : 

    There no DAX measure or  DAX Calculation. All the columns coming from Source.

    There is no Power Query transformation.

Even there no document from Microsoft/Blog to find what is going on there in Formula engine. 

Can anyone help us. 

@amitchandak @parry2k @Greg_Deckler @MFelix @MattAllington @Ashish_Mathur @ImkeF 

[v-shex-msft]

Hi @Baskar,

Can you please share some more detail about the 'FE' part that spend most of the duration to process?

How to Get Your Question Answered Quickly 

In addition, have you traced the network status of remote access? It may also affect the performance if these queries are pending for network connection and delay.

Regards,

Xiaoxin Sheng

[Baskar]

How we can measure the FE timing ? in DAX studio. 

[v-shex-msft]

HI @Baskar,

FE and SE seem to mean the 'formula engine' and 'storage engine'.  Perhaps You can take a look at the following blog about the difference between the two engines:

Formula engine and storage engine in DAX 
Regards,

Xiaoxin Sheng

[Baskar]

@v-shex-msft 

Thanks. Here my problem is FE taking more time than SE. 

Nothing is there in my model... 

Want to know whats went wrong here. 

[v-shex-msft]

HI @Baskar,

Tasks of the formula engine

The formula engine converts a DAX query into a query plan with a list of physical steps to execute. Each step in the query plan corresponds to a specific operation executed by the formula engine. Typical operators of the formula engine include joins between tables, filtering with complex conditions, aggregations, and lookups. Some of these operators require data from columns in the data model. In these cases, the formula engine sends a request to the storage engine, which answers by returning a datacache. A datacache is a temporary storage area created by the storage engine and read by the formula engine that contains the result of a storage engine query.

Datacaches are not compressed; datacaches are plain in-memory tables stored in an uncompressed format, regardless of the storage engine they come from. Because the formula engine is single-threaded, any operation executed in the formula engine uses just one thread and one core, no matter how many cores are available. The formula engine sends requests to the storage engine sequentially, one query at a time. A certain degree of parallelism is available only within each request to the storage engine, which has a different architecture and can take advantage of the multiple cores available.

I think it may mean the formula engine has handled some processing of the storage engine. Since it is single-threaded, if it invoked multiple times, it may use some additional time to process the queue of steps.

Regards,

Xiaoxin Sheng

[amitchandak]

@Baskar ,

Direct Query, means SQL - Please

and For DAX, please share the columns you are using in DAX visual.

[Baskar]

Thanks Superman @amitchandak 

No DAX.

used direct column