Thanks for the input gents. I’m going to have to look into the memory stuff as it’s something I know little about and it’s near bedtime in Scotland.
Warren, the actual programme does use the vector (it’s used to store the RowIDs). The console app mark 1 also did this (results were the same) but I left it out when I noticed it made no difference to the timings to add to the lunacy. Your point about the swapping was something I thought about but can’t understand why this apparently doesn’t happen when I use an array created with new and why no slowdown occurs despite the fact it’s grabbing the same memory. Maybe it’s the array that’s being optimised away but, like I said, the results were the same when I was assigning the results of the sqlite3_steps to the vector. The actual programme only does the resize once, setting the size to the largest one it’s likely to require. If you look back at my original post you’ll see that on one test I did remove all the sqlite code and replace it with code assigning rand() numbers to the array. No difference in the timings of the two passes were noted in this case. Will look in tomorrow. Thanks again. ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Warren Young <war...@etr-usa.com> Sent: Saturday, May 26, 2018 8:54:33 PM To: SQLite mailing list Subject: Re: [sqlite] This is driving me nuts On May 26, 2018, at 9:43 AM, x <tam118...@hotmail.com> wrote: > > I was sizing the vector beforehand and then timing how long it took to store > the RowIDs returned by an sqlite query. You’re not actually modifying the vector, only allocating it and then not using it. A sufficiently smart optimizer might optimize that vector away. > the mistakenly sized vector only used 8 GB? How much memory is actually available to your program when it runs, and how does that change from run to run? You say the machine has 16 GB of RAM, but Windows 10 will be using a huge chunk of that, and it won’t give all of what it isn’t immediately using to a single user program. It is quite possible you’ve driven your computer into swapping with this test, if only to swap out other programs’ pages to disk so that Windows *can* give your single program half of the system RAM. Never forget that your program isn’t the only one running on the machine. Pull up process monitor and put it into the advanced view; you’ll find dozens of other programs trying to run at the same time, if only the OS and its core services. > sqlite3_prepare_v2(DB,"select RowID from Big order by RowID",-1,&stmt,NULL); How representative is this of the real code you intend to write? If you are indeed going to be processing billions of rows in your real DB, you will typically either want to be processing the data as you read it in from the DB, not re-storing it in a std::vector unprocessed, or you will want to put WHERE clauses and such into the query to limit the amount of data you pull to only that which you actually require. If you’re scanning the whole table on every program run, SQL — any dialect — isn’t buying you very much. SQLite is just acting as a data file format for you in that case, offering little more above that than a flat data file. SQLite is giving you a lot of power here; can you use more of it to get some of the benefit of the complexity you’ve bought? > v.resize(Size[i]); As another reply said, this doesn’t necessarily do what you expect it will. std::vector is allowed to do several clever things to avoid unnecessary reallocations, and you’re leaving the door open to them here. If you want to ensure that the vector is completely reallocated every time through, declare it inside the loop to prevent std::vector from reusing previously allocated space. > What's it got to do with sqlite, if anything? Why was sqlite_step slowed down? Why do you believe that is the correct diagnosis? Have you got a fine-grained measurement to prove it, such as from a profiler? Or, have you repeated the measurement in two parts to separate the SQLite operations from the std::vector operations? You’re on the path to Science here, but you’re missing a key tenet: test only one thing at a time. This program of yours tests at least three different things — OS memory manager, std::vector memory allocator strategy, and SQLite — at the same time without providing a provision to isolate the confounds. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
