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 |
|
imp_galo
Starting Member
13 Posts |
 Posted - 2005-12-09 : 11:39:14
|
| I have a application in C++ which I have to know in advance how many records will be returned in my query. So I query to selects:"select count( RecordId ) from Table where Column = 1""select Name, Job from Table where Column = 1"But that way I have to scan the table for the same records twice, which is pretty much slower. Is there any way I can improve it?Thx,Ricardo |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-09 : 11:50:55
|
| You can execute the batch command:Select name, job from table where column=1select @@rowcountThis will return 2 result sets back to your application. The first result set contains your data, and the second will have the rowcount. If you are using dblib commands you can bumpt the result set to get the count and then go back and process the real results. |
 |
|
|
imp_galo
Starting Member
13 Posts |
Posted - 2005-12-09 : 12:30:26
|
| Good suggestion, but I'm using OLE DB. I think is not possible browse between the result sets.Do you have any other suggestion? |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-09 : 13:50:39
|
| Aaaah but you can. Check out: OleDbDataReader.NextResult Method It will advance the data reader to the next result set if there are multiples. I'm not sure how you would get back to the original result set though, I'm not that familiar with the OleDB stuff. You could do a batch that stuffs the results of the query into a temp table, select the @@rowcount which would yield the count as the first result set, then do a select from the #temp table as the second result set so that the first result set has the count, then you get the results, but performance wise you don't want to be doing that for every query.Secondly, unless you are specifically saying SET NOCOUNT ON then the server will return a message (not an error but a message) to your system with the count of the rows. Not sure if OLE DB gives you the ability to capture server messages or not, or if you'd be able to correlate somehow back to your calling object. Just thought I'd throw it out there. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-12-09 : 13:53:26
|
| Of course you could just send the select count() ... and the select fields in a single batch and then use the NextResult method. That way you only send 1 command but get both of the results you are looking for.Why do you need to know the row count ahead of retrieving the results? |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-12-09 : 13:54:38
|
Even if you get a set of rows, the client side object can get the count of the number of rows returned.
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url] |
|
|
|
imp_galo
Starting Member
13 Posts |
Posted - 2005-12-10 : 09:09:33
|
| Yes I can go to the next result set, but not go back to the original one. I wanna know in advance how many records will be returned, because in my application I use a component with virtual paradigm, which means no data is fetched nor displayed ultil really needed. So I save network bandwidht, memory and my application loads faster. But for this I need to inform how many records there are.But acctualy I don't know if its worthy to use virual paradigm, if I have to scan the table twice. The rows affected is only returned from the API in an insert or update query.Using rowcount, included in most app libs, is counterproductive because it has to fetch all data before returning the count.It takes about 120ms to call "select count()". I don't know what would be the cost of creating a temp table, update it with @@rowcount and fetching it later, but it should be slower, I think.I think this topic should be moved to developer forum!Thx! |
|
|
|
|
|
|
|
|
Record Count