lazy load, very lazy ....

  • Thread starter Thread starter Yair Cohen
  • Start date Start date
Y

Yair Cohen

hi all.
i use mysql as the db.
i use .net ado to run a select query to return a datatable:
something like this:

dim x as new dbdataadapter(".... select ...", cnn)
dim dt as new datatable
x.fill(dt)
return dt

' **
the probolem is, when i run this query on the db tool (i use control center
for mysql) the result come up in 0.5 seconds (3000 rows, 6 joins...) thats
grate, but in my program the line x.fill(dt) ( the build of the datatable)
takes neerly 30 seconds !!!!!
i never meet so huge difference.
what can i do to improve this ???

thanks, Yair
 
Don't use a query that uses a join and instead pull each table over
individually with only the records you need and relate them with a
datarelation object.
 
Thanks
i know this solution, but for now, it look for a quicker solution because to
do this it will take me a lot of time i want tosave. if u have any solution,
there must be a solution i can't beleave it takes so long !
thanks anyway :)
 
Yair:

Using a DataRelation takes essentially two lines of code to relate any given
two tables so it's probably not that much of an investment. 3000 rows is
nothing to pull over so if it's taking 30+ seconds from ADO.NET, there may
be many things causing this... If you use a DataReader to pull over the
data, how much does that speed thigns up? i'm sure it's the six joins and
while you can definitely do it the way you are working it, the datarelation
was put in for this very purpose and your updates are probably quite complex
if you are doing 6 tables joins via the dataadapter. A DataReader should be
able to speed this up quite a bit (so should using a datarelation) but if
you use a Reader ,it should be very little work at all to do the port from a
dataadapter.

I'd still make sure I had my query tuned and that my joins were on indexed
fields and all of that good stuff b/c data tends to get larger so even if
you get a working solution now, it may get sluggish inthe future. I guess
first I'd try the datareader and see how that works and if it won't work
for you, I'd seriously consider using the datarelation (for many reasons of
which performance is only one)
 
Thanks,
because it .net and i havn't the expirience i have now i do all the work
around the datatable, means i have an object that maps a datarow to the
object related to it. so all of the mapping objects (20 +/-) relies on this,
and u know, the work might take 2-3 hours, but the errors that can show up
may take 2-3 days, this what i want to save.
i just want it to work now, i'm working on a newer version already that user
the better way.
so thanks a lot, and if u will think about any solution with this conditions
it will be great !
(this is a server and there are only 4 station and if the loading of ado.net
was so lazy, it was working perfect to my needs, so i didn't use cacheing
thinking ADO.NET will handle that work quicker. now i added cacheing, for
now it suffish, not for the future...)
BTW i use the mapping object also to generate the fields list to update so
i'm not updating by the adapter....
thanks
 
Hi Yair,

Are you sure that Control Center (I've never used it) acutally fetches all
data and not just first few rows?
 
not all, the first 1000,
but, i reduce the amount of the testing records to get something like 1500
rows,
when 1000 rows it loads in 0.12 secs, in ado.net, it fills 1500 rows in
15-20 seconds !!

Miha Markic said:
Hi Yair,

Are you sure that Control Center (I've never used it) acutally fetches all
data and not just first few rows?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Yair Cohen said:
hi all.
i use mysql as the db.
i use .net ado to run a select query to return a datatable:
something like this:

dim x as new dbdataadapter(".... select ...", cnn)
dim dt as new datatable
x.fill(dt)
return dt

' **
the probolem is, when i run this query on the db tool (i use control center
for mysql) the result come up in 0.5 seconds (3000 rows, 6 joins...) thats
grate, but in my program the line x.fill(dt) ( the build of the datatable)
takes neerly 30 seconds !!!!!
i never meet so huge difference.
what can i do to improve this ???

thanks, Yair
 
Hi Yair,

Just for a test - try invoking da.SelectCommand.ExecuteReader() and see how
much time it takes.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Yair Cohen said:
not all, the first 1000,
but, i reduce the amount of the testing records to get something like 1500
rows,
when 1000 rows it loads in 0.12 secs, in ado.net, it fills 1500 rows in
15-20 seconds !!

Miha Markic said:
Hi Yair,

Are you sure that Control Center (I've never used it) acutally fetches all
data and not just first few rows?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Yair Cohen said:
hi all.
i use mysql as the db.
i use .net ado to run a select query to return a datatable:
something like this:

dim x as new dbdataadapter(".... select ...", cnn)
dim dt as new datatable
x.fill(dt)
return dt

' **
the probolem is, when i run this query on the db tool (i use control center
for mysql) the result come up in 0.5 seconds (3000 rows, 6 joins...) thats
grate, but in my program the line x.fill(dt) ( the build of the datatable)
takes neerly 30 seconds !!!!!
i never meet so huge difference.
what can i do to improve this ???

thanks, Yair
 
i have done this (with 400 rows for test) its take the same time !
Miha Markic said:
Hi Yair,

Just for a test - try invoking da.SelectCommand.ExecuteReader() and see how
much time it takes.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

Yair Cohen said:
not all, the first 1000,
but, i reduce the amount of the testing records to get something like 1500
rows,
when 1000 rows it loads in 0.12 secs, in ado.net, it fills 1500 rows in
15-20 seconds !!

Miha Markic said:
Hi Yair,

Are you sure that Control Center (I've never used it) acutally fetches all
data and not just first few rows?

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

hi all.
i use mysql as the db.
i use .net ado to run a select query to return a datatable:
something like this:

dim x as new dbdataadapter(".... select ...", cnn)
dim dt as new datatable
x.fill(dt)
return dt

' **
the probolem is, when i run this query on the db tool (i use control
center
for mysql) the result come up in 0.5 seconds (3000 rows, 6 joins...) thats
grate, but in my program the line x.fill(dt) ( the build of the datatable)
takes neerly 30 seconds !!!!!
i never meet so huge difference.
what can i do to improve this ???

thanks, Yair
 
Back
Top