Re: [sqlite] Importing a CSV file
On Fri, Feb 25, 2005 at 07:59:01AM -0500, Brass Tilde wrote : > > CREATE TABLE All_Chr_CxCb_atleast1 ( > > LOCUSLINK TEXT, > > GO, BLOB, > > CAA INTEGER, > > > You've got an extra comma here after the "GO" field. That makes the parser > think your trying to create field named GO, followed by one named BLOB, then > one named CAA. Remove the comma. > > That should have been obvious if you'd attempted to select something from > the table with headers turned on in the command line utility. Thank you so much, and sorry for sending this stupid question. Sometimes things ar just too obvious to be seen (in french, we say they are "on one's nose"). In fact, as the table was empty, I did not know how to start the troubleshooting. I tried to dump the table, but sqlite keeps the exact formatting of the sql instruction : sqlite> .dump BEGIN TRANSACTION; CREATE TABLE All_Chr_CxCb_atleast1 ( TCIDTEXT, STRAND TEXT, CHROMOSOME TEXT, START_POS INTEGER, STOP_POSINTEGER, REPRESENT_POS INTEGER, TKIDINTEGER, REPRESENT TEXT, SYMBOL1 TEXT, SYMBOL2 TEXT, LOCUSLINK TEXT, GO, BLOB, CAA INTEGER, CAC INTEGER, Maybe something to change in the future ? Thanks once again, -- Charles
Re: [sqlite] sqlite performance variationin linux and windows
build a version up to 10-15% faster I saw the same kind of speedup (and sometimes up to 25%) depending on if assertions are enabled. I believe they are enabled by default. Adding -DNDEBUG turns them off. Roger
Re: [sqlite] sqlite performance variationin linux and windows
http://www.adtmag.com/article.asp?id=7421 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vcconMixedDLLLoadingProblem.asp It's been awhile since Iooked at this, but I distinctly remember reading from one of the product managers at MS that this wouldn't be fixed until 2005. The basic details were, I think, that the 'native' multithreaded C++ applications, under heavy load, would kill deadlock and halt. Since I work for a speech recognition company, the cpu is *always* under heavy load, and the problem has kept us from moving to .NET . Otherwise, I think we would probably do switch, since there are some frustrating bugs in VC6 that have been fixed. I also don't know if this has been fixed already, or not. --Keith On Fri, 25 Feb 2005 17:31:14 -0500, Andrew Piskorski <[EMAIL PROTECTED]> wrote: > On Fri, Feb 25, 2005 at 02:25:06PM -0800, Keith Herold wrote: > > Well, MS has said that VC++ .NET has a serious multithreading issue; > > a lot of companies are staying on VC6 for that reason. > > Keith, I wasn't aware of that. Could you point us to more info on the > problem, please? > > -- > Andrew Piskorski <[EMAIL PROTECTED]> > http://www.piskorski.com/ > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] sqlite performance variationin linux and windows
Hi Appadurai, >From my experience the stock sqlite.dll is not very fast. I guess it's build with VC++ 6. Using VC++ 7.1 I was able to build a version up to 10-15% faster, not a bad optimization for 0 lines of code :) . It was a little bigger, about +25%, but for desktop this is not a problem. I only did this for sqlite 2.8.*, but it should also work for 3.* . Is there any technical reason why the "stock" dll is build with VC++ 6 while better compilers are available ? Ionut Filip -Original Message- From: Neelamegam Appadurai [mailto:[EMAIL PROTECTED] Sent: Friday, February 25, 2005 2:05 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] Re: sqlite performance variationin linux and windows Hi Chris, Thanks for the detailed explanation, I will dig deeper into my code to see if there is anything wrong in the OS specific code in my appln. Anyway thanks for the support. appadurai
Re: [sqlite] Functions
marco wrote: Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() All but the date/time functions are listed in func.c in a table that's part of sqlite3RegisterBuiltinFunctions(); the date/time functions are listed in a similar table in sqlite3RegisterDateTimeFunctions in date.c.
Re: [sqlite] SQL Help (Joining and Grouping)
Robert Simpson wrote: > SELECT games.id, team1_id, team2_id, a.name AS team1, b.name AS team2, result > FROM games > INNER JOIN teams AS a ON team1_id = a.id > INNER JOIN teams AS b ON team2_id = b.id > WHERE a.name LIKE 'Suns' AND b.name LIKE 'Bulls' > > That'll find all games where the Suns played the Bulls and you can use that > as an example of how you'd query for other things as well. > Robert, excellent. That's exactly what I need. I didn't know that you could actually inner join the same table twice. That's quite powerfull. Thanke you Gilbert -- "The best portion of a good man's life is his little, nameless, unremembered acts of kindness and love." - Wordsworth, William
[sqlite] SQL Help (Joining and Grouping)
Hello, I send this once some days ago, but it didn't appear on the list, so i hope this time it'll work out. I know this is not directly related to SQLite, but hopefully someone has the time to help me with a (probably) basic SQL question: Consider the following tables: CREATE TABLE teams (id,name); CREATE TABLE games (id, date, team1_id, team2_id, result); team1_id and team2_id refer to the id in the teams table. 1. What query would be best suited to get an output so that the output would contain the teams names (not only the id) and the dates and results? 2. What query would be best if I want to search for games that two specific teams had played. For example, I want all the games that the teams with the names 'bla' and 'blub' played against each other. Is there any way to do that? I tried this with joins, with groupings, but wasn't able to get the lists i wanted. I can get the name of one team, but not the name of the other team. Any help would be appreciated Gilbert
Re: [sqlite] Importing a CSV file
> CREATE TABLE All_Chr_CxCb_atleast1 ( > TCIDTEXT, > STRAND TEXT, > CHROMOSOME TEXT, > START_POS INTEGER, > STOP_POSINTEGER, > REPRESENT_POS INTEGER, > TKIDINTEGER, > REPRESENT TEXT, > SYMBOL1 TEXT, > SYMBOL2 TEXT, > LOCUSLINK TEXT, > GO, BLOB, > CAA INTEGER, You've got an extra comma here after the "GO" field. That makes the parser think your trying to create field named GO, followed by one named BLOB, then one named CAA. Remove the comma. That should have been obvious if you'd attempted to select something from the table with headers turned on in the command line utility.
Re: [sqlite] Re: sqlite performance variationin linux and windows
Hi Chris, Thanks for the detailed explanation, I will dig deeper into my code to see if there is anything wrong in the OS specific code in my appln. Anyway thanks for the support. appadurai On Fri, 25 Feb 2005 10:33:35 +0530, Neelamegam Appadurai <[EMAIL PROTECTED]> wrote: > Hi, > Thanks for the quick response and the interest you're showing, > I am testing the performance of linux and windows using > a. same testing data for both. > b. db schema is common for both. > c. though the test is conducted on two different machines but the > machine configurations are similar. > > Only consideration is at very few places jin the application, code is > platform dependent ie the api calls may differ based on the type of > OS, but this is also seldom used in our application. > But still, For the same application on windows, performance of sqlite > is slower compared to file system read or write which we were using > earlier. > Thanks once again for the reply, > appadurai > > > On Fri, 25 Feb 2005 15:28:23 +1100, Chris Schirlinger > <[EMAIL PROTECTED]> wrote: > > On 25 Feb 2005 at 9:38, Neelamegam Appadurai wrote: > > > > > Could anyone please give me reason for variation in performance > > > between linux and windows. > > > > How are you testing this performance? Do you have a program written > > in windows and another written for linux? If so, the code/mechanism > > of DB access may be the issue > > > > Is it the same phisical DB? Same schema? Same machine specs? Same > > amount of data? > > > > All those things could contribute. > > > > >
RE: [sqlite] Re: sqlite performance variationin linux and windows
> -Original Message- > From: Neelamegam Appadurai [mailto:[EMAIL PROTECTED] > Sent: 25 February 2005 05:04 > To: sqlite-users@sqlite.org; [EMAIL PROTECTED] > Subject: Re: [sqlite] Re: sqlite performance variationin > linux and windows > > Hi, > Thanks for the quick response and the interest you're > showing, I am testing the performance of linux and windows > using a. same testing data for both. > b. db schema is common for both. > c. though the test is conducted on two different machines but > the machine configurations are similar. I would say you need to do some profiling to find out which calls are taking longer. Which version of Windows is this? At one time I discovered an extraordinary slowdown with Sqlite on Windows 98 (but not 2000 or XP). Keeping a transaction open fixed the problem in that case. Tim
Re: [sqlite] Re: sqlite performance variationin linux and windows
Hi, Neelamegam Appadurai wrote: Hi, Thanks for the quick response and the interest you're showing, I am testing the performance of linux and windows using a. same testing data for both. b. db schema is common for both. c. though the test is conducted on two different machines but the machine configurations are similar. Only consideration is at very few places jin the application, code is platform dependent ie the api calls may differ based on the type of OS, but this is also seldom used in our application. But still, For the same application on windows, performance of sqlite is slower compared to file system read or write which we were using earlier. Thanks once again for the reply, appadurai The file system you are using could also have a large impact. If using FAT or FAT32, I recommend moving to NTFS if possible. HTH Ulrik P.
