May 7, 2012 at 5:01 am
Hello,
I have a table which has huge no. of records (10lac records). I need to select data page wise. Suppose need to fetch 50 records from 700000th no of row. I have done it using Row_Number(), but query runs too slow to fetch data. Is there any alternative to fetch records faster?
My query -
ALTER PROC STP_FILTER_TR_ACTIVITY
(
@JOBNAME NVARCHAR(4000) = NULL,
@PROJECTNUM NVARCHAR(200) = NULL,
@POSSTART INT = 0,
@COUNT INT = 50,
@TOTCOUNT INT OUT
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TEMPTABLE TABLE
(
ROWNUM INT,
ActivityID NVARCHAR(200),
JobName NVARCHAR(4000),
ProjectNumber NVARCHAR(200)
)
INSERT INTO @TEMPTABLE
(ROWNUM ,
ActivityID ,
JobName ,
ProjectNumber)
SELECT ROW_NUMBER() OVER(ORDER BY ActivityID),
ActivityID ,
JobName ,
ProjectNumber
FROM TR_Activity
SELECT
ActivityID ,
JobName ,
ProjectNumber
FROM @TEMPTABLE
WHERE ROWNUM BETWEEN @posStart + 1 and @count
END
May 7, 2012 at 6:37 am
Do you have any indexes on the "TR_Activity" table?
A clustered index on "ActivityId" will be helpfull
Also, you can try creating a clustered index in column "ROWNUM" in table "@TEMPTABLE"
Post the actual execution plan of the query so that we can suggest any more improvements if any..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 7, 2012 at 7:13 am
Kingston Dhasian (5/7/2012)
Do you have any indexes on the "TR_Activity" table?A clustered index on "ActivityId" will be helpfull
Also, you can try creating a clustered index in column "ROWNUM" in table "@TEMPTABLE"
Post the actual execution plan of the query so that we can suggest any more improvements if any..
ActivityID is uniqueidentifier, it is made nonclustered.
Making "ROWNUM" column as clustered index is not helping either. Taking near about 2min to execute the query.
Attached the execution plan.
Please help....
May 7, 2012 at 8:44 am
I see a couple of issues pretty quickly here. First, the query you posted is not the same query as the plan. The one you posted did not have the non-sargable where clause:
WHERE JobName like ISNULL(@JOBNAME, JobName) + '%'
Try changing that to
WHERE JobName like @JOBNAME + '%' OR @JOBNAME IS NULL
Also you said something about 700,000 rows. You probably should look into a temp table instead of a table variable with that much data so you can indexing.
Actually on second thought, why the temp table at all? No need to insert all this into a temp table to just immediately return it and then drop the temp table.
Something like this?
select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY ActivityID),
ActivityID ,
JobName ,
ProjectNumber ,
PhaseNumber ,
DeviceID ,
MediaID ,
MachineName ,
NoOfCopies
FROM TR_Activity
WHERE JobName like @JOBNAME + '%' OR @JOBNAME IS NULL
) x
WHERE x.ROWNUM > @posStart + 1 and x.ROWNUM <= @count
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 7, 2012 at 11:05 am
Sean Lange (5/7/2012)
I see a couple of issues pretty quickly here. First, the query you posted is not the same query as the plan. The one you posted did not have the non-sargable where clause:WHERE JobName like ISNULL(@JOBNAME, JobName) + '%'
Try changing that to
WHERE JobName like @JOBNAME + '%' OR @JOBNAME IS NULL
Also you said something about 700,000 rows. You probably should look into a temp table instead of a table variable with that much data so you can indexing.
Actually on second thought, why the temp table at all? No need to insert all this into a temp table to just immediately return it and then drop the temp table.
Something like this?
select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY ActivityID),
ActivityID ,
JobName ,
ProjectNumber ,
PhaseNumber ,
DeviceID ,
MediaID ,
MachineName ,
NoOfCopies
FROM TR_Activity
WHERE JobName like @JOBNAME + '%' OR @JOBNAME IS NULL
) x
WHERE x.ROWNUM > @posStart + 1 and x.ROWNUM <= @count
I'm thinking something a little more like this.
select * from
(
SELECT ROW_NUMBER() OVER(ORDER BY ActivityID),
ActivityID ,
JobName ,
ProjectNumber ,
PhaseNumber ,
DeviceID ,
MediaID ,
MachineName ,
NoOfCopies
FROM TR_Activity
WHERE JobName like @JOBNAME + '%' OR @JOBNAME IS NULL
) x
WHERE x.ROWNUM >= @posStart + 1 and x.ROWNUM <= @posStart + @count -- You want @count number of rows returned.
May 7, 2012 at 11:46 pm
Hello,
This is my final query --
ALTER PROC STP_FILTER_TR_ACTIVITY
(
@JOBNAME NVARCHAR(4000) = NULL,
@PROJECTNUM NVARCHAR(200) = NULL,
@POSSTART INT = 0,
@COUNT INT = 50,
@TOTCOUNT INT OUT
)
AS
BEGIN
SET NOCOUNT ON;
SELECT
ActivityID ,
JobName ,
ProjectNumber ,
PhaseNumber ,
DeviceID ,
MediaID ,
MachineName ,
NoOfCopies
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ActivityID) AS ROWNUM,
ActivityID ,
JobName ,
ProjectNumber ,
PhaseNumber ,
DeviceID ,
MediaID ,
MachineName ,
NoOfCopies
FROM TR_Activity
WHERE JobName like @JOBNAME + '%' OR @JOBNAME IS NULL
) AS x
WHERE x.ROWNUM > @posStart and x.ROWNUM <= @count
SET @TOTCOUNT = (SELECT COUNT(ActivityID) FROM TR_Activity)
END
This is much faster than previous...
Any other thing can be done for getting total count?
Attached the latest execution plan, please see it...
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply