Re: [sqlite] General help, a bit OT
OK, I've played with this a bit. Adding new columns to the datatable through a custom function is really slow because it enumerates every cell in the column. I don't need that -- I just need a blank column -- full of nulls -- but with a column name. I can add the columns to the datatable after I've retreived it, or store the empty columns in the database. Seems like there might be something better. Thanks __ My original question: I have to read thousands of tables and operate on each one in sequence. I need to do this fast. I want to read the data from 6 stored columns and many rows from each table. Once the table is read, I need to create derived columns (e.g. (new) Column C = column A + ColumnB). I'm finding it pretty slow to add the new columns to each table before working on it. I'm finding it faster to store the columns to be derived (empty) in the database -- but this makes the db larger. Is there an established, best practice, method for this problem? More generally, is there a place I could look for advice on how to optimize these procedures? Thanks - Original Message - From: "Szomraky, Stefan" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Tuesday, July 15, 2008 8:40 AM Subject: Re: [sqlite] General help, a bit OT >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader >> Sent: Tuesday, July 15, 2008 2:28 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] General help, a bit OT >> >> Oh, sorry, the example was just an example. The calculations >> I need to make on on ColC cannot be done within SQLite. >> >> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? >> > > > AFAIK, the current .NET provider for SQLITE supports custom functions > implemented in .NET. > Maybe this will work? > > SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; > > You might also want to implement the function in C for speed > improvements. > > Greetings, > Stefan. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
Thanks Robert... very informative. - Original Message - From: "Robert Simpson" <[EMAIL PROTECTED]> To: "'General Discussion of SQLite Database'" Sent: Tuesday, July 15, 2008 9:58 PM Subject: Re: [sqlite] General help, a bit OT > Without causing a rehash of that long-debated topic, I'll say this: As it > relates to SQLite, generally you will get better performance out of an > optimized C application talking to SQLite than you will out of .NET > talking > to SQLite. > > The difference can be insignificant to a user application however -- as > someone is probably not going to notice the difference between your C app > inserting 100,000 rows a second vs. .NET's 90,000 rows a second. > > I did a simple insert/read/update test comparing the ADO.NET provider to > raw > C++, results here: > > http://sqlite.phxsoftware.com/forums/t/19.aspx > > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of cstrader > Sent: Tuesday, July 15, 2008 5:36 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] General help, a bit OT > > is it true that C is always (or even generally) faster than .net? > > > - Original Message - > From: "Szomraky, Stefan" <[EMAIL PROTECTED]> > To: "General Discussion of SQLite Database" > Sent: Tuesday, July 15, 2008 8:40 AM > Subject: Re: [sqlite] General help, a bit OT > > >>> -Original Message- >>> From: [EMAIL PROTECTED] >>> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader >>> Sent: Tuesday, July 15, 2008 2:28 PM >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] General help, a bit OT >>> >>> Oh, sorry, the example was just an example. The calculations >>> I need to make on on ColC cannot be done within SQLite. >>> >>> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? >>> > >> >> AFAIK, the current .NET provider for SQLITE supports custom functions >> implemented in .NET. >> Maybe this will work? >> >> SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; >> >> You might also want to implement the function in C for speed >> improvements. >> >> Greetings, >> Stefan. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
Without causing a rehash of that long-debated topic, I'll say this: As it relates to SQLite, generally you will get better performance out of an optimized C application talking to SQLite than you will out of .NET talking to SQLite. The difference can be insignificant to a user application however -- as someone is probably not going to notice the difference between your C app inserting 100,000 rows a second vs. .NET's 90,000 rows a second. I did a simple insert/read/update test comparing the ADO.NET provider to raw C++, results here: http://sqlite.phxsoftware.com/forums/t/19.aspx -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of cstrader Sent: Tuesday, July 15, 2008 5:36 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] General help, a bit OT is it true that C is always (or even generally) faster than .net? - Original Message - From: "Szomraky, Stefan" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Tuesday, July 15, 2008 8:40 AM Subject: Re: [sqlite] General help, a bit OT >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader >> Sent: Tuesday, July 15, 2008 2:28 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] General help, a bit OT >> >> Oh, sorry, the example was just an example. The calculations >> I need to make on on ColC cannot be done within SQLite. >> >> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? >> > > > AFAIK, the current .NET provider for SQLITE supports custom functions > implemented in .NET. > Maybe this will work? > > SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; > > You might also want to implement the function in C for speed > improvements. > > Greetings, > Stefan. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
is it true that C is always (or even generally) faster than .net? - Original Message - From: "Szomraky, Stefan" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Tuesday, July 15, 2008 8:40 AM Subject: Re: [sqlite] General help, a bit OT >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader >> Sent: Tuesday, July 15, 2008 2:28 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] General help, a bit OT >> >> Oh, sorry, the example was just an example. The calculations >> I need to make on on ColC cannot be done within SQLite. >> >> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? >> > > > AFAIK, the current .NET provider for SQLITE supports custom functions > implemented in .NET. > Maybe this will work? > > SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; > > You might also want to implement the function in C for speed > improvements. > > Greetings, > Stefan. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
Oh, well that's interesting. I didn't know I could apply a function to create a new column as I read the data. I have to figure out how to make that work in vb.net. More generally, can I create a new blank column in my datatable as I read from an existing set of columns? Or must I do a copy from an existing variable? Thanks - Original Message - From: "Szomraky, Stefan" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Tuesday, July 15, 2008 8:40 AM Subject: Re: [sqlite] General help, a bit OT >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader >> Sent: Tuesday, July 15, 2008 2:28 PM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] General help, a bit OT >> >> Oh, sorry, the example was just an example. The calculations >> I need to make on on ColC cannot be done within SQLite. >> >> > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? >> > > > AFAIK, the current .NET provider for SQLITE supports custom functions > implemented in .NET. > Maybe this will work? > > SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; > > You might also want to implement the function in C for speed > improvements. > > Greetings, > Stefan. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of cstrader > Sent: Tuesday, July 15, 2008 2:28 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] General help, a bit OT > > Oh, sorry, the example was just an example. The calculations > I need to make on on ColC cannot be done within SQLite. > > > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? > > AFAIK, the current .NET provider for SQLITE supports custom functions implemented in .NET. Maybe this will work? SELECT ColA, ColB, custCLRfct(ColA, ColB) AS ColC FROM tab1; You might also want to implement the function in C for speed improvements. Greetings, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
Oh, sorry, the example was just an example. The calculations I need to make on on ColC cannot be done within SQLite. - Original Message - From: "Szomraky, Stefan" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Tuesday, July 15, 2008 8:19 AM Subject: Re: [sqlite] General help, a bit OT > >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of cstrader >> Sent: Tuesday, July 15, 2008 2:03 PM >> To: General Discussion of SQLite Database >> Subject: [sqlite] General help, a bit OT >> >> A bit off topic, but perhaps someone could direct me. >> >> I'm in vb.net >> >> I have to read thousands of tables and operate on each one in >> sequence. I need to do this fast. >> >> I want to read the data from 6 stored columns and many rows >> from each table. >> >> Once the table is read, I need to create derived columns >> (e.g. (new) Column C = column A + ColumnB). > > > Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? > > Greetings, > Stefan Szomraky. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] General help, a bit OT
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of cstrader > Sent: Tuesday, July 15, 2008 2:03 PM > To: General Discussion of SQLite Database > Subject: [sqlite] General help, a bit OT > > A bit off topic, but perhaps someone could direct me. > > I'm in vb.net > > I have to read thousands of tables and operate on each one in > sequence. I need to do this fast. > > I want to read the data from 6 stored columns and many rows > from each table. > > Once the table is read, I need to create derived columns > (e.g. (new) Column C = column A + ColumnB). Why does SELECT ColA, ColB, ColA+ColB AS ColC FROM tab1; not work? Greetings, Stefan Szomraky. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] General help, a bit OT
A bit off topic, but perhaps someone could direct me. I'm in vb.net I have to read thousands of tables and operate on each one in sequence. I need to do this fast. I want to read the data from 6 stored columns and many rows from each table. Once the table is read, I need to create derived columns (e.g. (new) Column C = column A + ColumnB). I'm finding it pretty slow to add the new columns to each table before working on it. I'm finding it faster to store the columns to be derived (empty) in the database -- but this makes the db larger. Is there an established, best practice, method for this problem? More generally, is there a place I could look for advice on how to optimize these procedures? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
