英文:
SQL Server 2022 : unique index with two columns not working correctly
问题
我有一个包含超过50000000条记录的用户表。我想要检查服务器上已注册用户的存在，并执行以下请求。
SELECT *
FROM
(SELECT TOP (1000) *
FROM UserdatasTemp) AS ut
JOIN Userdatas AS u ON u.Login = ut.Login
AND u.Password = ut.Password
-- ...其他连接条件...
我在登录和密码列上使用了两个唯一索引。与此同时，我有两个查询计划，其中第一个索引的搜索较慢，第二个较快。
第一个计划使用索引 IX_Userdatas_Login_Password：
第二个计划使用索引 IX_Userdatas_Password_Login：
我在第一个索引中使用了登录和密码列，而在第二个索引中使用了密码和登录列。在这些索引中，这些列没有包含在“included”标签中。
有谁能提示为什么会发生这种情况，以及如何更快地执行给定的请求？
我尝试过重建索引、调整页面大小、重置统计信息并再次重建索引，但问题仍然存在。
英文:
I have a table with users with over 50000000 entries. I want to check for the presence of already registered users on the server and I execute this request.
SELECT *
FROM
(SELECT TOP (1000) *
FROM UserdatasTemp) AS ut
JOIN Userdatas AS u ON u.Login = ut.Login
AND u.Password = ut.Password
-- ...OTHER JOINS...
I use 2 unique indexes on the login and on the password column. At the same time, I have 2 query plans in which the search for the first index is slow and the second one is fast.
First plan with index IX_Userdatas_Login_Password:
Second plan with index IX_Userdatas_Password_Login:
I use unique indexes with login and password columns in the first index and password and login in the second. In indexes, these columns are not in the included tab.
Who can prompt why so happens and how to execute the given request more quickly?
I've tried rebuilding indexes, resizing pages, resetting stats, and rebuilding indexes again, but the problem is still there
答案1
得分: 0
感谢我的朋友们解决了这个问题。问题本身是varchar(450)和varchar(MAX)列中的数据类型不正确。奇怪的是，我没有在密码中看到这个问题，而是试图找到与索引有关的问题，忘记了不同的类型。
英文:
Many thanks to my friends for solving this problem. The problem itself was incorrect data types in the varchar(450) and varchar(MAX) columns. It is strange that I did not see this problem in the password and tried to find a problem with the index forgetting about different types
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论