SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Any thoughts on this process?


Any thoughts on this process?

Author
Message
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2974 Visits: 3637
This is more of generic programming question. We have a stored procedure which is used on many of our applications built using asp.net. The sp basically first gives total count of pages and then displays the records. I would like to NOT include the count in the sp because it is most expensive ( very bad design, i will tried my best to optimize didnt find much luck). Is there any other way this can be calculated like in asp.net or somewhere else?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16642 Visits: 19557
sqldba_newbie (2/16/2013)

This is more of generic programming question. We have a stored procedure which is used on many of our applications built using asp.net. The sp basically first gives total count of pages and then displays the records. I would like to NOT include the count in the sp because it is most expensive ( very bad design, i will tried my best to optimize didnt find much luck). Is there any other way this can be calculated like in asp.net or somewhere else?


Please post the actual plan for the stored procedure.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15127 Visits: 14396
The sp basically first gives total count of pages and then displays the records.

Please define "total count of pages."

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Steve Thompson
Steve Thompson
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 2102
I'm assuming that total number of pages is a presentation-level metric indicating that at a rate of x records per page, you'll need to link to, and maybe display a label that indicates there are, y total pages. In other words if you show 10 records per page and the data set has 100 rows, you'll have 10 pages.

If so, this can very easily be done in your .NET layer. If you're using ADO objects to extract the data you can easily do something like Datatable.Rows.Count to calculate the total number of rows returned (I believe you can use a similar Count function if you're using Linq). Divide the row count by number of rows per page (adjusting for any remainder, of course) and you should be good to go.

If my assumption of what you meant by page count is off, I apologize.
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2974 Visits: 3637
Steve Thompson-454462 (2/19/2013)
I'm assuming that total number of pages is a presentation-level metric indicating that at a rate of x records per page, you'll need to link to, and maybe display a label that indicates there are, y total pages. In other words if you show 10 records per page and the data set has 100 rows, you'll have 10 pages.

If so, this can very easily be done in your .NET layer. If you're using ADO objects to extract the data you can easily do something like Datatable.Rows.Count to calculate the total number of rows returned (I believe you can use a similar Count function if you're using Linq). Divide the row count by number of rows per page (adjusting for any remainder, of course) and you should be good to go.

If my assumption of what you meant by page count is off, I apologize.


This is EXACTLY what i was looking for.Would you have a article for reference which will define the steps which you have mentioned?
Steve Thompson
Steve Thompson
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 2102
sqldba_newbie (2/19/2013)
Steve Thompson-454462 (2/19/2013)
I'm assuming that total number of pages is a presentation-level metric indicating that at a rate of x records per page, you'll need to link to, and maybe display a label that indicates there are, y total pages. In other words if you show 10 records per page and the data set has 100 rows, you'll have 10 pages.

If so, this can very easily be done in your .NET layer. If you're using ADO objects to extract the data you can easily do something like Datatable.Rows.Count to calculate the total number of rows returned (I believe you can use a similar Count function if you're using Linq). Divide the row count by number of rows per page (adjusting for any remainder, of course) and you should be good to go.

If my assumption of what you meant by page count is off, I apologize.


This is EXACTLY what i was looking for.Would you have a article for reference which will define the steps which you have mentioned?


I'm not sure how your Data Access Layer is constructed. If you use the SQLClient library to talk to your SQL Server back-end, you can create a SQLDataAdapter and use that to fill a DataTable or DataSet (a container that holds multiple DataTables). Once you have a DataTable filled with the results of your SQLCommand, you can get the row count with DataTable.Rows.Count.

Here's a link to the MSDN page on using SQLDataAdapters: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter%28v=vs.71%29.aspx.

Here's the page on System.Data.DataTable: http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

Note that if you're using a firehose-type construct to consume your data (e.g. a DataReader) you would not be able to get the row count without cursoring through the result set.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search