Fastest way of accessing CSV files

hi,
I am handling CSV files as database. each CSV got 40,000 - 60,000
lines. I have to do the joining, subquery and other complex tasks here.
the problem is, my query execution time too high, 2-3 mins. how can I
reduce the time ? I saw there is no way of declaring keys for CSV files
in Schema.ini. I am using OleDb.
anotherthing,
can anyone tell me what should I use to this heavy task ? is there any
good replacement of OleDb ?
please let me know.

Jerry
Fri Aug 25 10:09:29 CDT 2006

Do you have access to any version of SQL Server?

What I learned to do was simply import data from a CSV file right into
a SQL table, that way I can handle data processing via T-SQL and the
SQL Engine.





Shiplu wrote:
> hi,
> I am handling CSV files as database. each CSV got 40,000 - 60,000
> lines. I have to do the joining, subquery and other complex tasks here.
> the problem is, my query execution time too high, 2-3 mins. how can I
> reduce the time ? I saw there is no way of declaring keys for CSV files
> in Schema.ini. I am using OleDb.
> anotherthing,
> can anyone tell me what should I use to this heavy task ? is there any
> good replacement of OleDb ?
> please let me know.

William
Fri Aug 25 10:55:05 CDT 2006

Another poster gave you half of the answer--import into a SQL Server table
using SqlBulkCopy.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Shiplu" <shiplu.net@gmail.com> wrote in message
news:1156510975.442237.37660@75g2000cwc.googlegroups.com...
> hi,
> I am handling CSV files as database. each CSV got 40,000 - 60,000
> lines. I have to do the joining, subquery and other complex tasks here.
> the problem is, my query execution time too high, 2-3 mins. how can I
> reduce the time ? I saw there is no way of declaring keys for CSV files
> in Schema.ini. I am using OleDb.
> anotherthing,
> can anyone tell me what should I use to this heavy task ? is there any
> good replacement of OleDb ?
> please let me know.
>

Shiplu
Fri Aug 25 11:08:08 CDT 2006

Atually I am not using any SQL server.
my database is just a folder and tables are some files
I am using C# .NET
any Idea ?

Jerry
Fri Aug 25 13:32:24 CDT 2006

Well, I don't see how you'll be able to gain much speed if you limit
yourself to keeping your data in large, unindexable text files. You
could try using the MS Jet as the query engine, unless that is what
you're already doing, to see if that could help at all.

SQL Express is free to download and install. You could even try the
SQL Everywhere engine, which is sorta like Jet in that it's just a set
of LIBs that load at runtime by the client app, instead of a service.

Hope this helps.

Shiplu wrote:
> Atually I am not using any SQL server.
> my database is just a folder and tables are some files
> I am using C# .NET
> any Idea ?

Cor
Fri Aug 25 22:46:08 CDT 2006

Jerry,

Completely in addition to Jerry, which advices I would take first.

As an intermidiate result you can convert your csv files to an XML dataset.

You get than very much methods (not SQL) to select, filter, sort and bind to
controls.

http://www.vb-tips.com/dbPages.aspx?ID=1b644f6b-aa01-49f6-bc1f-212f9e0de193

I hope this helps,

Cor

"Shiplu" <shiplu.net@gmail.com> schreef in bericht
news:1156510975.442237.37660@75g2000cwc.googlegroups.com...
> hi,
> I am handling CSV files as database. each CSV got 40,000 - 60,000
> lines. I have to do the joining, subquery and other complex tasks here.
> the problem is, my query execution time too high, 2-3 mins. how can I
> reduce the time ? I saw there is no way of declaring keys for CSV files
> in Schema.ini. I am using OleDb.
> anotherthing,
> can anyone tell me what should I use to this heavy task ? is there any
> good replacement of OleDb ?
> please let me know.
>