Menu
We have a massive table where I need to make an update on a single row. I don't know the primary key of the row, but I have a varchar value that is unique within that table. I also have the value of a few other columns in that tables.
Running the update is taking upwards of three minutes, and I assume its doing a full table scan.
Looking at the indices on the table, the index on the column has a cardinality of zero and page count of zero. There are other indices with a cardinality equal to the number of rows in the table (couple million) and page count of a couple hundred thousand.
What do these numbers actually mean?
And as a followup, would adding a restriction that hits an index with a higher cardinality or number of pages speed up the execution? Or is there anything else I can look at on the indicies to find one that would be better suited to quickly find the row I ned to change.
924
In SQL, cardinality refers to the uniqueness of data in a specific column of a table. A table would be said to have less cardinality if it has more duplicated data in a column. So, more the cardinality less the data duplication (in a column) of SQL database table. In databases, the term data cardinality is used.
A higher cardinality => you're going to fetch more rows => you're going to do more work => the query will take longer. Thus the cost is (usually) higher. All other things being equal, a query with a higher cost will use more resources and thus take longer to run. But all things rarely are equal.
Cardinality is important because it creates links from one table or entity to another in a structured manner. This has a significant impact on the query execution plan. A query execution plan is a sequence of steps users can take to search for and access data stored in a database system.
Table size: If your query hits one or more tables with millions of rows or more, it could affect performance. Joins: If your query joins two tables in a way that substantially increases the row count of the result set, your query is likely to be slow.
Cardinality is the number of unique values for that field within the table.
I suspect that SQL Server didn't actually create the index because there are no records for it. Is is possible this field is all NULLs, or there is a filter on it that eliminates all records like WHERE 1=0?
A good place to start would be to run the update and capture the actual execution plan.
EDIT:
Index Type 3 is an XML Index.
Please see this page for reference.
The structure of XML indexes is quite a bit different. I don't use them myself but from some quick reading it sounds like it creates a series of additional indexes that can't be accessed directly by users but are used by the query engine.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
