Solved

functionality similar to LIMIT x,y

Posted on 2003-11-19
8
347 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:mahara
  • 3
  • 2
8 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 9777546
Performance will depend on the size of the recordset, speed of the network (or web access) and available memory.

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
0
 
LVL 34

Expert Comment

by:arbert
ID: 9779398
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
0
 
LVL 2

Author Comment

by:mahara
ID: 9791228
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?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 34

Expert Comment

by:arbert
ID: 9791926
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
0
 
LVL 2

Author Comment

by:mahara
ID: 9792093
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
0
 
LVL 34

Accepted Solution

by:
arbert earned 50 total points
ID: 9793249
Sor you're using asp and not ASPX?  If you're using ASPX, you can use the builtin paging, if not, check this out:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20185242.html?query=page+recordset&searchType=topic
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
tempdb log keep growing 7 33
SSRS: Why is Visual Studio stripping these properties? 2 22
query linked sql table field from access 4 22
xml files 7 25
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question