Sounds like something is pegging a core to max. Its been a while but I've seen instances where the mdnsresponder horks, or the spotlight re-index takes a left turn. And as Pete just said, could be a failing drive or lack of paging.
On Thu, Jul 23, 2015 at 3:08 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