Re: Stored Procedure performance

" the SP uses birnary division to find the start and end ID's for the requested date" I don't quite understand what it is doing. Do you mean that it uses a binary search routine to find the dates? It assumes that the dates are in the same order as the id. So it treats the table as a sorted array with the id as the index into the array. So it can get the (max(id) + min(id) )/ 2 and it checks this date to see if it is in the range repeat the binary search until you have what you need. Oddly clever but >>>YUCK<<<. It might be a case of knowing enough to be dangerous. You would be better off with a btree index on date and rec_id. The problem with doing a binary search on a btree index is that it needs to traverse the btree to actually find the record id that you need. It isn't actually an offset into a memory location like in an array in memory. Each loop of the binary tree, you need around 30 loops for a 200,000,000 row table, has to read through the index until it finds the record that it needs. By definition each id the binary search will choose will be far away from the last one because a binary search tries to remove as much as possible from the solution set with each iteration. So the likely hood of the page being in memory or close to the page you just read in would be low. So you are on average hitting the index 30 times reading in 1/2*logx(n) pages where x is keys per page and n is the number of rows in the table. So assuming 150 keys per page and your 200 million records you are hitting up to 100 pages and for each index key you still have to also hit the table because the data you need is not in the index. So around 130 pages are accessed for each call to the procedure. If you had an index like (date,rec_id) you would have 1/2*logx(n) x is 75 in this case you are looking at an average of 2 - 3 pages of index read for each compare. The index wouldn't take up that much space and would be much, much faster.