Why its much slower with configfile? (when load csv file to table)

[belotrei]

Currently I'm trying to build an app that will load csv file for indexlist. It's so fast when loading, but if i include the configfile (file that should be included if you want distribute app with net frame 2) it become much more slower.

This is what i do:
I created app that will load csv (6000 records) to table at start. It's fast without appname.exe.config in the directory.
But if i include the appname.exe.config file it becomes much slower. I know, net 2 is not needed for table control only, but later i want to add sql function to get the rest of large database. And for this i must add configfile to enable net 2 function for sql..

Help please?

 

[Cableguy]

Do you really need the csv file? iwll it be comming from a third party to use in your app?

If not, or even if so, why not convert it previously to sqlite?
DZT as provided a very nice app/example on how to convert any database format to sql...

I can see what you mean and i think that if there is that much of a diference it probably has to do with .NET CF 2 rather that B4PPC, and you say you will be using sql lib later on your app, so no loss...

 

[belotrei]

Ok, thanks Cableguy.. Just for infos, i've tried these things :

1. Convert csv file format (one column) (6000 records) to sql format and then load it to table (cmd.executetable) --> Took 31 seconds. :sign0148:
2. Load csv with (one column) (6000 records) to table control (without configfile) --> only took < 3 seconds. (WoooWWW...)
3. Load csv with (one column) (6000 records) --> same file as number #2 (6000 records) to table control (BUT this time with configfile) --> took 16 seconds.


Table control loads csv very fast... (without configfile, case #2) --> good for index list.
Sql allowed us to get the data that only needed at one time from large database (ex: when we select one item we want at index list). --> but sql need configfile (net frame 2) that makes table control loads csv so slow..

So, how to get this combining SPEED power of them?


(Tests above are done at ipaq 2750 624 Mhz, 128 RAM, WM 2003, Net cf2 installed)

Cheers.

 

[Erel]

I see this strange behavior too.
I will further investigate this issue.
Anyway, I recommend you to use an SQL database.
Case #1 is irrelevant as you should only convert the data once and later you should save it as an SQL table.

 

[belotrei]

Thanks Erel, hope this problem can be solved soon. Because table and sql are very useful when using them together.
And i guess, it will solve lots of database speed problem.

For case #1 i did like this: (maybe i didn't explain it carefully, sorry)
1. convert csv file (1 column, 6000 records) to sql file. DONE. --> on desktop.
2. copy the sql file that has been created to pda.
3. load it with sql command to table control.

So, converting csv to sql is not done anymore in app that open sql data to table on pda.

The code is just like this :

---
Sub App_Start
Form1.show
Con.New1
Cmd.New1("",con.Value)
Con.Open("Data Source = " & AppPath & "\data.sl3")
cmd.CommandText = "SELECT index FROM master"
cmd.ExecuteTable("table1",0)
Con.close
End Sub

Maybe there's something wrong with code/procedure that makes this process took 31 seconds on pda?


Thanks.

 

[Erel]

Using the SQL commands you could choose to retrieve only the required rows.
It is usually not useful to show the use a table with 6000 rows. Choosing only the important rows will be much faster and more user friendly.

 

[dzt]

what about searching in you table index belotrei?

It could be much faster to make a select in the db rather than filtering the table.

I think that you should load chanks of data in your table, lets say 100 rows based on search criteria or sql top.

If I don't miss something having a table with 6000 rows the user can't find anything going up and down

 

[belotrei]

So is it possible to make an index list with 6000 records but with fast loading?
Actually this has been solved by csv loads to table, but, again, config file problem if i want to make app with sql too.

or how if i want to make, lets say dictionary program with 6000 records in index list and want to load them all. Because i saw lingvosoft dictionary program can do that. Or maybe lingvosoft uses another method?

I want the user can see the whole index, and when the user press key in the searchbox it will filter the table and then when user click one item of the table it will load the database that only needed from sql. The filter function of table is GreAt and fast! No problem with that. But the loading time with select * from xxxxx (sql function) to table is poor. Until i tried the case #2, but yeah configfile problem make it slow.

The problem is i want to use them (table and sql) together, but with csv loads fast to table on start and with configfile (net frame 2) so i can use the sql control too for the rest of database.

Sorry repeat the problem above again. Hope you can solve this configfile problem.

Really need help on this.. i've got headache for this problem...

Thanks Erel..

 

[belotrei]

Thanks dzt, i've just read your reply. Actually i'm still want to know about csv loads (table control) why it can be so fast compare to select all from sql to table. (Table filtering also fast too, no prob.) I'm curious also because i've tried that method (csv to table) and it works like magic (so fast i mean). So why don't use that method if configfile problem can be solved?

Maybe if you don't mind, you can check to lingvosoft.com, or absoluteword.com (roadlingua software). Both of them are dictionary programs that pretty close to the app that i want to develop.

Cheers.

 

[dzt]

I see what you mean belotrei about these dictionaries and I'm pretty sure that they don't load all of their words in a list or grid control (like b4ppc table) but they use more complex methods.

If I will find some time I'll make an example of what I mean.

Hope You will find a solution soon.

Regards

 

[dzt]

AutoComplete (It could be ;-)

Here is a little example.

It is unfinished but shows my way about the solution to your problem.
It can handle quickly thousands of records.

Needs System.Data.SQLite.DLL, SQLDevice.DLL and ScrollBar.DLL and includes a demo SQLite database.

I Hope I helped.

 

[belotrei]

WoooWWWW dzt! You're genius! Very good and crystal clear code..
Yeah, thats a little bit different way, but it works!

One more word : Amazing!

Thank you man! :sign0188:

(Ps: but there's one little problem : if the records contains ' sign and it got to be counted with the >= / <= command it will give error message.. )

Ex: one record like this : Jack's house and if this record is processed by "select word, description from words where word <= ' "
or with >=. it will give error message.

Is there solution for this?

Thanks a LOTS!

 

[dzt]

Check for ' and add one more.

select word, description from words where description >= 'Jack''s house'