Selective Query Strategy

[wduty]

Hello,<br><br>This question may be kind of silly but I need to make sure a basic assumption is correct. Suppose I have a table in Access which has seven or eight columns. The column types include text, memo, booleans, and numbers. Now suppose I create a recordset as follows:<br><br>set rec = db.execute(&quot;SELECT * FROM thetable&quot<br><br>this returns all the records (the whole table basically)<br>Now, suppose I query the same table like this instead:<br><br>set rec = db.execute(&quot;SELECT ID FROM thetable)<br><br>this returns only a recordset of the ID fields which are an autonumber field of integers. I am assuming that the second query is less &quot;taxing&quot; to the server because it contains less data (some of the other fields, especially the memo field could contain up to 10000 characters). Is this correct or is there overhead in ADO which to some extent makes the benefit of less sheer data volume illusory?<br><br>The reason I am doing this is because I need to select small, contiguous groups of records from a table which will eventually get rather large. By selecting the ID columns only, I can put those in an array then determine what the low and high IDs are of the records I want and <i>then</i> do the full query of just those records. I already coded it and it works fine but it rests on the assumption that a query of just a single number field involves significantly less server time, memory etc.<br>Any comments on this question greatly appreciated. <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>

 

[palbano]

Dear Will,<br><br>Your assumption is correct regarding moving less data into the ASP process.<br><br>You might also look into utilizing more SQL power to accomplish your goal. For instance you stated:<br><br>&gt; I can put those in an array then determine what the low and high IDs are of the records I want <br><br>I believe Access supports the min() and max() aggregate functions, so you can obtain both numbers in a single query that returns only one row like this<br><br>select min(ID) as minid, max(ID) as maxid from thetable<br><br>Then if you move your database to SQL Server you can work with stored procedures. A stored procedure can run the above query the use the two values to build the where clause of another query and return the second result set only. This has the effect of transporting the smallest amount of data from the database process over to your ASP process.<br><br>Hope this helps<br>-pete

 

[wduty]

Thanks for responding. Very helpful...<br>What I am trying to do is create a arrangement like the one here at tek-tips where a user can see 10 records at a time. Problem is the ID values don't go in perfect numeric order and there is also a category column and a publish boolean column so a query will have only published records from a single category. Visually (just these three columns) look like:<br><br>ID&nbsp;&nbsp;&nbsp;&nbsp;discussion&nbsp;&nbsp;&nbsp;&nbsp;publish<br>----------------------------------<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x<br>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>...<br>45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x<br>49&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x<br>50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>52&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;x<br><br>Selecting out all the ids in category 3 of published records would yield:<br><br>2<br>...<br>49<br>52<br><br>So one way using your advice about the max/min functions is to do as follows: I create a recordset and set the maxrecords property to 10. I then query using the MIN and MAX functions as SELECT subqueries in the WHERE clause. I then grab the last value in the write loop and pass that to the next page to get the next recordset.<br>The key lines (with just one call to ADO) are:<br> <br> rec.maxrecords = 10<br> rec.Open &quot;SELECT * FROM threads WHERE discussion=&quot;&pageid&&quot; AND publish=true AND ID BETWEEN (SELECT MIN(ID) FROM threads WHERE discussion=&quot;&pageid&&quot AND (SELECT MAX(ID) FROM threads) ORDER BY dts desc&quot;, db<br><br>Just tested it and it works beautifully with much less overall code. As for stored procedures, I use stored procedures in Access with no problem although I know they aren't as versatile as in SQL server. I would like to use SQL but that's not really an option for this project at this point.<br><br>Thanks! <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>