New topic: How Query Rows Work
<http://forums.realsoftware.com/viewtopic.php?t=47600> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message Jason_Adams Post subject: How Query Rows WorkPosted: Mon Apr 15, 2013 8:32 am Joined: Fri Nov 10, 2006 4:10 pm Posts: 1772 Location: Michigan, USA Please note that I'm personally working with PostgreSQL 9.1. I'm very curious how, exactly, queries are handled by Real Studio. When I perform a query, the database returns multiple records, and Real Studio presents this to me as a RecordSet object. From here I'm able to view one row at a time and "move" between rows. This is what confuses me. What happens when I MoveNext? Are the rows in memory somewhere, or is RS retrieving the query rows from the database one at a time? What's confusing to me is that I don't see this concept of "moving" anywhere else when performing queries. Why aren't I simply returned an array of RecordSets? Appreciate the insight! _________________ Windows 7 Ultimate x64 Windows XP Pro SP3 Ubuntu 11.04 via Virtual Box RS Enterprise 2012r1.1 Programming Tutorials & Free Projects: http://www.JasonTheAdams.com "Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton Top DaveS Post subject: Re: How Query Rows WorkPosted: Mon Apr 15, 2013 9:00 am Joined: Sun Aug 05, 2007 10:46 am Posts: 4753 Location: San Diego, CA I'm not positive..... but based on my experience with other databases in other environments, A RecordSet is not TRULY an "array" of records.... but it is what would be called a "database cursor" in any other context. This "cursor" points to the currently valid record and allows the user to perform certain actions with it.. But that is the only "record" the "recordset" is dealing with (discounting any internal caching mechanisms). When you issue a MOVENEXT, the cursor simply increments to the next qualifying record (or EOF), the same with MOVEPREV (assuming your database supports bi-directional recordsets [SQLITE for example does not]) _________________ Dave Sisemore iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top Jason_Adams Post subject: Re: How Query Rows WorkPosted: Mon Apr 15, 2013 9:12 am Joined: Fri Nov 10, 2006 4:10 pm Posts: 1772 Location: Michigan, USA Thank you, Dave, that is very interesting. I just read up on Cursors in PostgreSQL. I can definitely appreciate this as the way to go as, if queries went straight to memory, returns of millions (or more) rows could bring systems to their knees. What's causing me to think about this is that I have a situation where I'm looping through active records of a View, but I also need a single column from a function. The problem is that if I perform the function on each loop iteration the total time of the loop will be the initial query + (sub-query * iterations). So as the number of rows grow, the loop will consume take more time linearly. Not good. I'm trying to think of a creative way of working the two queries together. Open to suggestions. _________________ Windows 7 Ultimate x64 Windows XP Pro SP3 Ubuntu 11.04 via Virtual Box RS Enterprise 2012r1.1 Programming Tutorials & Free Projects: http://www.JasonTheAdams.com "Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton Top DaveS Post subject: Re: How Query Rows WorkPosted: Mon Apr 15, 2013 9:21 am Joined: Sun Aug 05, 2007 10:46 am Posts: 4753 Location: San Diego, CA Depending on your situation of course. Create an IN-MEMORY database Copy the table (only desired field(s)) into that "CREATE table db.temptable AS Select x,y,z FROM db2.realtable" Then use the in-memory on. Still same mechanism, but in memory will be much faster Just an idea... only way to find out is try it. _________________ Dave Sisemore iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1 Note : I am not interested in any solutions that involve custom Plug-ins of any kind Top Jason_Adams Post subject: Re: How Query Rows WorkPosted: Mon Apr 15, 2013 11:36 am Joined: Fri Nov 10, 2006 4:10 pm Posts: 1772 Location: Michigan, USA Thanks for the idea, Dave. I ended up realizing that I could very effectively re-write the function into multiple, much faster views, which allows me to more effectively query across multiple rows without a linear consequence. It's funny how such different (often better) ideas occur after walking away from something for months. _________________ Windows 7 Ultimate x64 Windows XP Pro SP3 Ubuntu 11.04 via Virtual Box RS Enterprise 2012r1.1 Programming Tutorials & Free Projects: http://www.JasonTheAdams.com "Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
