Re: LiveCode and SQLite performace
You do not remember correctly. What I said (if I am remembering correctly) is that you need at least the size of your memory free and then some. This is because your OS will page out your memory into virtual memory, which is just a disk cache. But other apps also need room to grow for other things. I have always maintained that 10% is a red line you should never cross without risking damage to the OS or other apps data. Bob S On Jul 24, 2015, at 08:19 , Richard Gaskin ambassa...@fourthworld.commailto:ambassa...@fourthworld.com wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Okay I did not remember your statement correctly. But I have noticed after 50% things start slowing down a little. And if I get to 20% they have slowed down a lot. I do agree at 10% you are risking damage but I would say from the poor performance I have seen at 20% it suggest the drive is rising damage. 30% and above seems to work a lot better for me. Even 10% seems to be a lot of wasted space since on a 1TB drive that is around 100gb. That is how it works for me and it is not limited to 1 drive. As for the memory I usually only have one app open at a time so that is not a problem. John Balgenorth On Jul 28, 2015, at 8:49 AM, Bob Sneidar bobsnei...@iotecdigital.com wrote: You do not remember correctly. What I said (if I am remembering correctly) is that you need at least the size of your memory free and then some. This is because your OS will page out your memory into virtual memory, which is just a disk cache. But other apps also need room to grow for other things. I have always maintained that 10% is a red line you should never cross without risking damage to the OS or other apps data. Bob S On Jul 24, 2015, at 08:19 , Richard Gaskin ambassa...@fourthworld.commailto:ambassa...@fourthworld.com wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Another thing to consider is how many files you are working with and creating in a day. I am easily in the hundreds and that could make things worse than for someone who is just working with the same few files. John Balgenorth On Jul 28, 2015, at 12:59 PM, JB sund...@pacifier.com wrote: Okay I did not remember your statement correctly. But I have noticed after 50% things start slowing down a little. And if I get to 20% they have slowed down a lot. I do agree at 10% you are risking damage but I would say from the poor performance I have seen at 20% it suggest the drive is rising damage. 30% and above seems to work a lot better for me. Even 10% seems to be a lot of wasted space since on a 1TB drive that is around 100gb. That is how it works for me and it is not limited to 1 drive. As for the memory I usually only have one app open at a time so that is not a problem. John Balgenorth On Jul 28, 2015, at 8:49 AM, Bob Sneidar bobsnei...@iotecdigital.com wrote: You do not remember correctly. What I said (if I am remembering correctly) is that you need at least the size of your memory free and then some. This is because your OS will page out your memory into virtual memory, which is just a disk cache. But other apps also need room to grow for other things. I have always maintained that 10% is a red line you should never cross without risking damage to the OS or other apps data. Bob S On Jul 24, 2015, at 08:19 , Richard Gaskin ambassa...@fourthworld.commailto:ambassa...@fourthworld.com wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
On Sat, Jul 25, 2015 at 1:03 AM, Richard Gaskin ambassa...@fourthworld.com wrote: Yes, I also use Time Machine, but relying on any single backup isn't enough; TimeMachine can easily be set up to use multiple HDs so that you are not relying on a single backup. Even so I fully agree with everything you said. I have multiple HDs and use different software set-up differently. I'm so paranoid of the loss of time from HD/Computer failure that I run a regular clone so that should disaster strike it's a straight unplug - replug and Start and hold down the Option Key. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
I've found DiskWarrior to be an indispensable tool on my Mac. It fixes disc corruption that diskutil can't and optimizes the directory. It might be worth a try. Bill William Prothero http://ed.earthednet.org On Jul 24, 2015, at 4:50 AM, JB sund...@pacifier.com wrote: 10% might work for you but it definitely does not work for me. I have a 1tb drive and 348 gb free space. Most operations run slower than normal. I deleted about 150gb of music to bring it up to 350gb because it ran too slow to use. Now it runs fast enough to use but I still have a lot of wasted time. I had a 350gb drive and had the same problems way before 35gb free space. John Balgenorth On Jul 24, 2015, at 4:15 AM, Robert Brenstein r...@robelko.com wrote: A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
I'm z On Jul 24, 2015, 10:23 AM, at 10:23 AM, EED-wp Email proth...@earthednet.org wrote: I've found DiskWarrior to be an indispensable tool on my Mac. It fixes disc corruption that diskutil can't and optimizes the directory. It might be worth a try. Bill William Prothero http://ed.earthednet.org On Jul 24, 2015, at 4:50 AM, JB sund...@pacifier.com wrote: 10% might work for you but it definitely does not work for me. I have a 1tb drive and 348 gb free space. Most operations run slower than normal. I deleted about 150gb of music to bring it up to 350gb because it ran too slow to use. Now it runs fast enough to use but I still have a lot of wasted time. I had a 350gb drive and had the same problems way before 35gb free space. John Balgenorth On Jul 24, 2015, at 4:15 AM, Robert Brenstein r...@robelko.com wrote: A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
@ On Jul 24, 2015, 10:23 AM, at 10:23 AM, EED-wp Email proth...@earthednet.org wrote: I've found DiskWarrior to be an indispensable tool on my Mac. It fixes disc corruption that diskutil can't and optimizes the directory. It might be worth a try. Bill William Prothero http://ed.earthednet.org On Jul 24, 2015, at 4:50 AM, JB sund...@pacifier.com wrote: 10% might work for you but it definitely does not work for me. I have a 1tb drive and 348 gb free space. Most operations run slower than normal. I deleted about 150gb of music to bring it up to 350gb because it ran too slow to use. Now it runs fast enough to use but I still have a lot of wasted time. I had a 350gb drive and had the same problems way before 35gb free space. John Balgenorth On Jul 24, 2015, at 4:15 AM, Robert Brenstein r...@robelko.com wrote: A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
The guidelines I'd read were about 15% IIRC; I believe there used to be a tech note on this, but I can no longer find it at apple.com. Most third-party sites discussing this cite 15%. If OS X required us to never use half of our disk space that would be quite a public controversy, since that's far beyond what any other file system requires and would represent a tremendous waste of storage resources. If you have poor performance on a Mac that has more than 15% free, chances are free space isn't the cause. There may be other issues with the drive (+1 for Disk Warrior), or either the primary or a secondary drive has a power-scaling feature that lets the platter rest when not in use, or corrupted b-tree elements, or any number of other factors. -- Richard Gaskin Fourth World Systems Software Design and Development for the Desktop, Mobile, and the Web ambassa...@fourthworld.comhttp://www.FourthWorld.com John Balgenorth wrote: 10% might work for you but it definitely does not work for me. I have a 1tb drive and 348 gb free space. Most operations run slower than normal. I deleted about 150gb of music to bring it up to 350gb because it ran too slow to use. Now it runs fast enough to use but I still have a lot of wasted time. I had a 350gb drive and had the same problems way before 35gb free space. John Balgenorth On Jul 24, 2015, at 4:15 AM, Robert Brenstein rjb at robelko.com wrote: A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Thanks to everyone for the info. It looks like I need to run some test on it. John Balgenorth On Jul 24, 2015, at 8:19 AM, Richard Gaskin ambassa...@fourthworld.com wrote: The guidelines I'd read were about 15% IIRC; I believe there used to be a tech note on this, but I can no longer find it at apple.com. Most third-party sites discussing this cite 15%. If OS X required us to never use half of our disk space that would be quite a public controversy, since that's far beyond what any other file system requires and would represent a tremendous waste of storage resources. If you have poor performance on a Mac that has more than 15% free, chances are free space isn't the cause. There may be other issues with the drive (+1 for Disk Warrior), or either the primary or a secondary drive has a power-scaling feature that lets the platter rest when not in use, or corrupted b-tree elements, or any number of other factors. -- Richard Gaskin Fourth World Systems Software Design and Development for the Desktop, Mobile, and the Web ambassa...@fourthworld.comhttp://www.FourthWorld.com John Balgenorth wrote: 10% might work for you but it definitely does not work for me. I have a 1tb drive and 348 gb free space. Most operations run slower than normal. I deleted about 150gb of music to bring it up to 350gb because it ran too slow to use. Now it runs fast enough to use but I still have a lot of wasted time. I had a 350gb drive and had the same problems way before 35gb free space. John Balgenorth On Jul 24, 2015, at 4:15 AM, Robert Brenstein rjb at robelko.com wrote: A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
As long as we're on this topic, I can't get over how much faster my POC MacBook Air is than my not-so POC Mac Mini. The former has an ssd, the latter, a regular HD. The former has a 1.4 ghz i5 with 4GB RAM, and the latter a 2.3 ghz i5 with 16 GB RAM. That SSD makes the air scream. On Fri, Jul 24, 2015 at 1:03 PM, Richard Gaskin ambassa...@fourthworld.com wrote: Mark Waddingham wrote: I'd get your hdd checked out asap. This is a good opportunity for all of us to remember that portable large-capacity hard drives are dirt cheap compared to the cost of lost data. A USB 3.0 1TB drive can be picked up at the corner market for about US$60, and a 2TB drive for under US$85. I know everyone here already has multiple redundant daily backups anyway, but there was a time many years ago when I didn't, and I paid for it dearly. Now I have one copy of everything in the cloud, three in my office, and three at home which are rotated through the office so at least one of those offsite backups is never older than 24 hrs. With an rsync script backing up is super-fast and as easy as typing a single word in Terminal. Yes, I also use Time Machine, but relying on any single backup isn't enough; drives fail, software fails, archives corrupt, merde happens. rsync takes only a few minutes to learn and can move large amounts of data with ease anywhere, a perfect compliment to other backup systems for multiple redundancy. Far more than needed? Exactly. Disks are cheap, but time is the rarest commodity in the universe. Last year my MacBook Pro started acting wonky so before I ran any diagnostics the first thing I did was make a full backup. Good thing: the mobo died half an hour later. I just copied the files I needed to another machine and was back to work in minutes. -- Richard Gaskin Fourth World Systems Software Design and Development for the Desktop, Mobile, and the Web ambassa...@fourthworld.comhttp://www.FourthWorld.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode -- On the first day, God created the heavens and the Earth On the second day, God created the oceans. On the third day, God put the animals on hold for a few hours, and did a little diving. And God said, This is good. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Mark Waddingham wrote: I'd get your hdd checked out asap. This is a good opportunity for all of us to remember that portable large-capacity hard drives are dirt cheap compared to the cost of lost data. A USB 3.0 1TB drive can be picked up at the corner market for about US$60, and a 2TB drive for under US$85. I know everyone here already has multiple redundant daily backups anyway, but there was a time many years ago when I didn't, and I paid for it dearly. Now I have one copy of everything in the cloud, three in my office, and three at home which are rotated through the office so at least one of those offsite backups is never older than 24 hrs. With an rsync script backing up is super-fast and as easy as typing a single word in Terminal. Yes, I also use Time Machine, but relying on any single backup isn't enough; drives fail, software fails, archives corrupt, merde happens. rsync takes only a few minutes to learn and can move large amounts of data with ease anywhere, a perfect compliment to other backup systems for multiple redundancy. Far more than needed? Exactly. Disks are cheap, but time is the rarest commodity in the universe. Last year my MacBook Pro started acting wonky so before I ran any diagnostics the first thing I did was make a full backup. Good thing: the mobo died half an hour later. I just copied the files I needed to another machine and was back to work in minutes. -- Richard Gaskin Fourth World Systems Software Design and Development for the Desktop, Mobile, and the Web ambassa...@fourthworld.comhttp://www.FourthWorld.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Hi Mike, The only reference to slow opening of SQLite dbs on the Internet was to do with file permission issues on windows so if you are seeing this on Mac it very much sounds like a hard disk issue. One of my colleagues had an issue recently with the hard drive in a 2011 iMac which didn't show up as a problem in any of the macs internal diagnostics. We replaced the drive and all was well again. It would periodically get exceptionally slow when opening various files. I'd get your hdd checked out asap. Mark. Sent from my iPhone On 23 Jul 2015, at 22:08, Michael Doub miked...@gmail.com wrote: Hey guys, I think that I have a system issue of some type. We had a nasty thunderstorm last night and we lost power. Obviously my Mac was re-booted. When I ran my timing tests this morning. Open was 3 seconds and loading all the data in to a variable was 1. Much better. I just got back from a nice bike ride and tried it again.. some 5 hr later with the mac running the whole time. Open was 93 seconds and loading the data was 23. This tells me that it has nothing to do with livecode or the database, but something is going on with my system that is causing a dramatic slowdowns. -= Mike On 7/23/15 1:46 PM, Peter Haworth wrote: Hi Michael, Those are ridiculously long times! I can't think of anything obvious that would cause them. Mark's question about whether the automatic indexes are being recreated is a possibility but I'm pretty sure that doesn't happen. I couldn't tell from your original post if your table has a primary keydefined, looks like the ID column might be it? Reason I ask is if would be worth trying SELECT primarykeycolumn FROM mytable since selecting all primary key columns is the absolute fastest select statement on a table so I'd be interested to see how long it takes. If you don't have a primary key defined, use rowid instead. That doesn't help with the open time but it might shed some light and what's going on. If you would be willing to share the database with me offline, I'd be happy to see if I can spot anything. I'd also be happy to post the problem on the sqlite developers list but in order to do that, I would need the exact CREATE statement used to create the table plus any other tables in the database. Your sqlitebrowser tool should provide that somewhere. Pete On Wed, Jul 22, 2015 at 4:33 PM Michael Doubmiked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doubmiked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I
Re: LiveCode and SQLite performace
10% might work for you but it definitely does not work for me. I have a 1tb drive and 348 gb free space. Most operations run slower than normal. I deleted about 150gb of music to bring it up to 350gb because it ran too slow to use. Now it runs fast enough to use but I still have a lot of wasted time. I had a 350gb drive and had the same problems way before 35gb free space. John Balgenorth On Jul 24, 2015, at 4:15 AM, Robert Brenstein r...@robelko.com wrote: A rule of thumb for Mac is 10% of drive being free... I find iStat Menus a useful tool for continuous monitoring of vital parameters (just a happy user). RObert On 23.07.2015 at 15:37 Uhr -0700 JB apparently wrote: If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Thanks for letting us know Mike. You might try running Diskutil to check out your hard drive. Or maybe the disk is getting full enough that there's not enough paging space? On Thu, Jul 23, 2015 at 2:12 PM Michael Doub miked...@gmail.com wrote: Hey guys, I think that I have a system issue of some type. We had a nasty thunderstorm last night and we lost power. Obviously my Mac was re-booted. When I ran my timing tests this morning. Open was 3 seconds and loading all the data in to a variable was 1. Much better. I just got back from a nice bike ride and tried it again.. some 5 hr later with the mac running the whole time. Open was 93 seconds and loading the data was 23. This tells me that it has nothing to do with livecode or the database, but something is going on with my system that is causing a dramatic slowdowns. -= Mike On 7/23/15 1:46 PM, Peter Haworth wrote: Hi Michael, Those are ridiculously long times! I can't think of anything obvious that would cause them. Mark's question about whether the automatic indexes are being recreated is a possibility but I'm pretty sure that doesn't happen. I couldn't tell from your original post if your table has a primary keydefined, looks like the ID column might be it? Reason I ask is if would be worth trying SELECT primarykeycolumn FROM mytable since selecting all primary key columns is the absolute fastest select statement on a table so I'd be interested to see how long it takes. If you don't have a primary key defined, use rowid instead. That doesn't help with the open time but it might shed some light and what's going on. If you would be willing to share the database with me offline, I'd be happy to see if I can spot anything. I'd also be happy to post the problem on the sqlite developers list but in order to do that, I would need the exact CREATE statement used to create the table plus any other tables in the database. Your sqlitebrowser tool should provide that somewhere. Pete On Wed, Jul 22, 2015 at 4:33 PM Michael Doubmiked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doubmiked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to
Re: LiveCode and SQLite performace
If I remember correctly Bob Sneidar said that a you need at least 1/2 of your hard drive as free space to run efficiently. So if you have a drive with 500 GB you need 250 GB or more free space on the drive. Anything below that and it normal operations like opening files will be slower. I have used more space than 1/2 and the more I use the slower it gets. Sometimes you can speed things up a little by relaunching the Finder. That can be done using the Force Quit option. If it speeds things up it will only be a temporary fix. John Balgenorth On Jul 23, 2015, at 2:15 PM, Peter Haworth p...@lcsql.com wrote: Thanks for letting us know Mike. You might try running Diskutil to check out your hard drive. Or maybe the disk is getting full enough that there's not enough paging space? On Thu, Jul 23, 2015 at 2:12 PM Michael Doub miked...@gmail.com wrote: Hey guys, I think that I have a system issue of some type. We had a nasty thunderstorm last night and we lost power. Obviously my Mac was re-booted. When I ran my timing tests this morning. Open was 3 seconds and loading all the data in to a variable was 1. Much better. I just got back from a nice bike ride and tried it again.. some 5 hr later with the mac running the whole time. Open was 93 seconds and loading the data was 23. This tells me that it has nothing to do with livecode or the database, but something is going on with my system that is causing a dramatic slowdowns. -= Mike On 7/23/15 1:46 PM, Peter Haworth wrote: Hi Michael, Those are ridiculously long times! I can't think of anything obvious that would cause them. Mark's question about whether the automatic indexes are being recreated is a possibility but I'm pretty sure that doesn't happen. I couldn't tell from your original post if your table has a primary keydefined, looks like the ID column might be it? Reason I ask is if would be worth trying SELECT primarykeycolumn FROM mytable since selecting all primary key columns is the absolute fastest select statement on a table so I'd be interested to see how long it takes. If you don't have a primary key defined, use rowid instead. That doesn't help with the open time but it might shed some light and what's going on. If you would be willing to share the database with me offline, I'd be happy to see if I can spot anything. I'd also be happy to post the problem on the sqlite developers list but in order to do that, I would need the exact CREATE statement used to create the table plus any other tables in the database. Your sqlitebrowser tool should provide that somewhere. Pete On Wed, Jul 22, 2015 at 4:33 PM Michael Doubmiked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doubmiked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is
Re: LiveCode and SQLite performace
Hey guys, I think that I have a system issue of some type. We had a nasty thunderstorm last night and we lost power. Obviously my Mac was re-booted. When I ran my timing tests this morning. Open was 3 seconds and loading all the data in to a variable was 1. Much better. I just got back from a nice bike ride and tried it again.. some 5 hr later with the mac running the whole time. Open was 93 seconds and loading the data was 23. This tells me that it has nothing to do with livecode or the database, but something is going on with my system that is causing a dramatic slowdowns. -= Mike On 7/23/15 1:46 PM, Peter Haworth wrote: Hi Michael, Those are ridiculously long times! I can't think of anything obvious that would cause them. Mark's question about whether the automatic indexes are being recreated is a possibility but I'm pretty sure that doesn't happen. I couldn't tell from your original post if your table has a primary keydefined, looks like the ID column might be it? Reason I ask is if would be worth trying SELECT primarykeycolumn FROM mytable since selecting all primary key columns is the absolute fastest select statement on a table so I'd be interested to see how long it takes. If you don't have a primary key defined, use rowid instead. That doesn't help with the open time but it might shed some light and what's going on. If you would be willing to share the database with me offline, I'd be happy to see if I can spot anything. I'd also be happy to post the problem on the sqlite developers list but in order to do that, I would need the exact CREATE statement used to create the table plus any other tables in the database. Your sqlitebrowser tool should provide that somewhere. Pete On Wed, Jul 22, 2015 at 4:33 PM Michael Doubmiked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doubmiked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doubmiked...@gmail.com wrote: I need some database consulting help. I have created an SQLite
Re: LiveCode and SQLite performace
I use .db without any problems. By the way, why would you want to load the entire data set? I agree that to open any sql database should not take that long. It almost sounds like something is not working as it should and it times out. But on the query side, loading 100’s of 1000’s of records may not be the best way to access the database. And it will be a lot worse if you ever decide to do this remotely. It may be time to consider installing mySQL. It’s not a painful process, and it may be a great deal faster for your purposes. Bob S On Jul 23, 2015, at 10:48 , Peter Haworth p...@lcsql.com wrote: One more thing Michael - do the names of the auto indexes you're seeing begin with sqlite? If not, your sqlitebrowser program is creating them and may be doing something to contribute to the problem. On Wed, Jul 22, 2015 at 4:33 PM Michael Doub miked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doub miked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with
Re: LiveCode and SQLite performace
Hi Michael, Those are ridiculously long times! I can't think of anything obvious that would cause them. Mark's question about whether the automatic indexes are being recreated is a possibility but I'm pretty sure that doesn't happen. I couldn't tell from your original post if your table has a primary keydefined, looks like the ID column might be it? Reason I ask is if would be worth trying SELECT primarykeycolumn FROM mytable since selecting all primary key columns is the absolute fastest select statement on a table so I'd be interested to see how long it takes. If you don't have a primary key defined, use rowid instead. That doesn't help with the open time but it might shed some light and what's going on. If you would be willing to share the database with me offline, I'd be happy to see if I can spot anything. I'd also be happy to post the problem on the sqlite developers list but in order to do that, I would need the exact CREATE statement used to create the table plus any other tables in the database. Your sqlitebrowser tool should provide that somewhere. Pete On Wed, Jul 22, 2015 at 4:33 PM Michael Doub miked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doub miked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I
Re: LiveCode and SQLite performace
Hi Mark, I couldn't say for sure but I'm 99% sure the indexes are not recreated each time the database is opened. On Wed, Jul 22, 2015 at 4:58 PM Mark Wieder mwie...@ahsoftware.net wrote: On 07/22/2015 02:11 PM, Peter Haworth wrote: You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Doh! You're right. I could swear the documentation said you had to compile sqlite with a non-default compiler option to get this to happen, but the reality is that you have to do that to *prevent* it from happening. So do you know if the indices are stored with the database or are they just recreated at each load? That might explain the long wait times on open. -- Mark Wieder ahsoftw...@gmail.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
One more thing Michael - do the names of the auto indexes you're seeing begin with sqlite? If not, your sqlitebrowser program is creating them and may be doing something to contribute to the problem. On Wed, Jul 22, 2015 at 4:33 PM Michael Doub miked...@gmail.com wrote: I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doub miked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your
Re: LiveCode and SQLite performace
Hi Michael, Haven't come across that before. 600k records is a large number but I've seen dbs with millions of records in them so it's not unusual. In any case, almost every performance problem I've seen revolves around either selecting or changing data not simply opening the database. Try executing a PRAGMA integrity_check command after you open the database. It checks out the physical integrity of the database and returns information about any errors it finds. If that doesn't reveal anything, there are a number of PRAGMA statements that affect cache size, buffer sizes, etc which might help but I'm not very familiar with them. If needed, I can post your problem on the sqlite mailing list and see if anyone can help. What platform is this running on? Pete On Tue, Jul 21, 2015 at 4:59 PM Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
If its on an external usb drive, part of the wait is most likely the drive wake up time. To test, move the file to a local drive and try it from there, OR, open the drive in finder first and poke around a little to make sure the drive is live, then try to open the database and see if it solves the issue. On Wed, Jul 22, 2015 at 2:46 PM, Michael Doub miked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doub miked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
I just updated the database and it now has 608436 records. Sorry for the typo. it was 604000. How long to open - 216 seconds. I timed put revDataFromQuery(,,db_id,select * from mydatabase) into tResult it was 26 seconds. 216 to open and 26 to copy all of the data into a variable. This seems odd to me. The database is on the main internal drive (same as OS) in folder with the stack that is accessing the database. The result of the integrity check is ok On 7/22/15 5:11 PM, Peter Haworth wrote: Hi Michael, Out of interest, when you say it takes a long time to open the database, how long do you mean? Also, where is the database located? On your Mac's hard drive, external drive, on a network? I'm a bit confused as to the number of records. Your original email said 600,000+ records, but you mentioned that the ID field (which is defined as unique) has values from 1 to 60400. Maybe a type somewhere? You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Don't worry about indexes for now. They on;y help if you are having problems with how long it takes to execute your select commands and this problem is occurring long before then. PRAGMAs are just another type of sqlite statement. After opening your database, do this: put revDataFromQuery(,,gDBID,PRAGMA integrity_check)) into tResult put tResult gDBID is just the variable with your connection ID in it so replace it with your variable name. The message box will open and you'll see the output from the PRAGMA command. It it begins with revdberr,Livecode detected an error with the statement for some reason. If the PRAGMA does not find any error it returns OK. Anything else, there's corruption in your database. Let me know the result of the integrity check and we'll go from there. On Wed, Jul 22, 2015 at 1:50 PM Michael Doub miked...@gmail.com wrote: Kay and Mark, the database file size is 250Mb. The performance issue that surprised me was the open time. Adds and Queries are as expected. ID's currently range from 1 to 60400 URL is a typical URL that ranges from 50 to 130 characters, average is 80 char. tag is currently not being used so empty local is a mac file path: of the form /Volumes/EXTERNAL/XXX/x.xxx How would I create and index? When I look at the database with sqlitebrowser, it looks like an auto index was created. Can you give me instructions as to how I should have created the database with an index? Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4. My DataBase expertise is very limited. I created the database with sqlitebrowser, then added the data with livecode. I don't know anything about PRAMAs or even how to execute them. If you can provide instructions I will give it a try. -= Mike On 7/21/15 11:48 PM, Kay C Lan wrote: An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to
Re: LiveCode and SQLite performace
On 07/22/2015 02:11 PM, Peter Haworth wrote: You'll see auto indexes for any columns that are defined as UNIQUE, that's how sqlite enforces that constraint. Doh! You're right. I could swear the documentation said you had to compile sqlite with a non-default compiler option to get this to happen, but the reality is that you have to do that to *prevent* it from happening. So do you know if the indices are stored with the database or are they just recreated at each load? That might explain the long wait times on open. -- Mark Wieder ahsoftw...@gmail.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
An example of half a dozen records please. Maybe a couple of very short, a couple of the longest and a couple of average. How big is the db file size - MB not record count? On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub miked...@gmail.com wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Regards, Mike ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: LiveCode and SQLite performace
On 07/21/2015 04:55 PM, Michael Doub wrote: I need some database consulting help. I have created an SQLite database that has 4 fields: ID, URL, tag, local. IS is integer, unique, URL is text not null unique and tag and local are just text. I have 600,000+ records. I use the ID to access each record in most cases or I am filtering trying to find records based on the contents of the tag, URL or local fields. It seems to take forever to open the database. This is normal? I am primarily using the database to make sure that I do not have non-unique URLs, adding performance and selects seem reasonable. I am just surprised about the open performance Does opening the database load a lot of information into memory thus the long delay? Could I have created the database in someway that is not optimal, thus causing the delay? This is not a livecode issue as I am seeing the same delay when I open the database with sqlitebrowser. Any advise or incites in how sqlite actually works would be appreciated. Have you created an index? That could significantly speed things up. If your queries are always using the tag, unique and local fields then you might consider creating a compound index as well. How big is the database file? Are the queries slow or just the initial opening? This might help: https://www.sqlite.org/optoverview.html -- Mark Wieder ahsoftw...@gmail.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
