![]() ![]() It does this by using precalculated statistics on your table sizes and data contents in order to be able to pick a "good enough" plan quickly. SQL is a declarative language: you write code that specifies *what* data to get, not *how* to get it.īasically, the SQL Server query optimizer takes your SQL query and decides on its own how it thinks it should get the data. So if the order that our tables are joined in makes a big difference for performance reasons, SQL Server follows the join order we define right? SQL Server doesn't let you choose the join order If we tried doing the Orders to OrderLines join first, we actually wouldn't filter out any rows in our first step, cause our subsequent join to StockItems to be more slower (because more rows would have to be processed).īasically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve. Since the StockItems table has no duplicate rows (it's a simple lookup table for product information) it is a great table to join with as early as possible since it will reduce the total number of rows getting passed around for the remainder of the query. It's made even smaller by filtering on 'USA' which reduces it to only 8 rows. ![]() Selective? Well you might notice that our StockItems table is small with only 227 rows. In terms of performance, it's almost certain that the latter scenario (joining OrderLines with StockItems first) will be faster because StockItems will help us be more selective. We basically have two options for table join orders then - we can join Orders with OrderLines first and then join in StockItems, or we can join OrderLines and StockItems first and then join in Orders. The key thing to notice is that we are joining three tables - Orders, OrderLines, and StockItems - and that OrderLines is what we use to join between the other two tables. Note: with an INNER join, I normally would prefer putting my 'USA' filter in the WHERE clause, but for the rest of these examples it'll be easier to have it part of the ON. CountryOfManufacture = 'USA' - 8 rows for USA StockItemID - 1036 rows after join AND s. OrderID - 231412 rows after join INNER JOIN Warehouse. * - Run if if you want to follow along - add a computed column and index for CountryOfManufacture ALTER TABLE Warehouse.StockItems SET (SYSTEM_VERSIONING = OFF) ALTER TABLE Warehouse.StockItems ADD CountryOfManufacture AS CAST(JSON_VALUE(CustomFields,'$.CountryOfManufacture') AS NVARCHAR(10)) ALTER TABLE Warehouse.StockItems SET (SYSTEM_VERSIONING = ON) CREATE INDEX IX_CountryOfManufacture ON Warehouse.StockItems (CountryOfManufacture) */ SELECT o.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |