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 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/xxxxxxxxx.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 >>>>> >>>> _______________________________________________ >>>> 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 > _______________________________________________ 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