How to "append" a datatable?

  • Thread starter Thread starter Christian Maier
  • Start date Start date
C

Christian Maier

Hello!

I have huge unperformant "where like" querys to a mysql DB. So there
are some performance issues. To resolve my performance problem I thougt
to split the query into threads. I hold 10 connections to my database
and each connection queries in its own thread as the following:

conn1: select fname from root where p_key between 1 and 20000 fname
like '%somestring%';
conn2: select fname from root where p_key between 20001 and 40000 fname
like '%somestring%';
and so on ...

the root table contains today about 200000 rows growing

Now the problem is: How can I put the 10 datatable objects to one huge
object to bind to my grid object?

THX!
Christian Maier

PS Any other solution for my performance issue is welcome!
 
do you present the hole data (the 200000 records) to the user once?
don't you page it?

if you page it... why not just get the data (100 at a time) to populate the
current datagrid... there's some tutorials on it.


--


Thank you in Advance.

Bruno Alexandre
(a Portuguese in Denmark)
 
do you present the hole data (the 200000 records) to the user once?
don't you page it?
Click to expand...
the query is over 200000 records, the result should be <= 500 selects.
so the problem is not getting huge data into my datagrid, the problem
is the query it self. maybe there are only 10 selects from each thread.
but i do not know how to "union" all retured datatables out of my
threads.

christian
 
Hi Christian,

Christian Maier said:
I have huge unperformant "where like" querys to a mysql DB. So there
are some performance issues. To resolve my performance problem I thougt
to split the query into threads. I hold 10 connections to my database
and each connection queries in its own thread as the following:
Click to expand...

in my opinion you have to find out what causes this performance leak!

Is it:
- your app (the way it handles the resulting datatables, etc.)
- the query
- the database connection (drivers, network, etc.)
- the database itself (old or buggy, heavy load, etc.)

If you know what causes the leak you'll have a greater chance solving it.

If something's wrong with the query, the database, the database connection
then I don't think it makes anything faster querying it with multiple
threads.

Just ideas....

Greets,
Kurt
 
I have found that if you don't invoke Table.Clear then the DataAdapter will
append data to it. Interestingly, however, if it were the same records,
then the records get replaced. If this doesn't work for you, try creating
the dataset as a file in the project and load data to it, that's how I do
it.
 
+Vice said:
I have found that if you don't invoke Table.Clear then the DataAdapter will
append data to it. Interestingly, however, if it were the same records,
then the records get replaced. If this doesn't work for you, try creating
the dataset as a file in the project and load data to it, that's how I do
it.
Click to expand...

cool thank you for the hint!

Christian
 
in my opinion you have to find out what causes this performance leak!
yea I did!
- your app (the way it handles the resulting datatables, etc.)
- the query
- the database connection (drivers, network, etc.)
- the database itself (old or buggy, heavy load, etc.)
Click to expand...
it is the query because if you select * where foo like '%something'
every index on foo is useless!
just if you query where foo like 'something%' you can tune the query
with an index.

when I select * where pkey between 1 and 10000 and foo like
'%something'
then the optimizer goes trough the primary key. but you are right, the
boost is in a milliseconds range.

I tried to make a hash table but you can imagine that there is not
enough memory allowed to allocate by the database.

I am thinking about select the whole data into a textfile and do in
instr() to find the lines I need but the file will be 50 MB at least I
do not know if this meight be faster.

thx!
Christian
 
Back
Top