ADOMD - get_Item() method too slow - why?

[Jarek Micha³owski]

Hi, i am newbie here and need help. Subject: Multidimmension al Cube
In the following code I send the sql query to mssql and retrive data to
objCellSet object. My problem is:
In retrive data I have 10 000 records, get_Item() method is too slow, does
anybody knows why? Is any better solution for this problem?

Thanks
Jarek

if (objConn == null)
objConn = new ConnectionClass ();

objConn.Open( strProvider, "", "", 0 );

if (objCellSet == null)
objCellSet = new CellsetClass();

objCellSet.Open ( mdxQuery, objConn );

string strColName = "";

if (mdxDataSet != null)
mdxDataSet = null;

mdxDataSet = new DataSet();

if (mdxTable != null)
mdxTable = null;

mdxTable = new DataTable("mdxT able");

DataRow mdxRow;

Cell cell;
object[] coords = new Object[2];

mdxTable.Column s.Add("Descript ion");

for (int i=0; i<objCellSet.Ax es[0].Positions.Coun t; i++)
{
strColName = objCellSet.Axes[0].Positions[i].Members[0].Caption;
mdxTable.Column s.Add(strColNam e);
}

Console.WriteLi ne(objCellSet.A xes[1].Positions.Coun t);

for (int i=0 ;i<objCellSet.A xes[1].Positions.Coun t; i++)
{
string val = "";

mdxRow = mdxTable.NewRow ();

mdxRow[0] = objCellSet.Axes[1].Positions[i].Members[0].Caption;

for (int j=0; j<objCellSet.Ax es[0].Positions.Coun t; j++)
{
coords[0] = j;
coords[1] = i;

cell = objCellSet.get_ Item(ref coords); //<------------ too
slow

string strTemp = cell.FormattedV alue;
if ( strTemp == "" || strTemp == null )
val = "0";
else
val = cell.FormattedV alue.ToString() ;

mdxRow[j + 1] = val;
}
mdxTable.Rows.A dd(mdxRow);
}
mdxDataSet.Tabl es.Add(mdxTable );

[Nicholas Paldino [.NET/C# MVP]]

Jarek,

Is there a reason why you wont use the ADOMX result set that is returned
from you? You seem to be transferring the results into a DataTable, and you
are basically doing the fill operation on the data container twice. For a
result set for 10000 rows (and who knows how many columns), that can be
expensive.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Jarek Micha³owski" <j.***********@ gazeta.pl> wrote in message
news:bo******** **@inews.gazeta .pl...

Hi, i am newbie here and need help. Subject: Multidimmension al Cube
In the following code I send the sql query to mssql and retrive data to
objCellSet object. My problem is:
In retrive data I have 10 000 records, get_Item() method is too slow, does
anybody knows why? Is any better solution for this problem?

Thanks
Jarek

if (objConn == null)
objConn = new ConnectionClass ();

objConn.Open( strProvider, "", "", 0 );

if (objCellSet == null)
objCellSet = new CellsetClass();

objCellSet.Open ( mdxQuery, objConn );

string strColName = "";

if (mdxDataSet != null)
mdxDataSet = null;

mdxDataSet = new DataSet();

if (mdxTable != null)
mdxTable = null;

mdxTable = new DataTable("mdxT able");

DataRow mdxRow;

Cell cell;
object[] coords = new Object[2];

mdxTable.Column s.Add("Descript ion");

for (int i=0; i<objCellSet.Ax es[0].Positions.Coun t; i++)
{
strColName = objCellSet.Axes[0].Positions[i].Members[0].Caption;
mdxTable.Column s.Add(strColNam e);
}

Console.WriteLi ne(objCellSet.A xes[1].Positions.Coun t);

for (int i=0 ;i<objCellSet.A xes[1].Positions.Coun t; i++)
{
string val = "";

mdxRow = mdxTable.NewRow ();

mdxRow[0] = objCellSet.Axes[1].Positions[i].Members[0].Caption;

for (int j=0; j<objCellSet.Ax es[0].Positions.Coun t; j++)
{
coords[0] = j;
coords[1] = i;

cell = objCellSet.get_ Item(ref coords); //<------------ too slow

string strTemp = cell.FormattedV alue;
if ( strTemp == "" || strTemp == null )
val = "0";
else
val = cell.FormattedV alue.ToString() ;

mdxRow[j + 1] = val;
}
mdxTable.Rows.A dd(mdxRow);
}
mdxDataSet.Tabl es.Add(mdxTable );

[Jarek Micha³owski]

Hi, I use the datatable to pleace it in dataset which is a datagrid
datasource. I need to display this result in datagrid. In ADOMX result set
is only 4 columns. Is any other method to display ADOMX result set in
datagrid? Or how to display that a lot of data (10 000 records)?

Thanks
Jarek
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard .caspershouse.c om> wrote in
message news:OZ******** ******@TK2MSFTN GP12.phx.gbl...

Jarek,

Is there a reason why you wont use the ADOMX result set that is returned from you? You seem to be transferring the results into a DataTable, and you are basically doing the fill operation on the data container twice. For a
result set for 10000 rows (and who knows how many columns), that can be
expensive.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m

"Jarek Micha³owski" <j.***********@ gazeta.pl> wrote in message
news:bo******** **@inews.gazeta .pl...

Hi, i am newbie here and need help. Subject: Multidimmension al Cube
In the following code I send the sql query to mssql and retrive data to
objCellSet object. My problem is:
In retrive data I have 10 000 records, get_Item() method is too slow, does anybody knows why? Is any better solution for this problem?

Thanks
Jarek

if (objConn == null)
objConn = new ConnectionClass ();

objConn.Open( strProvider, "", "", 0 );

if (objCellSet == null)
objCellSet = new CellsetClass();

objCellSet.Open ( mdxQuery, objConn );

string strColName = "";

if (mdxDataSet != null)
mdxDataSet = null;

mdxDataSet = new DataSet();

if (mdxTable != null)
mdxTable = null;

mdxTable = new DataTable("mdxT able");

DataRow mdxRow;

Cell cell;
object[] coords = new Object[2];

mdxTable.Column s.Add("Descript ion");

for (int i=0; i<objCellSet.Ax es[0].Positions.Coun t; i++)
{
strColName = objCellSet.Axes[0].Positions[i].Members[0].Caption;
mdxTable.Column s.Add(strColNam e);
}

Console.WriteLi ne(objCellSet.A xes[1].Positions.Coun t);

for (int i=0 ;i<objCellSet.A xes[1].Positions.Coun t; i++)
{
string val = "";

mdxRow = mdxTable.NewRow ();

mdxRow[0] = objCellSet.Axes[1].Positions[i].Members[0].Caption;

for (int j=0; j<objCellSet.Ax es[0].Positions.Coun t; j++)
{
coords[0] = j;
coords[1] = i;

cell = objCellSet.get_ Item(ref coords); //<------------

too

slow

string strTemp = cell.FormattedV alue;
if ( strTemp == "" || strTemp == null )
val = "0";
else
val = cell.FormattedV alue.ToString() ;

mdxRow[j + 1] = val;
}
mdxTable.Rows.A dd(mdxRow);
}
mdxDataSet.Tabl es.Add(mdxTable );