Why is INT not more efficient than UNIQUEIDENTIFIER (according to the execution plan)?

Question

I have a parent table and a child table, where the columns that join them are of type UNIQUEIDENTIFIER.

The child table has a clustered index in the column that connects it to the parent table (its PC, which is also clustered).

I made a copy of both of these tables, but instead changed the relationship columns as INT, rebuilt the indexes so that they were essentially the same structure and could be queried in the same way.

When I query for the known 20 records from the parent table, pulling all the related records from the child tables, I get the same query costs for both, i.e. 50/50 for parties.

If this is the case, then my gigantic project to modify all tables like this seems pointless except to speed up inserts. Can someone highlight the situation?

---

EDIT:

The question is not which one is more efficient, but why does the query execution plan show that both requests have the same cost?

Answer 1

Finding a key in a clustered index is basically the same with a 4 byte key, 16 byte key, or 160 byte key. The cost of comparing slots with a predicate is just the noise in the total cost of the request (preparation of execution, preparation of the execution context, opening sets of lines, searching for pages, etc.), even if no I / O module is involved.

As long as no one claims that the GUIDs and INTs are on an equal footing, a comparison of just 20 queries will not reveal any differences. One instant you can measure is space: saving 12 bytes per row and per sheet of page with a clustered index plus 12 bytes on each page of the sheet on non-clustered indexes will contain more than a million rows and dozens of tables and indexes. Less space means less IO, improves memory cache performance, improves overall quality, and this can be measured, but you need to measure real loads, not heavy 20 lines.

In the lab, you can measure the difference in download speed between an INT or GUID search, but that should not be your focus. The argument against INT GUID is not controlled by something like a 5% increase in search performance, due to space saving and random randomness leading to fragmentation, and it is very easy to measure the indicators that make a solid case for INT on its own grounds, there is no need to give a search argument of performance.

Answer 2

Much more effective.

Int is much smaller. This means that you get much smaller indexes, which means that you significantly improve memory usage and load time to access the index. It depends on how big your tables are and what you do with them.

Answer 3

In addition to what Remus said, using GUIDs for clustered indexes in most cases will result in tremendous fragmentation, which will affect query performance in terms of I / O. This happens when you do not use sequentially generated tooltips, which, I believe, mainly take place when the application generates guid outside the database. To create a sequential pointer ("more" than previously created in the database), you must use the newsequentialid () function

Comparison of the cost of two plans in one batch is not accurate in all cases. Cost is estimated, among other things, by the number of I / O operations required to complete the request. In small databases, the difference between INT and GUID will not change IO significantly to show the difference in execution plans.