Query tuning issues

I'm in trouble trying to tune this following query. I used to be 100% focused in the infrastructure side of the stuff, but since August this year I'm working for a processing card company that is demanding just my "dark side" - query tuning skills.

I'd appreciate if you could provide me suggestions and tips to improve the performance of query.
Here are the tables:

```
CREATE TABLE [dbo].Delivery NOT NULL,
[Address_Name] varchar NULL,
[Address_Street] varchar NULL,
[Address_Complement] varchar NULL,
[Address_City] varchar NULL,
[Address_District] varchar NULL,
[Address_Number] varchar NULL,
[Address_State] varchar NULL,
[Address_ZipCode] varchar NULL,
[OsFk] [int] NOT NULL,
[WebstoreOrderId] [int] NOT NULL,
[CourierType] [int] NULL,
CONSTRAINT [PK_Delivery] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].OrderItem NOT NULL,
[CardId] [bigint] NULL,
[WebstoreOrderId] [int] NULL,
[WebstoreEcommerceTypeId] [tinyint] NULL,
[WebstoreChannelId] [int] NULL,
[OS_Id] [int] NULL,
[Delivery_Id] [int] NULL,
[TrackingCode] varchar NULL,
[WebstoreOrderDate] [datetime] NULL,
[IsBipped] [bit] NULL,
[WebstoreCategoryId] [int] NULL,
[WebstoreOrderItemId] [int] NULL,
[Product] [int] NULL,
[IsPrePrinted] [bit] NULL,
CONSTRAINT [PK_OrderItem] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].Os NOT NULL,
[Status] [int] NULL,
[OSType_Id] [int] NULL,
[CreatedAt] [datetime] NULL,
[ParentOS_Id] [int] NULL,
[HasChip] [bit] NULL,
[HasProcessingError] [bit] NULL,
[Product] [int] NULL,
[IsPrePrinted] [bit] NULL,
CONSTRAINT [PK_ServiceOrder]

I would remove all the subqueries from your inner query named "QUERY" and create temp tables or CTEs instead.

Reason, each of the subqueries in your select statement are evaluated for every record your outer query generates, dragging down the performance or your query and potentially causing performance issues for the entire environment.

Below is an example to replace your order count subqueries with a join to a CTE containing a dataset of your order counts. If your dataset is large, you can change the CTE to a temp table, giving your the flexibility to add indexes or create your own statistics.

;WITH CTE_ORDER(OS_Id, OrderCnt)
AS
(
    SELECT 
        OI.OS_Id,
        COUNT(*)
    FROM OrderItem OI 
    GROUP BY OI.OS_Id
)

SELECT 
OS.Id, 
CASE WHEN OS.Product = 0 THEN ORDER.OrderCnt ELSE NULL END AS StripeBlankCardCount,
CASE WHEN OS.Product = 1 THEN ORDER.OrderCnt ELSE NULL END AS ChipBlankCardCount, 
CASE WHEN OS.Product = 2 THEN ORDER.OrderCnt ELSE NULL END AS StripePrePrintedCardCount, 
CASE WHEN OS.Product = 4 THEN ORDER.OrderCnt ELSE NULL END AS ChipPrePrintedCardCount 
--(SELECT COUNT(1) FROM OrderItem OI WHERE OS.Product = 0 AND OS.Id = OI.OS_Id) AS StripeBlankCardCount, 
--(SELECT COUNT(1) FROM OrderItem OI WHERE OS.Product = 1 AND OS.Id = OI.OS_Id) AS ChipBlankCardCount, 
--(SELECT COUNT(1) FROM OrderItem OI WHERE OS.Product = 2 AND OS.Id = OI.OS_Id) AS StripePrePrintedCardCount, 
--(SELECT COUNT(1) FROM OrderItem OI WHERE OS.Product = 4 AND OS.Id = OI.OS_Id) AS ChipPrePrintedCardCount, 
FROM dbo.Os OS 
    INNER JOIN dbo.OSType OSType 
        ON OSType.Id = OS.OSType_Id 
    INNER JOIN CTE_ORDER ORDER
        ON ORDER.OS_Id = OS.Id

Hope this helps!

EDIT: Corrected the name of the CTE and column name.

StackExchange Database Administrators Q#124442, answer score: 4