StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POVFPOLEDB Too large files
text
Body
copied!<p>I had the assignment of writing several queries to read in some .dbf files. The files are Visual FoxPro files and the big problem here is when I start using joins, because this makes the temporary files extremely big, eventually resulting in errors like:</p> <pre><code>File c:\users\me\appdata\local\temp\00001kjd000a.tmp is too large. </code></pre> <p>Furthermore the querying takes a really long time and this is not what I want. I tried accessing this data using sqlserver and also using c# code, but it is extremely slow.</p> <p>The sizes of the databases are about 350mb, 100mb and 10mb. I have these files locally to "speed up" the process, however a triple join from these takes more than 15min...</p> <p>and i know i will have to use another one which is 2gb</p> <p>code i'm using:</p> <pre><code>string connStr = @"Provider=VFPOLEDB.1;Data Source=D:\data\B. Mayer Real\;"; string qryStr = @" select top 100 * from db1 a, db2 b, db3 c where a.id = b.id and b.id = c.id order by a.id "; OleDbConnection conn = new OleDbConnection(connStr); conn.Open(); OleDbCommand cmd = new OleDbCommand(qryStr, conn); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); DataSet data = new DataSet(); adapter.Fill(data); DataTable table = data.Tables[0]; foreach (DataRow myDataRow in table.Rows) { Console.WriteLine("Found data {0}", myDataRow[1]); } Console.ReadLine(); conn.Close(); </code></pre> <p>EDIT:</p> <p>The biggest problem is simply going through the files... If I do something like this:</p> <pre><code>SELECT * FROM [CARATLOCAL]...[lzarb] where la_nummer = 364999 </code></pre> <p>This already takes 30 seconds</p> <p>This query took 38 minutes! (it's just one line)</p> <pre><code>select max(la_datum + convert(datetime, la_time, 108)) as book_datetime, la_nummer, la_index from [CARATLOCAL]...[lzarb] where la_datum is not null and la_time is not null and la_nummer = 364999 group by la_nummer, la_index </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload