SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Why my Pagination not too fast..?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 10/09/2013 :  06:04:36  Show Profile  Reply with Quote
Good day!

I just want to know why my current query for pagination not too fast even if few records are chosen.
Let say I need only recordset between 1 to 10 of 5000 records. Why is the query speed is quite still the same even I query record between 1 to 5000 of 5000?


ALTER PROCEDURE sp_Paged_Show_All_Items
	-- Add the parameters for the stored procedure here
	@CurrentPage as bigint,
	@RowsPerPage as bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

with PagedResult AS
(
		
	SELECT     ROW_NUMBER() over (order by productid) as RowNum,
			   p.productcode,
               itemname,
               description,
               packtype,
               itemtype,
               c.categoryname,
               s.suppliername,
               supplierprice,
               srp,
               discount,
               itemlocation,
               expdate,
               service_other_charge_amount_percent,
               vatable,
               COALESCE(BeginInventory,0) AS BeginInventory, 
			   COALESCE(Purchases,0) AS Purchases,
               COALESCE(TransfersIn,0) AS TransfersIn,
               COALESCE(TransfersOut,0) AS TransfersOut,
               COALESCE(SoldItem,0) AS SoldItem,
               COALESCE(BeginInventory,0) + COALESCE(Purchases,0) + COALESCE(TransfersIn,0) - COALESCE(TransfersOut,0) - COALESCE(SoldItem,0) AS RunningOnhand
    FROM tbl_product_list p 
         
    LEFT JOIN  (SELECT prodcode,
				SUM(ISNULL(CASE WHEN memo='beginning' THEN qty ELSE 0 END,0)) AS BeginInventory,
                SUM(ISNULL(CASE WHEN memo='purchases' THEN qty ELSE 0 END,0)) AS Purchases
                FROM PO_detail
                GROUP BY prodcode)i
    ON i.prodcode = p.productcode
         
    LEFT JOIN (SELECT prodcode,
               SUM(CASE WHEN TransferInOutMemo = 'IN' THEN qty END) AS TransfersIn,
               SUM(CASE WHEN TransferInOutMemo = 'OUT' THEN qty END) AS TransfersOut
               FROM tbl_product_transfer_detail 
               GROUP BY prodcode)pts
    ON pts.prodcode= p.ProductCode
         
    LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
               FROM tbl_pos_sales_detail
               GROUP BY productcode)psd
    ON psd.productcode= p.productcode
         
	LEFT JOIN  tbl_supplier s
	ON  s.suppliername=(SELECT s.suppliername
	WHERE s.suppliercode=p.supplierid)
			
	LEFT JOIN  tbl_prod_category c
	ON  c.categoryname=(SELECT c.categoryname
	WHERE c.catgoryid=p.category)
	
	
	GROUP BY productid,p.productcode,itemname,description,packtype,itemtype,c.categoryname,
             s.suppliername,supplierprice,srp,discount,itemlocation,expdate,service_other_charge_amount_percent,vatable,
             BeginInventory,Purchases,TransfersIn,TransfersOut,SoldItem
	--ORDER BY p.itemname ASC
)

	select * 
	from PagedResult
	where RowNum between @CurrentPage and @RowsPerPage
	ORDER BY itemname ASC
	
	END
	GO


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 10/09/2013 06:06:02

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/09/2013 :  07:52:51  Show Profile  Reply with Quote
check execution plan and identify costly steps.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 10/15/2013 :  05:14:10  Show Profile  Reply with Quote
thank you visah16!

sorry for late reps!

I have this table below which returns the highest percentage at around 45% of the execution plan. Total data of this table is around 90,000 rows..

Maybe this part of the query affects the execution plan since it affects the said table.


  LEFT JOIN (SELECT productcode,SUM(qty) AS SoldItem
               FROM tbl_pos_sales_detail
               GROUP BY productcode)psd
    ON psd.productcode= p.productcode


thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Edited by - adbasanta on 10/15/2013 05:15:07
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/16/2013 :  02:55:45  Show Profile  Reply with Quote
Whats the involed step? does the table have required indexes?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000