大家看看怎么提高效率
数据库“IM”有两个表，表“UserInfo”存储用户信息（UserID，UserName）；表“MessageInfo”存储用户间的对话（MsgID，SendUserID，ReceiveUserID，Msg）。现要联表查询用户ID为1和2之间的对话信息（MsgID、SendUserName，ReceiveUserName，Msg），我用的SQL语句：
select MsgID,
'SendUserName '=(select UserName from UserInfo where UserID=SendUserID),
'ReceiveUserName '=(select UserName from UserInfo where UserID=ReceiveUserID),
Msg
from MessageInfo
where ReceiveUserID in(1,2) and SendUserID in(1,2)
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
请问：怎么提高执行效率？
附生成该两个表的SQL：
USE [IM]
GO
/****** 对象: Table [dbo].[UserInfo] 脚本日期: 02/27/2007 09:49:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserInfo](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
USE [IM]
GO
/****** 对象: Table [dbo].[MessageInfo] 脚本日期: 02/27/2007 09:50:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MessageInfo](
[MsgID] [int] IDENTITY(1,1) NOT NULL,
[SendUserID] [int] NOT NULL,
[ReceiveUserID] [int] NOT NULL,
[Msg] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
[解决办法]
用關聯
select A.MsgID,
'SendUserName '= B.UserName,
'ReceiveUserName '=C.UserName,
A.Msg
from MessageInfo A
Inner Join UserInfo B On A.SendUserID = B.UserID
Inner Join UserInfo C On A.ReceiveUserID = C.UserID
where A.ReceiveUserID in(1,2) and A.SendUserID in(1,2)
[解决办法]
UserID建唯一索引
MsgID建唯一索引
[解决办法]
可以考虑使用INNER JOIN or LEFT OUTER JOIN，这样会更好。
如：
SELECT MessageInfo.MsgID,UserInfo_A.UserName,UserInfo_B.UserName
FROM MessageInfo LEFT OUTER JOIN UserInfo AS UserInfo_A
ON UserInfo_A.UserID=MessageInfo.SendUserID LEFT OUTER JOIN UserInfo AS UserInfo_B
ON UserInfo_B.UserID=MessageInfo.ReceiveUserID
WHERE MessageInfo.ReceiveUserID IN in(1,2) AND MessageInfo.SendUserID IN in(1,2)
