How to Divide query into pieces

[TheSmileyCoder]

Details:
I have a temporary table which does contain a unique ID. I have a query that runs to update a field in the table. That query takes quite a long while to run, and as such my progress indicator appears to be at a standstill.

Therefore I was considering an approach of dividing it into steps, I.e. run a query that updates the first 10% percent, update progress indicator, run the next 10% and so on. I realise this approach will overall be slower, but it will not make the screen appear frozen.

Question: Is there something similar to Select Top 100 records but instead is Select records 101-200?


Alternatively I could do Select top 100 ... WHERE MyField Is not Null but before going that route I was curios to know if the other is possible.

[Seth Schrock]

From what I have found in a quick Google search, you can't select a range from the middle using the SELECT TOP function.

One idea that I did find was to create a field that did a running record count and use a BETWEEN criteria. However, I would think that this would slow down your query even more and isn't that much different then your solution of WHERE MyField Is Not Null.

[Rabbit]

What's the SQL for your update query? Maybe we can optimize that.

As far as dividing it up, if you have a primary key, you could to a top 100 ordering by the primary key and that can serve as your cursor.

[TheSmileyCoder]

As far as dividing it up, if you have a primary key, you could to a top 100 ordering by the primary key and that can serve as your cursor.

I don't really see how you could about doing this? Using it as a cursor?

Mind the primary key has plenty of holes, but of course no duplicats. I.e. an export could be records with primary key 1,2,3,4,5,127,128, 1009,10472.

[Rabbit]

The gaps won't matter. Something like this.

LastRunPK = 0
 
UPDATE table
SET field = value
WHERE PK IN (
   SELECT TOP 100 PK
   FROM table
   WHERE PK > LastRunPK
   ORDER BY PK
)
 
Update progress meter.
 
LastRunPK = 
   SELECT MAX(PK) AS MaxPK
   FROM (
      SELECT TOP 100 PK
      FROM table
      WHERE PK > LastRunPK
      ORDER BY PK
   ) AS t

Lather, rinse, repeat.

[TheSmileyCoder]

It was the getting of the LastRunPK that was eluding me. Thanks for the suggestion.

[Rabbit]

Not a problem. I am still of the mindset that optimizing the update query, if any such optimization can be done, may remove the need to divide the query in this fashion.

[zmbd]

If PK is numeric use the "Between" along with the Primary key.
I have a VBA and an UPDATE - SQL that does just this sort of thing for the exact same reason. I then set the built in status bar using some code I picked up along the way.

If I was at work I'd post you the SQL; however, the basics are built from the following:

SELECT tblpuke1.id, 
   tblpuke1.puke, 
   tblpuke1.puke2
FROM tblpuke1
WHERE (((tblpuke1.id) Between 1 And 3));
 
 

Convert this to an UPDATE Query

UPDATE tblpuke1 
   SET tblpuke1.puke = "NewValue"
WHERE (((tblpuke1.id) 
   Between 1 And 3));
 

Instead of the hard coded Between condition, build the string in VBA.
Several ways to get the update increment for the status bar.
Normally, I open the record set sorted on the PK, move last, get that value. I then use a for next loop, starting my strings there and use an increment against that loop and test against the last value and drop the loop after the high value.
At each cycle of the loop I update the status bar. In V2003 I had to issue a doevents command.

This may be along the same lines as Seth's suggestion.

And that is one of the reasons I use that autonumber as a PK for every table, even when a composite key will do the job... makes things like this easy.

[TheSmileyCoder]

Thank you all for your replies. I think you (Rabbit) are right that I could aim for some improvements into the query design, but I do believe that is a question best suited for its own thread.