problem with Open of a query to a recordset

SWDevil

Hi, I have an application in which I am loading data from a database (in this specific case a large database) into a grid in my GUI. So, I open the query into a recordset using the Open method as follows: sQuery.Format(_T("SELECT DISTINCT D.fieldA, DC.fieldB from TableDC DC, TableD D WHERE D.fieldA = DC.fieldA AND D.No = '3’ ORDER BY DC.fieldB")); if(!Open(AFX_DB_USE_DEFAULT_TYPE, sQuery, CRecordset::readOnly)) { ... } The problem is that the statement that contains the call to the Open method sometimes takes about 2 minutes to execute (and sometimes works ok). Anyone have any idea of why this happens? And maybe an idea of how I can fix it (speed it up)?

David Crow

Are the TableD.fieldA and TableD.No fields indexed?

"The largest fire starts but with the smallest spark." - David Crow

"Judge not by the eye but by the heart." - Native American Proverb

Viorel

As I now, execution of SQL queries can be speeded up by defining appropriate indexes in the database definition. I think you have to add indexes for TableD.fieldA, TableDC.fieldA, TableD.No and TableDC.fieldB. This can be done with the application you are using for database management. In additions, use the DISTINCT statement only if is realy required.

SWDevil

I don't think they are. Not in table D at least. What exactly do you mean by indexed?

David Crow

SWDevil wrote:

What exactly do you mean by indexed?

See here.

"The largest fire starts but with the smallest spark." - David Crow

"Judge not by the eye but by the heart." - Native American Proverb

SWDevil

ok - I think I confused indexes and primary keys :) do you know how I can see in SQL server if the fields are indexed or not? And how can I use indexes on these fields to speed the execution of the open method?

SWDevil

Do you know how to add indexes to fields in SQL Server?

David Crow

SWDevil wrote:

Do you know how to add indexes to fields in SQL Server?

Use the CREATE INDEX command.

"The largest fire starts but with the smallest spark." - David Crow

"Judge not by the eye but by the heart." - Native American Proverb

David Crow

SWDevil wrote:

do you know how I can see in SQL server if the fields are indexed or not?

Have you considered the Index Tuning Wizard? There's also SHOWPLAN_TEXT and SHOWPLAN_ALL opptions.

"The largest fire starts but with the smallest spark." - David Crow

"Judge not by the eye but by the heart." - Native American Proverb

Milton Karimbekallil

Use the index tuning wizard in the Query Analyzer. This will give you the right index creation scripts optimized for your select statement. cheers...milton kb.