大家看看怎么提高效率
数据库“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)