When you deal with large amount of data, every detail is important in terms of performance. Today I want to show you how insert time and log size changes depending on factors like indexes and TABLOCKX hint.
Our test table contains 0.5 billion rows and 5 columns.
There is a lot of situations when you need to add new partitions to existing, partitioned table. We can perform it in pretty simple way.
I’ve created partitioned table with following partitioning function.
CREATE PARTITION FUNCTION udf_PartitionYear(date)
AS RANGE RIGHT
And partition schema.
CREATE PARTITION SCHEME ups_PartionYear
AS PARTITION udf_PartitionYear
ALL TO ([PRIMARY])
As you can see there is 23 partitions.
SELECT partition_id, partition_number, used_page_count, row_count FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID(‘dbo.GHCN_PARTITION’)
XMLA endpoint is a protocol for communication between client applications and Analysis Services engine. Power BI is based on tabular engine. So, for people who are experienced with Analysys Services Tabular model development. XMLA endpoint is nothing new. But for Power BI developers is a game changer.
That way you can connect to datasets or entire workspaces. Read permission is set by default. If you want to turn write permission on you can do it in admin portal.
Power BI services brings great feature Deplyment Pipelines. You can separate your enviornments and synchoronize them in an easy way.
Every environment has it’s own workspace. You can name it whatever you want. Here is an example with one report created. Everything is in sync.
In March 2021 Power BI got new IF.EAGER DAX function. What is the difference between “new” and the “old” IF?
IF function is strict evaluated which computes TRUE or FALSE result only when condition is met.
For example: IF (5<2, 5+2, 7+1). Engine computes only the second part of T/F section (7+1), (5+2) isn’t computed.
IF.EAGER function is eager evaluated. Computes TRUE and FALSE results regardless the condition is met or not.
Example: IF(5<2, 5+2, 7+1). Engine computes first and second part of T/F section (5+2), (7+1).
Now, you can ask a question. Why we shoud use the second one?
Today I show you three examples of deleting rows and it’s performance. I will compare deleting rows on non-partitioned table and truncating partition on partitioned table.
Our example is a table containing 0.75 billion records. [Date] range is 2000–01–01 to 2020–12–31.
When you don’t want to load your model into memory, you use Direct Query mode. As you now, gap between in-memory and Direct Query performance can be immense. We can use Direct Query mode and attain good effectivness using Direct Query on Columnstore Clustered Indexed table.
I prepared two identical tables.