ODBC连接MySQL,服务启动前加载30W数据，请大神指教，看是否能在优化一下性能，代码如下：

Question

BOOL SqlDB::GetALLMsg()
{
UserInfo * m_gu;
BOOL whetherGetAllMsg=FALSE; //是否获得所有信息的标记;
try 
{  
// 是否已经连接数据库 ; 
if(m_conn == NULL)  
{  
//重新连接数据库;   
m_conn->Open("DSN=zlx;server=localhost;database=dotagame","root","",adModeUnknown);  
}  


//取登陆用户数据
TCHAR sql[400];
_stprintf_s(sql,400,TEXT("SELECT * FROM dota_user, dota_friend,dota_equip,dota_elite WHERE dota_user.user_ID >= 100000 and dota_user.user_ID < 400000 AND dota_user.user_ID=dota_friend.user_ID and dota_friend.user_ID=dota_equip.user_ID and dota_equip.user_ID=dota_elite.user_ID;  "));


_RecordsetPtr m_pRecordset; //记录集;
m_pRecordset.CreateInstance(__uuidof( Recordset )); //创建实例;
m_conn->CursorLocation=adUseClient; //游标类型
m_pRecordset->Open(sql,m_conn.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
TotalCountUser=m_pRecordset->GetRecordCount();
while(!m_pRecordset->adoEOF)
{
m_gu =new UserInfo;
CString c_UserName,c_CodePhone;
m_gu->user_ID=(int)m_pRecordset->GetCollect((_bstr_t)"user_ID");
c_UserName=VariantToString(m_pRecordset->GetCollect((_bstr_t)"user_Name"));

memcpy(m_gu->user_Name,c_UserName,32);
TRACE("\n%d",m_gu->user_ID);
c_CodePhone=VariantToString(m_pRecordset->GetCollect((_bstr_t)"user_CodePhone"));
memcpy(m_gu->user_CodePhone,c_CodePhone,33);
m_gu->user_Level=(short)m_pRecordset->GetCollect((_bstr_t)"user_Level");
m_gu->user_Energy=(short)m_pRecordset->GetCollect((_bstr_t)"user_Energy");
m_gu->user_EXP=(short)m_pRecordset->GetCollect((_bstr_t)"user_EXP"); //m_gu->user_Maximum_capacity=(short)m_pRecordset->GetCollect((_bstr_t)"user_Maximum_capacity");
m_gu->user_Elite_Level=(short)m_pRecordset->GetCollect((_bstr_t)"user_Elite_Level");
m_gu->user_Common_Level=(short)m_pRecordset->GetCollect((_bstr_t)"user_Common_Level");
m_gu->user_Gold=(int)m_pRecordset->GetCollect((_bstr_t)"user_Gold");
//  memcpy(&m_gu->user_Common_Level+1,&m_gu->user_Gold,4);
// 
m_gu->user_Diamond=(int)m_pRecordset->GetCollect((_bstr_t)"user_Diamond");
m_gu->user_PVP=(short)m_pRecordset->GetCollect((_bstr_t)"user_PVP"); m_gu->CheckNum=(short)m_pRecordset->GetCollect((_bstr_t)"CheckNum"); m_gu->user_LoginTime=(int)m_pRecordset->GetCollect((_bstr_t)"user_LoginTime");
m_gu->user_FriendPoint=(int)m_pRecordset->GetCollect((_bstr_t)"user_FriendPoint"); m_gu->user_Bag_Slot=(short)m_pRecordset->GetCollect((_bstr_t)"user_BagNum");
m_gu->oldCheckNum=(short)m_pRecordset->GetCollect((_bstr_t)"OldCheckNum"); m_gu->user_team[0]=(int)m_pRecordset->GetCollect((_bstr_t)"Card1");

m_gu->user_team[1]=(int)m_pRecordset->GetCollect((_bstr_t)"Card2");

m_gu->user_team[2]=(int)m_pRecordset->GetCollect((_bstr_t)"Card3");

m_gu->user_team[3]=(int)m_pRecordset->GetCollect((_bstr_t)"Card4");


//等级所对应的领导力跟背包数等基础数据;
//UserLevel m_level=GameData::GetDataFromUserLevelBaseDate(m_gu->user_Level);
//相应等级所对应的领导力;
//m_gu->Leadership=m_level.Leadership; //加载数据库好友信息;
char friendID[14];
char friendCardID[18];
_variant_t theValue;
for (int i = 0; i < 100; i++)
{
UFriend * m_UF=new UFriend;
UCard m_UC; //好友队长卡牌的信息;

_snprintf_s(friendID,14,"friend_ID%d",i+1);
m_UF->f_id=(int)m_pRecordset->GetCollect((_bstr_t)friendID);
_snprintf_s(friendCardID,18,"friend_CardID%d",i+1);
m_UC.cardId=(int)m_pRecordset->GetCollect((_bstr_t)friendCardID);
if (m_UF->f_id!=0)
{
char mysql[200];
_snprintf_s(mysql,200,"call SELECT_CARDMSG(%d,%d,%d,%d);",m_UF->f_id,m_UC.cardId,0,1000); 
_RecordsetPtr m_pRecordsetTWO=m_conn->Execute((LPCSTR)mysql,&theValue,adCmdText);
m_UC.cardId=(int)m_pRecordsetTWO->GetCollect((_bstr_t)"card_ID");
m_UC.cardType=(int)m_pRecordsetTWO->GetCollect((_bstr_t)"card_TypeID");
m_UC.cardSkillLevel[0]=m_pRecordsetTWO->GetCollect((_bstr_t)"cardSkillLevelOne");
m_UC.cardSkillLevel[1]=m_pRecordsetTWO->GetCollect((_bstr_t)"cardSkillLevelTwo");
m_UC.cardSkillLevel[2]=m_pRecordsetTWO->GetCollect((_bstr_t)"cardSkillLevelThree");
m_UC.cardExp=(int)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EXP");
m_UC.cardLevel=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_Level");
m_UC.cardEquip[0]=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EquipID1");
m_UC.cardEquip[1]=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EquipID2");
m_UC.cardEquip[2]=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EquipID3");
m_UC.cardEquip[3]=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EquipID4");
m_UC.cardEquip[4]=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EquipID5");
m_UC.cardEquip[5]=(short)m_pRecordsetTWO->GetCollect((_bstr_t)"card_EquipID6");
}else
{
m_UC.cardId=0;
m_UC.cardType=0;
m_UC.cardSkillLevel[0]=0;
m_UC.cardSkillLevel[1]=0;
m_UC.cardSkillLevel[2]=0;
m_UC.cardExp=0;
m_UC.cardLevel=0;
m_UC.cardEquip[0]=0;
m_UC.cardEquip[1]=0;
m_UC.cardEquip[2]=0;
m_UC.cardEquip[3]=0;
m_UC.cardEquip[4]=0;
m_UC.cardEquip[5]=0;
}

m_UF->f_leaderCard=m_UC;
m_gu->user_Friend.SetAt(m_UF->f_id,m_UF);
} //初始化精英副本信息;
char eliteID[13];
byte eliteX;
for (int i=0;i<m_gu->user_Elite_Level;i++)
{
_snprintf_s(eliteID,13,"Elite_%d",i+1);

eliteX=m_pRecordset->GetCollect((_bstr_t)eliteID).bVal;
m_gu->user_JYcount.SetAt(i+1,eliteX);
//m_gu->user_JYcount.Add(eliteX); } //初始化装备表里的信息
char equipID[13];
short m_IDEquips;
int m_NumEquips;
for (int i=0;i<130;i++)
{
_snprintf_s(equipID,13,"Equip_%d",i+1);

m_NumEquips=(short)m_pRecordset->GetCollect((_bstr_t)equipID);
m_IDEquips=i+1;
m_gu->user_equip.SetAt(m_IDEquips,m_NumEquips); } //初始化卡牌信息;
_variant_t theValue2;
char cardSql[200];
_snprintf_s(cardSql,200,"select * from dota_card WHERE user_ID=%d AND dota_card.card_ID >%d and      dota_card.card_ID <%d;",m_gu->user_ID,0,1000); 
_RecordsetPtr m_pRecordset3=m_conn->Execute((LPCSTR)cardSql,&theValue2,adCmdText);
UCard * m_CardMsg;
while (!m_pRecordset3->adoEOF)
{
m_CardMsg=new UCard;
m_CardMsg->cardId=(int)m_pRecordset3->GetCollect((_bstr_t)"card_ID");
m_CardMsg->cardType=(int)m_pRecordset3->GetCollect((_bstr_t)"card_TypeID");
m_CardMsg->cardSkillLevel[0]=m_pRecordset3->GetCollect((_bstr_t)"cardSkillLevelOne");
m_CardMsg->cardSkillLevel[1]=m_pRecordset3->GetCollect((_bstr_t)"cardSkillLevelTwo");
m_CardMsg->cardSkillLevel[2]=m_pRecordset3->GetCollect((_bstr_t)"cardSkillLevelThree");
m_CardMsg->cardExp=(int)m_pRecordset3->GetCollect((_bstr_t)"card_EXP");
m_CardMsg->cardLevel=(short)m_pRecordset3->GetCollect((_bstr_t)"card_Level");
m_CardMsg->cardEquip[0]=(short)m_pRecordset3->GetCollect((_bstr_t)"card_EquipID1");
m_CardMsg->cardEquip[1]=(short)m_pRecordset3->GetCollect((_bstr_t)"card_EquipID2");
m_CardMsg->cardEquip[2]=(short)m_pRecordset3->GetCollect((_bstr_t)"card_EquipID3");
m_CardMsg->cardEquip[3]=(short)m_pRecordset3->GetCollect((_bstr_t)"card_EquipID4");
m_CardMsg->cardEquip[4]=(short)m_pRecordset3->GetCollect((_bstr_t)"card_EquipID5");
m_CardMsg->cardEquip[5]=(short)m_pRecordset3->GetCollect((_bstr_t)"card_EquipID6");
m_gu->user_CardBag.SetAt(m_CardMsg->cardId,m_CardMsg);
m_pRecordset3->MoveNext();
}
PutUserInfoToCache(m_gu);
CountUser++;

m_pRecordset->MoveNext();
}

m_pRecordset->Close();
whetherGetAllMsg=TRUE;

}
catch(_com_error e)  
{  
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
TRACE("Error\n");
TRACE("Code = %08lx\n", e.Error());
TRACE("Code meaning = %s\n", e.ErrorMessage());
TRACE("Source = %s\n", (LPCSTR) bstrSource);
TRACE("Description = %s\n", (LPCSTR) bstrDescription);
}  
return whetherGetAllMsg;
}

Answer 1

建议把问题分开，先判断MYSQL查询的SQL语句本身效率如何。如果SQL语句执行效率没有问题，则建议在相应程序的版块咨询。

Answer 2

sql语句的效率：
(299852 row(s) returned)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:19:064
Total Time     : 00:00:19:064
至于ODBC，我用的是mysql-connector-odbc-5.1.12-winx64
本人菜鸟一枚，希望对此有研究的大神，指点一二。