SQL Server indexing and query performance are often the difference between an enterprise .NET application that remains responsive as data grows and one that becomes increasingly slow under real workloads. In ASP.NET Core applications, the database can be the dominant part of an API response even when the C# code itself is efficient.
This is particularly important for data-intensive systems such as shipping software. A platform like ShippingPortal can work with shipment orders, sender and receiver addresses, carrier services, tracking information, activity logs and operational reports. Poor indexes or inefficient SQL can therefore affect shipment search, address lookup, tracking, reporting and other workflows at the same time.
This guide focuses on practical SQL Server indexing, query optimization and performance-tuning techniques for ASP.NET Core and .NET applications. It also connects those techniques to Dapper, ADO.NET and real shipping workflows including carrier API integration.
- Measure SQL Server performance before changing indexes or queries.
- Use execution plans to understand scans, seeks, joins and expensive operators.
- Design indexes around real WHERE, JOIN and ORDER BY patterns.
- Return only the data required by the application.
- Use pagination and targeted projections for large shipping datasets.
- Optimize SQL independently from Dapper or ASP.NET Core application code.
1. Measure SQL Server Performance Before You Optimize
Performance tuning should begin with evidence. Before adding an index or rewriting a query, identify which SQL operation is consuming time and whether the bottleneck is CPU, I/O, blocking, connection wait, poor cardinality estimates or excessive data transfer.
For an ASP.NET Core application, measure both the database operation and the complete HTTP request. A slow shipment endpoint, for example, may include SQL Server time plus a DHL, UPS, DPD or Asendia API call.
var stopwatch = Stopwatch.StartNew();
var shipment =
await connection.QuerySingleOrDefaultAsync<Shipment>(
sql,
parameters);
stopwatch.Stop();
logger.LogInformation(
"Shipment query completed in {ElapsedMs} ms",
stopwatch.ElapsedMilliseconds);
Measure database execution separately from external carrier API latency so you optimize the correct layer.
2. Read SQL Server Execution Plans
An execution plan shows how SQL Server intends to retrieve and process the requested data. It can reveal table scans, index seeks, expensive joins, sorts, lookups and other operations that explain why a query is slow.
When investigating a slow ShippingPortal query, compare the SQL statement with the execution plan rather than assuming that adding an index is always the answer.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT
ID,
TrackingNo,
CourierID,
SenderName,
ReceiverName
FROM tblOrders
WHERE UserID = @UserID;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
The resulting logical reads and CPU information can help establish a baseline before a query or index is changed.
3. Understand Index Seeks and Index Scans
An index seek can allow SQL Server to navigate directly to relevant index entries, while an index scan may require a much larger portion of an index or table to be examined. A scan is not automatically wrong, but an unexpected scan on a large table is worth investigating.
Consider a shipment lookup:
SELECT
ID,
TrackingNo,
CourierID
FROM tblOrders
WHERE TrackingNo = @TrackingNo;
If tracking-number lookups are common and the column is suitably indexed, SQL Server may be able to locate the requested rows without scanning the entire shipment table.
4. Create Indexes Around Real Query Patterns
An index should exist because the application has a meaningful query pattern, not because the column happens to look important.
In a shipping system, common access patterns may include shipment lookup by identifier, tracking-number search, user-specific shipment lists or carrier-service selection.
CREATE INDEX IX_tblOrders_TrackingNo
ON tblOrders (TrackingNo);
Before adding such an index, verify the existing schema, workload frequency, selectivity and write cost.
5. Use Composite Indexes for Multi-Column Filters
Many enterprise queries filter on more than one column. A composite index can be useful when its key order matches the way the application actually searches the data.
SELECT
ID,
TrackingNo,
CourierID,
Label_Generation_Date
FROM tblOrders
WHERE UserID = @UserID
AND CourierID = @CourierID
ORDER BY ID DESC;
A candidate composite index might use the columns that consistently participate in the filtering pattern. Test the real query and execution plan rather than assuming that every combination should be indexed.
6. Use Included Columns When a Covering Index Makes Sense
A query may filter using one set of columns while returning another set. Included columns can sometimes allow SQL Server to satisfy the query directly from the index.
CREATE INDEX IX_tblOrders_UserID
ON tblOrders (UserID)
INCLUDE
(
TrackingNo,
CourierID,
SenderName,
ReceiverName
);
This can reduce additional lookups for suitable read workloads. The trade-off is that larger indexes consume storage and increase the cost of writes and maintenance.
7. Avoid SELECT * in Enterprise Queries
Returning every column from a shipping record increases data transfer, mapping work and memory usage. Select only the fields the current operation actually needs.
SELECT
ID,
OrderID,
TrackingNo,
CourierID,
SenderName,
ReceiverName
FROM tblOrders
WHERE ID = @OrderId;
This is useful for Dapper-based endpoints because a smaller projection also means less result data for the .NET application to map.
8. Write SARGable WHERE Conditions
Query predicates should be written so SQL Server can make effective use of available indexes. Applying expressions directly to indexed columns can make index usage harder in some cases.
-- Prefer a direct predicate where possible
WHERE TrackingNo = @TrackingNo
Be especially careful with functions and conversions on columns used for filtering. Always verify the actual execution plan because SQL Server's optimizer can use different strategies depending on the query and schema.
9. Optimize JOIN Conditions
Shipping reports commonly join orders, services, customers or other operational data. Join columns should be supported by appropriate keys or indexes where the workload requires it.
SELECT
o.ID,
o.TrackingNo,
s.ServiceProviderName,
s.Product
FROM tblOrders AS o
INNER JOIN developer.tblDeliveryService AS s
ON s.ID = o.CourierID
WHERE o.UserID = @UserID;
Examine the execution plan when joins become expensive. The goal is to reduce unnecessary rows and avoid large intermediate result sets.
10. Pay Attention to ORDER BY and Sorting
Operational screens often display the newest shipments first. Sorting a very large result set can become expensive if the query cannot obtain rows efficiently in the desired order.
SELECT
ID,
TrackingNo,
CourierID,
Label_Generation_Date
FROM tblOrders
WHERE UserID = @UserID
ORDER BY ID DESC;
Consider whether the filtering and ordering pattern is frequent enough to justify an appropriate index strategy.
11. Use Server-Side Pagination for Large Shipment Tables
Shipment history can grow significantly over time. Returning thousands of rows to a dashboard is rarely necessary.
SELECT
ID,
TrackingNo,
CourierID,
SenderName,
ReceiverName
FROM tblOrders
WHERE UserID = @UserID
ORDER BY ID DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
Pagination limits both SQL Server result processing and the amount of data that Dapper needs to map.
12. Use Parameterized Queries with Dapper
Dapper allows developers to keep SQL explicit while passing parameter values separately.
var shipments =
await connection.QueryAsync<ShipmentSummary>(
"""
SELECT
ID,
TrackingNo,
CourierID
FROM tblOrders
WHERE UserID = @UserID
AND TrackingNo = @TrackingNo;
""",
new
{
UserID = userId,
TrackingNo = trackingNo
});
Parameterization should be the normal approach for user-provided tracking numbers, account identifiers, addresses and other request values.
13. Understand Statistics and Cardinality Estimates
SQL Server's optimizer relies on statistics to estimate how many rows a predicate will return. Poor estimates can lead to inefficient join or access strategies.
When a query suddenly becomes slower after data growth or changing distributions, investigate statistics and the execution plan before rewriting application code.
The correct maintenance strategy depends on the database workload and SQL Server configuration, so statistics should be managed deliberately rather than updated indiscriminately.
14. Investigate Parameter-Sensitive Query Behavior
A query can behave differently for different parameter values when the data distribution is highly uneven. This can produce plans that are efficient for one shipment search and inefficient for another.
When a parameterized Dapper query behaves inconsistently, compare execution plans for representative values and investigate the SQL Server behavior before changing the application query.
15. Investigate Blocking and Long-Running Transactions
A query can be slow because it is waiting, not because its own execution plan is inefficient.
In enterprise shipping systems, long-running writes, reporting operations or poorly scoped transactions can block other operations that need the same data.
SELECT
request_session_id,
resource_type,
resource_database_id
FROM sys.dm_tran_locks;
Treat blocking as a separate diagnostic category from ordinary query execution time.
16. Dapper Performance Depends on SQL Server Too
Dapper is lightweight, but it cannot compensate for an inefficient SQL Server query. In a shipment search, most response time may come from database I/O, joins or sorting rather than object mapping.
A useful performance path is:
ASP.NET Core
↓
Dapper
↓
SQL Query
↓
SQL Server
↓
Indexes / Execution Plan
This is why Dapper optimization and SQL Server indexing should be considered together rather than as separate topics.
17. SQL Server Query Optimization in ShippingPortal
ShippingPortal contains shipment-oriented data such as order records, tracking information, carrier identifiers, sender and receiver details and operational statuses. These are ideal examples of workloads where query design and indexing need to follow real application behavior.
SELECT
ID,
OrderID,
TrackingNo,
CourierID,
SenderName,
ReceiverName,
TotalCost
FROM tblOrders
WHERE UserID = @UserID
ORDER BY ID DESC
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;
Instead of asking SQL Server for the complete order row, the application can return only the fields required by the shipment grid and paginate the result.
18. Index Sender and Receiver Address Workloads Based on Usage
Shipping systems repeatedly access address-book data. Sender and receiver searches may be filtered by user, identifier or other business fields.
SELECT
ID,
ReceiverName,
ReceiverCountry,
ReceiverCity,
ReceiverZip
FROM tblAdressBookReceiver
WHERE UserID = @UserID
AND ID = @ReceiverId;
If this pattern is common, review its execution plan and evaluate the existing key and index structure before introducing a new index.
19. Optimize Carrier Service Queries
A multi-carrier shipping platform may repeatedly retrieve carrier service configuration while preparing shipments or presenting available services.
SELECT
ID,
ServiceProviderName,
Product,
SubCategory
FROM developer.tblDeliveryService
WHERE ServiceProviderName = @ProviderName;
This is another case where SQL Server indexing should follow the real filter patterns used by the application instead of indexing every available column.
20. Design Tracking Queries for Operational Scale
Tracking screens and synchronization processes can read the same shipment records repeatedly. Keep tracking queries narrow and return only the state needed by the current operation.
SELECT
ID,
TrackingNo,
CourierID,
VerificationStatus,
IsProcessed
FROM tblOrders
WHERE TrackingNo IS NOT NULL
AND TrackingNo <> '';
Tracking workloads should also be separated from external carrier latency. A slow DHL, UPS, DPD or Asendia response should not automatically turn into a long-lived SQL transaction.
21. Use Bulk Operations for Large Address or Shipment Imports
ShippingPortal uses SqlBulkCopy for bulk data
operations. This is a different performance strategy from
executing an individual insert for every row.
using var bulkCopy =
new SqlBulkCopy(connection);
bulkCopy.DestinationTableName =
"developer.tblAddressBookReceiver_Bulk";
bulkCopy.WriteToServer(dataTable);
For bulk workloads, evaluate index overhead, batch size, transaction scope and validation strategy alongside throughput.
22. Remember That Indexes Also Cost Writes
Indexes improve many reads, but every additional index can increase the work required for INSERT, UPDATE and DELETE operations.
This matters in shipping software because shipment creation, tracking updates, address imports and operational status changes can all be write-heavy.
The best indexing strategy balances the application's important read patterns against its write workload and maintenance requirements.
23. Do Not Over-Index Every Column
Adding indexes for every searchable field is tempting, but this can create unnecessary storage use, slower writes and more complicated maintenance.
Prefer a small set of indexes that support the application's highest-value queries and verify their impact with measurements.
24. Connect SQL Performance to ASP.NET Core Architecture
Database optimization works best when the application architecture makes it possible to identify and isolate expensive operations.
ASP.NET Core API
↓
Application Service
↓
Repository / Data Access
↓
Dapper / EF / ADO.NET
↓
SQL Server
This separation makes it easier to measure SQL duration, change query implementations and keep infrastructure details from spreading through the rest of the application.
25. Keep SQL Optimization Separate from Carrier API Latency
A shipment workflow may first read data from SQL Server and then call a carrier API. These are separate performance boundaries.
Load Shipment
↓
SQL Server
↓
Prepare Carrier Request
↓
DHL / UPS / DPD / Asendia API
↓
Process Response
↓
Persist Result
Keep SQL connections and database transactions focused on database work rather than holding them open while waiting for DHL API Integration, UPS API Integration, DPD API Integration or Asendia API Integration.
26. Benchmark SQL Server Queries with Realistic Data
A query that performs well against a small development dataset can behave very differently against millions of shipment rows, addresses or activity records.
- Test realistic row counts.
- Test representative parameter values.
- Measure logical reads and execution time.
- Test concurrent application requests.
- Compare before-and-after execution plans.
- Measure the complete API workflow as well as SQL time.
SQL Server Indexing & Query Performance Checklist
- Have you measured the slow query before changing it?
- Have you reviewed the actual execution plan?
- Are WHERE and JOIN columns supported by appropriate keys or indexes?
- Are composite indexes aligned with real filter patterns?
- Are unnecessary columns excluded from SELECT statements?
- Are large shipment and activity lists paginated?
- Are statistics and cardinality estimates reasonable?
- Have blocking and long-running transactions been ruled out?
- Are index write and maintenance costs acceptable?
- Have you measured the complete ASP.NET Core request, including external API latency?
Conclusion
SQL Server indexing and query performance optimization are not separate from application architecture. They are a critical part of how an enterprise .NET application behaves under real workloads.
The most effective approach is to measure first, inspect execution plans, design indexes around actual access patterns, limit returned data and keep database operations separate from unrelated external work.
In a platform such as ShippingPortal, the same principles apply to shipment search, sender and receiver address lookups, carrier service queries, tracking workflows, reporting and bulk processing. Combined with Dapper, Entity Framework or ADO.NET at the appropriate boundaries, these techniques help keep ASP.NET Core enterprise applications responsive as data and usage grow.