ADOMD - get_Item() method too slow - why?

[Jarek Micha³owski]

Hi, i am newbie here and need help. Subject: Multidimmensional 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("mdxTable");

DataRow mdxRow;

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

mdxTable.Columns.Add("Description");

for (int i=0; i<objCellSet.Axes[0].Positions.Count; i++)
{
strColName = objCellSet.Axes[0].Positions[i].Members[0].Caption;
mdxTable.Columns.Add(strColName);
}

Console.WriteLine(objCellSet.Axes[1].Positions.Count);

for (int i=0 ;i<objCellSet.Axes[1].Positions.Count; i++)
{
string val = "";

mdxRow = mdxTable.NewRow();

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

for (int j=0; j<objCellSet.Axes[0].Positions.Count; j++)
{
coords[0] = j;
coords[1] = i;

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

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

mdxRow[j + 1] = val;
}
mdxTable.Rows.Add(mdxRow);
}
mdxDataSet.Tables.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.com

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

Hi, i am newbie here and need help. Subject: Multidimmensional 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("mdxTable");

DataRow mdxRow;

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

mdxTable.Columns.Add("Description");

for (int i=0; i<objCellSet.Axes[0].Positions.Count; i++)
{
strColName = objCellSet.Axes[0].Positions[i].Members[0].Caption;
mdxTable.Columns.Add(strColName);
}

Console.WriteLine(objCellSet.Axes[1].Positions.Count);

for (int i=0 ;i<objCellSet.Axes[1].Positions.Count; i++)
{
string val = "";

mdxRow = mdxTable.NewRow();

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

for (int j=0; j<objCellSet.Axes[0].Positions.Count; j++)
{
coords[0] = j;
coords[1] = i;

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

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

mdxRow[j + 1] = val;
}
mdxTable.Rows.Add(mdxRow);
}
mdxDataSet.Tables.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.com> wrote in
message news:OZ**************@TK2MSFTNGP12.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.com

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

Hi, i am newbie here and need help. Subject: Multidimmensional 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("mdxTable");

DataRow mdxRow;

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

mdxTable.Columns.Add("Description");

for (int i=0; i<objCellSet.Axes[0].Positions.Count; i++)
{
strColName = objCellSet.Axes[0].Positions[i].Members[0].Caption;
mdxTable.Columns.Add(strColName);
}

Console.WriteLine(objCellSet.Axes[1].Positions.Count);

for (int i=0 ;i<objCellSet.Axes[1].Positions.Count; i++)
{
string val = "";

mdxRow = mdxTable.NewRow();

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

for (int j=0; j<objCellSet.Axes[0].Positions.Count; j++)
{
coords[0] = j;
coords[1] = i;

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

too

slow

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

mdxRow[j + 1] = val;
}
mdxTable.Rows.Add(mdxRow);
}
mdxDataSet.Tables.Add(mdxTable);