mahara
asked on
functionality similar to LIMIT x,y
Hi,
I am building a website using asp and sql server and trying to implement a site search.
I want to show 20 search results per page as usual. But my problem is, not having LIMIT x,y at SQL server. I have 2 solutions in mind but can't decide which one is better or is there any other way.
1- Using stored procedures I will move the query results cursor to the starting limit I want and return only 20 rows to the user.
2- Using ado, I will open the ResultSet as forwardonly(suggestions expected here) and I will move to the row I want to start at.
I am wondering which one will be better from performance point of view and if there is a better solution I will be glad to learn about it.
Hoping to hear your suggestions
Thanks
I am building a website using asp and sql server and trying to implement a site search.
I want to show 20 search results per page as usual. But my problem is, not having LIMIT x,y at SQL server. I have 2 solutions in mind but can't decide which one is better or is there any other way.
1- Using stored procedures I will move the query results cursor to the starting limit I want and return only 20 rows to the user.
2- Using ado, I will open the ResultSet as forwardonly(suggestions expected here) and I will move to the row I want to start at.
I am wondering which one will be better from performance point of view and if there is a better solution I will be glad to learn about it.
Hoping to hear your suggestions
Thanks
Either way, I would NOT use a cursort. Make sure you have a primarykey on your table and use the primary key to "page" through the recordset by keeping track of your last primary key.
Brett
Brett
ASKER
Hi Brett
Thanks for your reply but keeping track of a primary key is not a suitable solution for my situation.
For ex :
I have a table having a primary key X and I have a query like this
select X,Y from TableX where a=b order by someranking
then the result set will be something like
ROW X Y
1 1 a
.
20 2897 c
.
.
545 997 b
And I want to show 20 results per page. Then I cant keep track of a primary key.
What is your suggestion for a case like this?
Thanks for your reply but keeping track of a primary key is not a suitable solution for my situation.
For ex :
I have a table having a primary key X and I have a query like this
select X,Y from TableX where a=b order by someranking
then the result set will be something like
ROW X Y
1 1 a
.
20 2897 c
.
.
545 997 b
And I want to show 20 results per page. Then I cant keep track of a primary key.
What is your suggestion for a case like this?
I do understand what you're asking above, what are you using for your front end?
Also, here is a good article using the primary key approach.
http://www.microsoft.com/sql/techinfo/tips/administration/resultset.asp
Also, here is a good article using the primary key approach.
http://www.microsoft.com/sql/techinfo/tips/administration/resultset.asp
ASKER
Thanks for your interest
I am using asp. It is for a web site, and I have 3 days left to implement a site search :) And 6 days to finish the job, hope you understand the situation.
And I need a suitable solution for showing the results.
Thanks again
I am using asp. It is for a web site, and I have 3 days left to implement a site search :) And 6 days to finish the job, hope you understand the situation.
And I need a suitable solution for showing the results.
Thanks again
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If this is a very large recordset, option 1 would probably be better. For small recordsets, you could go with option 2. Either way, I would reccommend using a stored procedure to retrieve the data - stored procedures are far faster than dynamic SQL statements.
Cheers
Night