Child Windows Freeze on large SQL query

I have a C++ MDI  application using MFC with SQLServer. The sql query is complex and takes approximately 20 sec to complete. This freezes the application and the user can't do any thing in the other child windows. I would like to know what should be done to allow the user to continue on the other Child windows while the sql query retrieves its information.

A snip of my code:

CDatabase m_db;


Interface::Interface()
{
      m_db.OpenEx("DSN=MYsql;Description=test;APP=Microsoft\x00ae Visual Studio .NET;WSID=TEST;DATABASE=myDB;Trusted_Connection=Yes");
}

CMyRecView.h
-----------------
class CMyRecView : public CRecordView
{
protected:
      CMyRecView();
..
..
}

CMyRecView.cpp
-----------------
void CMyRecView::OnBnClickedRequest()
{


      CDynamicBulkSet rs(&m_db);
      rs.Open(CRecordset::snapshot, "SELECT * FROM tblLeaders", CRecordset::readOnly | CRecordset::useMultiRowFetch);

      FillGrid(&rs);
      rs.Close();
}


Many Thanks,
Dan
DBOTMAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roshan DavisCommented:
I think the problem is not in the query, the function FillGrid will take much amount of time for a bulk data, so the messages will block add this snippet in that function (in the loop of that function)

void CYourClass::FillGrid(CRecordset& rs)
{
      MSG msg;

      (******* LOOP *******)
      {

            while ( ::PeekMessage(&msg, NULL, NULL, NULL, PM_REMOVE) )
            {
                  ::TranslateMessage(&msg);
                  ::DispatchMessage(&msg);
            }

            m_wndYourList.InsertItem(.........);

      }



Good LUck
0
itsmeandnobodyelseCommented:
The problem is, that you call the lengthy job when the BUTTON_CLICKED message is processed.
So, no other messages could be processed as long as are in CMyRecView::OnBnClickedRequest()

Rosh showed you how to process messages within your handler function, thus avoiding the problem.

Another way is to handle only a small portion of your result set within one message handling.
You would setup a timer, that is called say all 10 ms and that will fill say 10 records to your grid.
This would look like this:
 

CMyRecView.cpp
-----------------
void CMyRecView::OnBnClickedRequest()
{
     // You need the CDynamicBulkSet as member
     // CDynamicBulkSet rs(&m_db);

     if (m_pRs != NULL)
     {
         // do not handle button clicks while an old result set exists
          return;
     }
     m_pRs = new CDynamicBulkSet (&m_db);
     m_pRs->Open(CRecordset::snapshot, "SELECT * FROM tblLeaders", CRecordset::readOnly | CRecordset::useMultiRowFetch);

     // setup the timer
     m_timerID  = SetTimer(123, 10);

     // FillGrid(&rs);
     // rs.Close();
}

void CMyRecView::OnTimer(UINT eventId)
{
      for (int i = 0; i < 10; i++)
      {
            m_pRs->MoveNext();
            if (m_pRs->IsEOF())
            {
                  KillTimer(m_timerID);
                  m_pRs->Close();
                  delete m_pRs;
                  m_pRs = NULL;
                  return;
            }
           
            // do something with record
            FillNextGridLine(m_pRs);
            ...
      }
     
}

I hope, you got the idea.

A further possibility would be to do the whole job in a thread.
I can give you the details if you want them.

Regards, Alex
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBOTMAAuthor Commented:
The problem is not with FillGrid, even with the line commented out it still take 20 seconds.

Please note: The sql "select * from tblLeaders" is only to show the problem.

Any better solutions than using a worker thread?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.