Help on performance question

[Guest]

Dear all,

I have a windows application which is able to collect particular data on a
remote Access database that can be in use by other local application
I cannot change the structure of that remote database.

From my application i collect data between 3 different linked tables.
The way I am collecting data, I am collecting first, records from table A
ans store them in a dataset named DatasetA

Then from that DataSetA I scan each unique records and retrive corresponding
entry from the remote database through the same connection .

What I have noticed is that, it takes quite long time in my mind to retrive
data especially if DataSetA records get many entries in other tables

For example:
If in DataSetA i have 100 rows and for each rows I have 10 records in other
tables, which means 1000 records, it takes around 2minutes.

I was wondering if I am cllecting data in the proper way or is they a way to
speedup that row scan ?

thanks for wyour reply
regards
serge

 

[Robbe Morris [C# MVP]]

Do you really need to bring all 1000 back in a single query?

That is a lot to hold in memory. Are the columns in
your WHERE clause for table B indexed properly?

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

 

[Guest]

Hi,

no I do net get the whole records in asingler queery becasue as the remote
databased is distributing data to soem other local application, I do not waht
to get some locking trouble and as you know that it is access DB I do not
want to cause any risk.

Fro that reason I take in a first querry concerned data for TAble A (100)
records and store then in a TableADataset

Then I scan row by row this dataset and retrive other corresponding data
from the remote DB in a second dataset. (this the place wher it could take
time)

Indexes in the remote table, they are sure, but I am not the owner of that
database and cannot change any structure. I just have the right to extract
data

I have been told that retriveind requested data in an SQL querry is faster
that retriving locally each Table content in dataset and define relation
between those tables

what do you think

regards
serge

 

[Rogas69]

Hi Serge,

As I understand you perform 100 queries in a loop - one for each row you
retrieved in first stage, to get 10 records. This is wrong. You should get
all records that are related to records from DataSetA using joined query. If
you want to filter records you should do it on client side - in your
program.

Regards

Peter

 

[Guest]

Hi,

I thing this si what I am doing.
TO fill up DataSetA I execute the following code :

m_OldbAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT ID,
S_START_TIME,END_QUALITYST FROM REELHIST ORDER BY S_START_TIME ASC",
m_OldbCon)
m_OldbAdapter.Fill(DataSetA )
m_iMaxRecord = DataSetA .Tables(0).Rows.Count


Then from that dataset I browse all records to get other fields as follow:

sSql = "SELECT DISTINCT EVENTHIST.MACHINE_ID, MACHINE.NAME_TEXT_CONTENT "
sSql = sSql & "FROM EVENTHIST INNER JOIN MACHINE ON EVENTHIST.MACHINE_ID =
MACHINE.ID"
sSql = sSql & " WHERE REELHIST_ID = " & ReelId

adapter.SelectCommand = New OleDb.OleDbCommand(sSql, m_oldbCon)
adapter.Fill(dsEventHistMachine)
m_iMaxRecord = dsEventHistMachine.Tables(0).Rows.Count

Note that the parameter ReelId pass to that join querry is the primary key
ID from the first querry

thnaks for your comments
serge