The ODBC connection MySQL, the service startup before loading 30W data, please t

BOOL SqlDB::GetALLMsg()
{
UserInfo * m_gu;
BOOL whetherGetAllMsg=FALSE; //Tag is to obtain all the information;
try
{
// If the database is already connected ;
if(m_conn == NULL)
{
//To connect to the database;
m_conn->Open("DSN=zlx;server=localhost;database=dotagame","root","",adModeUnknown);
}


//The user data
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; //Record set;
m_pRecordset.CreateInstance(__uuidof( Recordset )); //Create an instance of;
m_conn->CursorLocation=adUseClient; //The cursor type
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");


//The number of leadership with knapsack based data corresponding to the level of;
//UserLevel m_level=GameData::GetDataFromUserLevelBaseDate(m_gu->user_Level);
//The corresponding level of leadership;
//m_gu->Leadership=m_level.Leadership;

//Load database information friends;
char friendID[14];
char friendCardID[18];
_variant_t theValue;
for (int i = 0; i <100; i++)
{
UFriend * m_UF=new UFriend;
UCard m_UC; //Friend Captain card information;

_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);


}

//Initialize the elite copy information;
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);

}

//Initialize the equipment inside information
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);

}

//Initialize the card information;
_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;
}

Started by Bruno at November 24, 2016 - 8:16 AM

Where efficiency is low.

Posted by Spark at December 06, 2016 - 8:45 AM

When the traversal of the second set, probably only 5.5 user traversal, 30 spend almost 15 hours

Posted by Bruno at December 18, 2016 - 9:37 AM

The problem separately, first determine the SQL statement itself to MYSQL query efficiency. If the SQL statement execution efficiency is no problem, is recommended in the corresponding procedure section consultation.

Posted by Moses at December 19, 2016 - 9:57 AM

Efficiency of the SQL statement:
(299852 row(s) returned)
Execution Time : 00:00:00:000
Transfer Time : 00:00:19:064
Total Time : 00:00:19:064
As for ODBC, I use mysql-connector-odbc-5.1.12-winx64
I am a rookie, there is hope that this research. One or two, pointing.

Posted by Bruno at December 24, 2016 - 10:05 AM