Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2005-08-19 : 14:16:03
|
| I'm looking for a way to instruct SQL server to return rows from a select query as they are found, rather than saving them up and delivering them all at once.I know this isn't a generally-desirable idea, and I understand the performance and round-trip implications, and I understand it would not work with many order-by's, but I've got an app that could really use it.Is this even possible?Thanks-b |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-08-19 : 14:34:01
|
| Hi,The way it works (kinda) is results are accumulated until they have filled a TDS packet(Tabular Data Stream), which are then wrapped in network protocol (usually IP) packets and sent.There really isn't anything you can, or would want to change about that.If you really want to emululate record-by-record processing, you could use a cursor. Even though you would be doing row-at-a-time processing, I believe the whole TDS transmission thing would still happen the same way - I'm pretty sure fetches happen at the client against the received buffer.The next worse alternative is to select the "Top 1" row in a loop where the key of the next "select" is greater than the key of the previous "select".So now that I've given you ammunicion for some TERRIBLE habits, you are sure asking for trouble if you process your data on purpose like that. Don't try to outsmart the engine. It was designed by some pretty smart people with a lot of accumulated experience.It is kind of fun to pick it apart to see how it ticks though. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-19 : 16:22:00
|
| Aiken, we did something like this on a project many years ago, where we were populating a list box with a LOT of values. We would do a SELECT TOP 100... to get the first snapshot and then continue to fill it with additional calls behind the scenes.What's the environment of the app that you're trying to do this? Web? Desktop? .NET or other?There's a setting in Query Analyzer Options to Scroll Results As Received. Not sure if that is just display or actually affects the query processing, but you might run Profiler while you toggle that option switch and see if you get different info.---------------------------EmeraldCityDomains.com |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2005-08-19 : 17:18:05
|
| I should indeed have given more info about what I'm trying to do. I certainly understand that you'd never want to do something like this in a real app. I'm flexible about the environment; I'd prefer classic ASP (yeah, I know), but .NET or even query analyzer would be fine.My purpose here is to provide senior customer support people the ability to perform ad-hoc searches on very large tables, and to be able to cancel the query once they've got the record they want. In this case, it's going to take human eyes and brains to determine what that record is; there will be a basic where clause to isolate the records as much as possible, but then they need to scroll past a human who will be using their wits to process the info.Additionally, a real live human will be using their brains and their perception about how much data is coming back to dynamically tune the where clause; if they sit there for 30 seconds without getting a single hit, the query needs to be adjusted. If they get 1000 hits in 10 seconds, the query also needs to be adjusted. Yes, that could be achieved with a count(*) to tell how many records match, but 1) the tables involved are very very large and that query alone would be brutal and 2) there's not sure to be an index on the particular columns in the where clause, since each one will be different (anything that's being repeatedly asked for, we'll index of course, but there will be one-off queries that don't match indexes)Rereading that, it sounds kind of vague, but I'm afraid that's the best I can do. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-19 : 18:52:39
|
| Well, then I'd say you want to do something like Google does which is do a TOP X search with paging and maybe an estimate of how many total results there are. Or the Microsoft KB route which (at least used to) returns only the first 200 results, paged by 20, and if that didn't get what you wanted, you had to narrow your query. Most people don't want to peruse 200 results anyway.---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
Getting SQL server to return rows as found?