| Author |
Topic |
|
divyaram
Posting Yak Master
180 Posts |
 Posted - 2010-04-22 : 23:13:43
|
| Hi all, I am having performance issue with one select .i have to get the total count of records and rownumber and paging is also there .The table will be having more than 1 lakh records.i am using method like thisSELECT ROW_NUMBER() OVER(ORDER BY CASE WHEN @SortTypeColumn =0 AND @SortAscending =0 THEN CONVERT(VARCHAR,ImpactArticle.Article_Title) END DESC, CASE WHEN @SortTypeColumn =0 AND @SortAscending =1 THEN CONVERT(VARCHAR,ImpactArticle.Article_Title) END ASC )AS ROWNUM, COUNT(*) OVER(PARTITION BY NULL) AS TotCountfrom Table_nameSuggest me some other methods, so that performance issue wnt come....Regards,Divya |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 01:59:22
|
| can you illustrate your requirement with some sample data and expected output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
divyaram
Posting Yak Master
180 Posts |
Posted - 2010-04-23 : 02:05:03
|
quote:
Originally posted by visakh16
can you illustrate your requirement with some sample data and expected output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
i am just selecting details of user from india.... result set will be having lakhs of data.... while selecting its having some performance issue... like while select from sql it will take memory my system.... i want to select the second ten thousand records.... how i can get the details.....with out performance issue...some table may contain trillions of records so in future performance issue will happen datz why i have to take an alternative way for geting the detailsRegards,Divya |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 02:08:43
|
| whats the basis for selection? on what column(s) value you decide ones to be selected?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
divyaram
Posting Yak Master
180 Posts |
Posted - 2010-04-23 : 02:31:38
|
quote:
Originally posted by visakh16
whats the basis for selection? on what column(s) value you decide ones to be selected?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
consider the case of mobile subscribers users details if a mobile company want to sent a mail to users from one country.. first dey have to do a selection after dat sending mails in module wise.. like for first 1 and half lakhs subscribers and next second 1.5 lakh subscribes... here while doing selection for second mailing list i have to select only second mailing peoples usersd detilas only....Regards,Divya |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 02:38:06
|
| so do you have a column in table to determine their batch? or do u use country name itself and loop through?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
divyaram
Posting Yak Master
180 Posts |
Posted - 2010-04-23 : 07:56:53
|
| ill be doing sorting using a column from table.... as of now i will taking one country at a time onlyRegards,Divya |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-23 : 08:07:31
|
| do you have an index on country column?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
need a query for rownum and total count