December 26, 2018 at 9:08 am
In My below paging query,taking 25 records from one lac records in just 2 seconds.But when i add TOTROWS column in my query for taking total count of records(1 lac),it is taking more then 1 minute.Is there any method to find total no of records in optimized manner?
Below one is running fast without including TOTROWS column in the outer select query.Declare
@PRODUCTNAME NVARCHAR(200),
@PAGE VARCHAR(100)
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT
DECLARE @TOTROWS INT
DECLARE @XY INT
SET @PAGE=1
SELECT TOP 25 ID,NAME FROM
( SELECT *, TOTROWS=COUNT(ID) OVER() FROM
( SELECT DISTINCT TP.ID AS ID,TP.NAME AS [NAME],ROW_NUMBER() OVER(ORDER BY TP.ID ASC) AS Row
FROM PDF TP
<WHERE CONDITIONS>
UNION ALL
SELECT DISTINCT TP.ID AS ID,TP.NAME AS [NAME],ROW_NUMBER() OVER(ORDER BY TP.ID ASC) AS Row
FROM HTML TP WHERE <conditions>
)a
WHERE ROW>(@PAGE-1)*25
)XY
Below one is running slow after adding TOTROWS column in the outer select query.Declare
@PRODUCTNAME NVARCHAR(200),
@PAGE VARCHAR(100)
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT
DECLARE @TOTROWS INT
DECLARE @XY INT
SET @PAGE=1
SELECT TOP 25 ID,NAME,TOTROWS FROM
( SELECT *, TOTROWS=COUNT(ID) OVER() FROM
( SELECT DISTINCT TP.ID AS ID,TP.NAME AS [NAME],ROW_NUMBER() OVER(ORDER BY TP.ID ASC) AS Row
FROM PDF TP
<WHERE CONDITIONS>
UNION ALL
SELECT DISTINCT TP.ID AS ID,TP.NAME AS [NAME],ROW_NUMBER() OVER(ORDER BY TP.ID ASC) AS Row
FROM HTML TP WHERE <conditions>
) a
WHERE ROW>(@PAGE-1)*25
)XY
any wrong in above query?
December 26, 2018 at 9:25 am
My recommendation is to stop doing paging the old way. You've posted in a 2012 forum. Learn how to use the OFFSET/FETCH methodology of the ORDER BY clause. Please see the section titled "Limiting the number of rows returned" at the following link. And, once you've seen what to look for and 1 example, you'll be able to search for even more examples.
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2018 at 9:53 am
I tired this method also using CTE and OFFSET/Fetch methods for finding total count of records.This method also taking more time.so i tried old method.
CREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT INT
SET @ROWCOUNT = ((@PAGE - 1) * @ROWNUM)
;WITH TempResult AS
(
SELECT DISTINCT ID,NAME FROM PDF
WHERE CONDITIONS
UNION ALL
SELECT DISTINCT ID,NAME FROM HTML
WHERE CONDITIONS
), TempCount AS
(
SELECT
COUNT(ID) AS TotalCount
FROM
TempResult
)
SELECT
ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult, TempCount
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLY
Any other method to find total no of records in optimized manner?
December 26, 2018 at 11:02 am
jkramprakash - Wednesday, December 26, 2018 9:53 AMI tired this method also using CTE and OFFSET/Fetch methods for finding total count of records.This method also taking more time.so i tried old method.
CREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT INTSET @ROWCOUNT = ((@PAGE - 1) * @ROWNUM)
;WITH TempResult AS
(
SELECT DISTINCT ID,NAME FROM PDF
WHERE CONDITIONSUNION ALL
SELECT DISTINCT ID,NAME FROM HTML
WHERE CONDITIONS
), TempCount AS
(
SELECT
COUNT(ID) AS TotalCount
FROM
TempResult
)
SELECT
ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult, TempCount
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLYAny other method to find total no of records in optimized manner?
Why the DISTINCT in your queries? This implies that you may have duplicate data you wish to ignore.
December 26, 2018 at 12:04 pm
SELECT s.row_countThis returns the row count of a table. I have never had it return the wrong count so far and it's faster than
FROM sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
WHERE t.name='Your table name'
AND t.schema_id=Schema_Id('Your table schema')SELECT Count(*) FROM dbo.MyTable;
December 26, 2018 at 12:10 pm
Lynn Pettis - Wednesday, December 26, 2018 11:02 AMjkramprakash - Wednesday, December 26, 2018 9:53 AMI tired this method also using CTE and OFFSET/Fetch methods for finding total count of records.This method also taking more time.so i tried old method.
CREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT INTSET @ROWCOUNT = ((@PAGE - 1) * @ROWNUM)
;WITH TempResult AS
(
SELECT DISTINCT ID,NAME FROM PDF
WHERE CONDITIONSUNION ALL
SELECT DISTINCT ID,NAME FROM HTML
WHERE CONDITIONS
), TempCount AS
(
SELECT
COUNT(ID) AS TotalCount
FROM
TempResult
)
SELECT
ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult, TempCount
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLYAny other method to find total no of records in optimized manner?
Why the DISTINCT in your queries? This implies that you may have duplicate data you wish to ignore.
It makes even less sense, since you're using UNION ALL which will retain duplicates of records that appear in both subsets.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 27, 2018 at 3:38 am
CREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT INT
SET @ROWCOUNT = ((@PAGE - 1) * @ROWNUM)
;WITH TempResult AS
(
SELECT ID,NAME FROM PDF
WHERE CONDITIONS
UNION ALL
SELECT ID,NAME FROM HTML
WHERE CONDITIONS
), TempCount AS
(
SELECT
COUNT(ID) AS TotalCount
FROM
TempResult
)
SELECT
DISTINCT TOP 25 ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult, TempCount
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLY
I use the distinct in my last paging query,but still it is slow.
December 27, 2018 at 7:44 am
jkramprakash - Thursday, December 27, 2018 3:38 AMCREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =25
DECLARE @ROWCOUNT INTSET @ROWCOUNT = ((@PAGE - 1) * @ROWNUM)
;WITH TempResult AS
(
SELECT ID,NAME FROM PDF
WHERE CONDITIONSUNION ALL
SELECT ID,NAME FROM HTML
WHERE CONDITIONS
), TempCount AS
(
SELECT
COUNT(ID) AS TotalCount
FROM
TempResult
)
SELECT
DISTINCT TOP 25 ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult, TempCount
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLYI use the distinct in my last paging query,but still it is slow.
You have a CROSS JOIN built between your two "Temp" tables and that's a part of the slowness. Adding DISTINCT just makes it slower. You have to create a proper join between your tables and NOT use DISTINCT here.
I'll also state that you need to use the paging to find the unique data first and then calculate/return the count from that derivation.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2018 at 9:17 am
These 2 things look wrong to me
This is getting count of all records from CTE regardless of ID
TempCount AS
(
SELECT
COUNT(ID) AS TotalCount
FROM
TempResult
)
this join is what Jeff mentioned. This is a cross join.
SELECT
DISTINCT TOP 25 ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult, TempCount TempResult, TempCount
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLY
If should be something like
from TempResult
join tempCount
on .......
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 27, 2018 at 10:18 am
Now i changed the cross join into simple join but it returns the total count as 24 instead of total count(1 lack).any logic missing in my below join condition?and any wrong in taking total count of all records for paging.
CREATE PROCEDURE [dbo].[PAGING_NEW]
@parameter1 NVARCHAR(200),
@parameter2 NVARCHAR(200),
@PAGE INT,
AS
SET NOCOUNT ON;
DECLARE @ROWNUM INT =24
DECLARE @ROWCOUNT INT
SET @ROWCOUNT = ((@PAGE - 1) * @ROWNUM)
;WITH TempResult AS
(
SELECT ID AS ID,NAME FROM PDF
WHERE CONDITIONS
UNION ALL
SELECT ID AS ID,NAME FROM HTML
WHERE CONDITIONS
), TempCount AS
(
SELECT
COUNT(ID) AS TotalCount,ID AS ID
FROM
TempResult
)
SELECT
TempResult.ID, NAME, TEMPCOUNT.TotalCount
FROM
TempResult
JOIN TempCount
ON TempResult.ID=TempCount.ID
ORDER BY
Tempresult.ID
OFFSET @ROWCOUNT ROWS FETCH NEXT @ROWNUM ROWS ONLY
December 27, 2018 at 12:32 pm
Hi,
Did you try this option?
Select paginationquery.*, summary.*
From
(
Select count(*) as totrows
From
) as summary
Cross join
(
Select *
From
+
) paginationqry
.
This technique wasimplemented in our environments, and , it is proving to be the best available solution to get the total rows along with pagination.
December 27, 2018 at 12:34 pm
Summary query :
Select count(*)
From
December 27, 2018 at 1:19 pm
>> In my below paging query, taking 25 records [sic] from one lac records [sic] in just 2 seconds. <<
Rows are nothing like records. Your mindset is still locked in filesystems so you haven't learned RDBMS. Our model of computing today is based on the idea of tiered architectures. The database tier would produce the data, then pass it on to a presentation layer that would be worried about paging. It's never done in the database layer.
The rest of your codes pretty much a mess too.
SQL is not a procedural language. It is a declarative language and as such, it hates loops, if-then-else statements and local variables. You’re basically writing Basic, COBOL, or Fortran in SQL.
There are no such things as generic “id” or generic “something_name”; they have to be the identifier and the name of something in particular.
Why did you feel you shouldn’t post DDL? That’s been the standard netiquette for over 30 years on SQL forums. Why do you think that PDF and HTML are valid table names? Why do these two tables have the same structure?
You can probably assign something like a page number using the COUNT() OVER() function and some MOD() operators. We can probably show you how to do this if you actually follow the posting rules on this forum.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 30, 2018 at 4:08 am
jcelko212 32090 - Thursday, December 27, 2018 1:19 PM>> In my below paging query, taking 25 records [sic] from one lac records [sic] in just 2 seconds. <<Rows are nothing like records. Your mindset is still locked in filesystems so you haven't learned RDBMS. Our model of computing today is based on the idea of tiered architectures. The database tier would produce the data, then pass it on to a presentation layer that would be worried about paging. It's never done in the database layer.
The rest of your codes pretty much a mess too.
SQL is not a procedural language. It is a declarative language and as such, it hates loops, if-then-else statements and local variables. You’re basically writing Basic, COBOL, or Fortran in SQL.
There are no such things as generic “id†or generic “something_nameâ€; they have to be the identifier and the name of something in particular.
Why did you feel you shouldn’t post DDL? That’s been the standard netiquette for over 30 years on SQL forums. Why do you think that PDF and HTML are valid table names? Why do these two tables have the same structure?
You can probably assign something like a page number using the COUNT() OVER() function and some MOD() operators. We can probably show you how to do this if you actually follow the posting rules on this forum.
DDL Commands
create table pdf_details
(
prodid nvarchar(100),
prodname nvarchar(100),
lang nvarchar(100),
fmt nvarchar(5),
type varchar(2)
constraint pk_pdf Primary Key (proid, lang, fmt)
)
create table html_details
(
prodid nvarchar(100),
prodname nvarchar(100),
lang nvarchar(100),
fmt nvarchar(5),
type varchar(2),
published_date datetime,
created_date datetime
constraint pk_html Primary Key(prodid, lang, fmt)
)
create index ix_pdf_details on pdf_details(prodname)
Sample records
insert into pdf_details
values ('A100', 'X', 'EN', 'HM', 'PDF'),
('A100', 'X', 'JP', 'GM', 'PDF'),
('A100', 'X', 'EN', 'HM', 'PDF'),
('B101', 'Y', 'EN', 'HM', 'PDF');
insert into html_details
values ('B100', 'X', 'EN', 'HM', 'HTML')
('B100', 'X', 'JP', 'GM', 'HTML')
('B100', 'X', 'EN', 'HM', 'HTML')
('C101', 'Y', 'EN', 'GH', 'HTML')
Actually above table contains million of records
My Original Query
SELECT DISTINCT
TP.PRODID AS ID,
TP.PRODNAME AS NAME,
TP.LANG AS LANG,
TP.FMT,
TP.TYPE
FROM
PDF_DETAILS TP
WHERE
TP.PRODID = @PRODID
AND (@PRODUCTNAME IS NULL OR
REPLACE(REPLACE(REPLACE(REPLACE(TP.PRODNAME, '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG')
LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODNAME, '[', '\['), '_', '\_'), '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG') ESCAPE '\'
UNION ALL
SELECT DISTINCT
TP.PRODID AS ID,
TP.PRODNAME AS NAME,
TP.LANG AS LANG,
TP.FMT,
TP.TYPE
FROM
HTML_DETAILS TP
WHERE
TP.PRODID = @PRODID
AND (@PRODUCTNAME IS NULL OR
REPLACE(REPLACE(REPLACE(REPLACE(TP.PRODNAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
I am trying for optimize paging query for my above query with total count of records in a stored procedure. Please provide some optimized paging query to fetch 25 records per page from millions of records.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply