Background

Core of any BI tools is to acquire business data from multiple sources and then to co-relate it for quality reports & dashboards. IT being dominantly used for business process from last 20 years, volume of data has grown a lot as on today. Businesses have shifted to enterprise database platforms like SQL Server, Oracle, etc, which have capability to store, fetch & process voluminous data efficiently in optimized manner.

Modes of connectivity in Power BI

Power BI supports 2 modes to connect with data, Import & Direct Query
Import Mode: In this mode, Power BI connects with underlying data source & downloads entire data from the datasource. This data is stored in Power BI model. Fresh copy of this data can be downloaded by pressing Refresh button. PBIX file internally stores model data in compressed format. This published datset model on Power BI Service, internally is stored on Common Data Model, which is sort of Azure Managed SQL Server instance in the backend.
Direct Query Mode: In this mode, Power BI connects to data source, but do not downloads entire data from the datasource. Instead, it generates SQL Query (or any equivalent) to generate data for specific visualization & fires it to underlying datasource to utilizing optimization feature of underlying datasource engine.

Differences

Import
Direct Query

Storage
Stores data into model
Do not store data

Volume
At max can store 1 GB data in model
No restriction

Performance
Fast with less volume, but degrades when volume increases
Fast if proper indexes are created on database, otherwise might underperform

Compatibility
Compatible with every type of data source
Supported for Database Server type of data source

RLS (Row Level Security)
Supports RLS
Supports RLS. But proper care needs to be taken since one might use Service Accounts to connect to database which practically impersonates identity of Power BI user

DAX & Transformations
Supports all the Power Query transformations & DAX
Supports only those transformations & DAX, for which Power BI is able to generate equivalent SQL Query

Refresh Schedule
User have option to set automatic refresh of data on scheduled interval based on subscription
Since data is not stored in model, concept of scheduling refresh is not applicable. Data is fetched from server when user opens report

Availability
If data refresh fails due to unavailability of datasource, last data persisting in model is used to prepare visuals
If data refresh fails due to unavailability of datasource, then entire report goes blank, as data is not stored in model. No option to go back to previous state

Why Direct Query outperforms Import, in majority scenario ?

To know reason behind this, one needs to understand how visuals are prepared. Any visual be it a Column Chart, Line Chart, Card, Matrix, etc, needs an underlying tabular data. This tabular data is visually arranged/plotted by Power BI on the visual.
Power BI most of the time is doing below mentioned operations:
Merging
Grouping
Aggregating
Filtering
Let’s understand this in detail. When we drag any field in row/column of matrix/table or X/Y/Legend axis of Column/Line chart, internally Power BI is grouping the data to arrive at unique values in those fields. Upon dragging any field or measure into values box, internally Power BI applies aggregation operation like SUM,COUNT,AVERAGE,MIN,MAX etc. When multiple tables are connected using relationship, upon dragging any of the field/measure from those columns, internally merging of those tables is done by Power BI in the background. And upon selection of value(s) in slicer or filters, filtering operation is done.
Power BI has 2 mighty hands, DAX (powered by SQL Server Analysis Service) and M Script (powered by Power Query), to perform the ETL jobs and visualization related calculations. Both of these engines are efficient with their way of performing calculations. But when it comes to performing calculations in an optimized way, both under-performs compared to database engines, since they are in-memory calculation engines best suited to play with data. They lack indexing of data, which database handles while storing of data. Also, database engine re-uses query results of last few queries, by keeping track of changing data, which is something complex for both DAX & Power Query. As the data size grows, performance difference is quite noticeable.

In which scenario Import mode outperforms Direct Query ?

It’s a myth that Direct Query is always faster than Import. There can be scenarios where-in reverse is observed
Not using indexing feature of database. This might create situation where-in database server is taking too much time to read data from disk, where-as in-memory calculations by Power BI itself might work a little faster.
Running database server with very low resource (like CPU, RAM, etc). In this scenario, database server might be badly struggling with resources to fetch data. Although Power BI Service runs on shared Azure resources, it might outperform in this case.
Many concurrent users querying database. Power BI practically fires multiple SQL queries for multiple visuals. If there are locks on table, then it may lead to long wait freezing Power BI visuals.
Long connection time to database. This happens when database connection request is made to on-premises server over VPN having high latency. If volume of data is not quite large, then it would be wise to import data instead of direct query. With import one always enjoy good service of Azure resource in back-end on Power BI Service.

Is Direct Query real-time ?

No, one should make a distinction between Live and Realtime. Direct Query is a live connection. Whenever report is opened, fresh connection to database server is established to pull the latest data. But the visual will not automatically update for the changes in database affecting visual like a security market ticker. After opening screen, it will become static. Only after pressing refresh or re-opening of the report will load fresh data.