topic Re: Stressing the performance query againt one table on a warehouse in Data Warehouse

Stressing the performance query againt one table on a warehouse

pmscorca — Wed, 16 Oct 2024 13:57:00 GMT

Hi,

on a warehouse I've one table with 33 columns, having some datetime columns, some varchar columns, some bit columns and some integer columns.

I need to stress the performance query on this single table: I've already tried to use some DATEDIFF functions, registering good query times, but I'd like to proof some queries more time intensive, considering that a warehouse is based on delta format.

Any suggests to me, please? Many thanks

Re: Stressing the performance query againt one table on a warehouse

FabianSchut — Wed, 16 Oct 2024 14:34:20 GMT

Hi pmscorca,

There are several SQL queries that you can use for stresstesting. You can think of:

Reporting Queries with Complex Aggregations
SELECT
CustomerID,
SUM(TotalAmount) AS TotalSpent,
COUNT(OrderID) AS NumberOfOrders,
AVG(TotalAmount) AS AverageOrderValue
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY CustomerID
ORDER BY TotalSpent DESC;

Recursive Common Table Expression (CTE)
WITH RecursiveOrgChart AS (
SELECT EmployeeID, ManagerID, 1 AS Level
FROM Employees
WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.ManagerID, roc.Level + 1
FROM Employees e
INNER JOIN RecursiveOrgChart roc ON e.ManagerID = roc.EmployeeID
)
SELECT *
FROM RecursiveOrgChart
ORDER BY Level;

Window Functions over Large Data Sets
SELECT
EmployeeID,
Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
FROM Employees
WHERE HireDate BETWEEN '2000-01-01' AND '2023-01-01';

Lag and Lead Functions
SELECT
StockID,
PriceDate,
ClosePrice,
LAG(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate) AS PreviousClosePrice,
LEAD(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate) AS NextClosePrice,
(ClosePrice - LAG(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate)) AS PriceChange,
(LEAD(ClosePrice, 1) OVER (PARTITION BY StockID ORDER BY PriceDate) - ClosePrice) AS PredictedChange
FROM StockPrices
WHERE PriceDate BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY StockID, PriceDate;

Re: Stressing the performance query againt one table on a warehouse

pmscorca — Wed, 16 Oct 2024 15:45:41 GMT

Hi, thanks for your reply.

Saying again that I need to query only on a single table without joining with other tables,

I've noticed that sometimes when a query is executed for the first time, in a new SQL query window or in an idle existing SQL query windows, the query time is more high than the next runs: is it a normal behaviour? Why?

Thanks

Re: Stressing the performance query againt one table on a warehouse

FabianSchut — Wed, 16 Oct 2024 17:08:14 GMT

Hi pmscorca,

All the query examples that I gave can be executed with a single table. The Recursive Common Table Expression (CTE) example that I gave uses a recursive cte on the same table, it uses the same table twice.

About your first time execution, that is normal behaviour, as can be seen in this documentation: https://learn.microsoft.com/en-us/fabric/data-warehouse/guidelines-warehouse-performance#cold-run-cold-cache-performance
"The first 1-3 executions of a query perform noticeably slower than subsequent executions."