How to Count Total records before Fetching records in sql server

Question

i am working on windows Form application and wanted to perform Paging in DataGridView so i wrote a query for that

Select  DISTINCT  TOP(1000)
        TblStudentBioData.RegNo
    , Coalesce(TblStudentBioData.First_NameUr + SPACE(1) + 
      TblStudentBioData.Middle_NameUr + SPACE(1),TblStudentBioData.First_NameUr)
    + TblStudentBioData.Last_NameUr AS Name
    , TblStudentBioData.Father_NameUr
    , Coalesce(Ay.AcademicYearName,N'+@InCaseNull+') As AcademicYearName
    , Coalesce(Smst.SemName,N'+@InCaseNull+') As SemName
    , Coalesce(Colg.CollegeName,N'+@InCaseNull+') As CollegeName
    , Coalesce(CID.ClassName,N'+@InCaseNull+') As ClassName
    , TblImages.Images
    , TblStudentBioData.Student_ID
    , TblImages.ImageId
    , Ay.AcademicYearId
    , Smst.SemesterId
    , TblClassSchedule.ClassSchId


FROM TblStudentBioData
        INNER JOIN TBLCFGSEX AS sex
                    ON TblStudentBioData.CfgSexId = sex.CfgSexId
        LEFT JOIN TBLMARITALSTATUS ms
                    ON TblStudentBioData.MaritalStatusId = ms.MaritalStatusId
        INNER JOIN TblImages
                    ON TblStudentBioData.ImageId = TblImages.ImageId
        LEFT JOIN TBLBLOODGROUP BG
                    ON TblStudentBioData.BloodID = BG.BloodId

        LEFT JOIN TblStudentDetail
                    ON (TblStudentBioData.Student_ID = TblStudentDetail.Student_ID)
        LEFT JOIN TblStudentSubAss
                    ON TblStudentDetail.StudentDetailID = TblStudentSubAss.StudentDetailID
        LEFT JOIN TblClassSchedule
                    ON TblStudentDetail.CLassSchID = TblClassSchedule.ClassSchID

        LEFT JOIN TblSubAss
                    ON TblSubAss.SubAssId = TblStudentSubAss.SubAssId
        LEFT JOIN TblClass AS CID
                    ON TblClassSchedule.ClassID = CID.ClassID
        LEFT JOIN TBLCOLLEGE Colg
                    ON CID.CollegeId = Colg.CollegeID
        LEFT JOIN TblSemAssigning SA
                    ON TblClassSchedule.SemAssId = SA.SemAssId
        LEFT JOIN TblAcademicYear AY
                    ON SA.AcademicYearId = AY.AcademicYearId
        LEFT JOIN TblSemester Smst
                    ON Smst.SemesterId = SA.SemesterId

This Query Return me more than 28K rows but for paging i want only 1000 rows and total records which is 28K i will be fetching records 1000 wise. Any Idea how can i do this ?

Note:

This query takes more then 20 seconds to execute and i wanted a solution which does not increase executing time.

Have a look at the query plan first, it seems you are missing indexes etc. And Left joins are inefficient, try to rewrite them to inner joins. Selecting 28k rows should not be that hard for sql-server. — Peter, Mar 02 '15 at 16:00
20 seconds to return 28k rows is a bit slow. Try checking for missing index. — Felix Pamittan, Mar 02 '15 at 16:01
possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — Rhys Jones, Mar 02 '15 at 16:02
You have quite huge number of tables in the query. Could the query plan creation time out causing bad performance? — James Z, Mar 02 '15 at 16:02
@wewesthemenace: it takes 20 seconds because i have image coulmn in my select query that is why it is slow — Khurram Ali, Mar 02 '15 at 17:13
You can do the paging with actually ROW_NUMBER(). This is what is commonly used anyways: https://msdn.microsoft.com/en-us/library/ms186734.aspx — Hozikimaru, Mar 02 '15 at 19:50

score -2 · Answer 1 · answered Mar 02 '15 at 16:01

-2

If you only hava 1:1 detail-Tables in your query you could first do a selec count() on your primary key-field of your primary table. that should only last very few milliseconds

answered Mar 02 '15 at 16:01

swe

1,416
16
26

How to Count Total records before Fetching records in sql server

1 Answers1