Paging With no Primary Key - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Paging With no Primary Key

Author

Topic

petalyaa
Starting Member

4 Posts

Posted - 2009-10-02 : 02:31:13

Hi all,
I am currently developing an application to be integrate with client's database. Our client use MSSQL 2000. I only have one major problem that is the paging. I already create SQL for paging :

SELECT TOP x * FROM (SELECT TOP [x*y] * FROM VPCSTINV AS T1 ORDER BY NAME DESC) AS T2 ORDER BY NAME ASC

x = Number of result to show
y = current page

However, this sql will become slower and slower as it reach to the last page. Is there any other method to do this?

One more thing, I don't have permission to create any object in the clients database so, i cannot use stored procedure and i also aware that some of the table have promary key and some of them don't have. Can anyone help me?

Help me!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 08:10:18

do you at least have a set of column(s) which helps you to uniquely identify a row?

petalyaa
Starting Member

4 Posts

Posted - 2009-11-02 : 23:29:15

Yeah, i have it. Already talk to client and they agree to allow me create a stored proc in their db. I already create the stored proc but the result is error. "Invalid Syntax near keyword FROM"

This is the stored Proc.
CREATE PROCEDURE UserPaging(@sqlStr varchar(5000), @orderBy varchar(1000), @startRow int, @endRow int)
AS
DECLARE @Out int, @SQLSTRING varchar(5000)
DECLARE @fieldName varchar(1000)
DECLARE @tableName varchar(1000)
DECLARE @pageSize int

SET @pageSize = @endRow - @startRow
SET @fieldName = SUBSTRING(@sqlStr, 8, PATINDEX('%from%', @sqlStr) - 8)
SET @tableName = SUBSTRING(@sqlStr, PATINDEX('%from%', @sqlStr), 4)

SET NOCOUNT ON

SET @SQLSTRING = N'select '+@fieldName+' from ( SELECT TOP '+ CAST(@pageSize as varchar(10)) + ' ' + @fieldName + ' from (SELECT TOP ' + CAST(@endRow as varchar(10)) +
' ' + @fieldName + ' FROM (SELECT TOP ' + CAST(@endRow as varchar(10)) +
' ' + @fieldName + ' FROM ' +@tableName + ' as T1
ORDER BY ' + @orderBy + ') AS T2 ORDER BY ' + @orderBy + ' DESC ) AS T3) As T4 ORDER BY ' + @orderBy + ' ASC'

SET @SQLSTRING = @fieldName
EXEC(@SQLSTRING)
RETURN
GO

From what i see, im quite sure that the error is most probably came from the substring function that i use. But when i test in query designer "select SUBSTRING('select id, name, email from user', 8, PATINDEX('%from%', 'select id, name, email from user') - 8)", there is no error....

Help me!!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-03 : 09:25:55

at a quick glance, you look to be missing an alias for one (or more) of the subqueries

print out the sql and run that as a standalone item....

petalyaa
Starting Member

4 Posts

Posted - 2009-11-03 : 22:02:34

quote:
Originally posted by AndrewMurphy

at a quick glance, you look to be missing an alias for one (or more) of the subqueries

print out the sql and run that as a standalone item....

Yeah, thanks for your advice. I printed out the sql and integrate the sql inside Java Source code. From here i send sql query to the database to be execute and get the resultset. But then, my first problem happen again. The time for query to execute getting slower and slower. It took about 1.5 seconds to query the last page with 1 million records!!

Was it supposed to be like this because SQL 2000 dont have ROW_NUMBER() function like SQL 2005 and LIMIT 5, 10 as MySQL?

Help me!!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-04 : 09:53:21

Are you expecting far better performance? ... "It took about 1.5 seconds to query the last page with 1 million records" (how long does a plain select * from million_row_table take...that's as good as it could ever get)

And on a realistic basis...who has the time to "page up & down" through a million records? Nobody could make reasonable "business decisions" on evaluating/scanning such data....

You might be better off looking to trim the million records down somewhat...or investing in better hardware.

petalyaa
Starting Member

4 Posts

Posted - 2009-11-04 : 21:24:09

Yeah, thats correct. Who want to view the records if theres a million of it. But, this sql also been use by batching thread in a background. My Java Code will get all records from client's db (could be more than million) and generate CSV file with it. I plan to use batching which means 10000 records will be inserted into one csv and the rest will be in the next csv.

Help me!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources