Retreieving only the records the parameters indicate

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)

Retreieving only the records the parameters indicate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-24 : 09:07:35

Joseph writes "I'm in web application development and i have a search page where the results can get BIG! as in a 10 thousand records. So my team came up with a form of simulating packet records, so the web page shows a status bar with : "9000 records found (Showing record 1 to 50)" due a given filter, and on the table (that is usin' an XML data island) shows only 50 records.
We keep the packet number on the page (javascript) and request the server the other packets with First, Prev, Next and Last butttons keeping the same search criteria. THe main issue is that I find the method implemented quite expensive. The following is the logic of the storeed procedure.
I need help from you guys to tell me if there is another way to do the same shit without creating temporary tables or opening cursors:

this is a very simplifyed version of the original SP cause that real one joins lke 8 tables and receives a a lot extra parameters for filetrs. but the mainlogic is there as the main issue : the temp table taking the thousand records just to show 50 and the status bar.

-----------------------------------------------------------------
PROCEDURE [dbo].[listing]
@name varchar(12),
@PackedRecord smallint,
@NroRecords smallint,
@TypeSearch smallint
AS
SET NOCOUNT ON
create table #TempTable(
order_no int Identity primary key,
voyage_id varchar(12),
voyage_no varchar(12)
)
declare @SQLSentence varchar(4000)
set @SQLSentence =
insert into #TempTable( voyage_id,
voyage_no)
select job.voyage_id,
job.voyage_no
from job (noLock)
where job.name like @name

if @TypeSearch = 1 --Search
begin
declare @MinRecord smallInt,
@MaxRecord smallInt
set @MinRecord = (@PackedRecord - 1)*@NroRecords + 1
set @MaxRecord = @PackedRecord * @NroRecords
select *
from #TempTable
where order_no between @MinRecord and @MaxRecord
order by order_no
end else
if @TypeSearch = 0 --Count
begin
select count(*) Counter,
@PackedRecord PackedRecord,
@NroRecords MaxRecords
from #TempTable
end
drop table #TempTable
SET NOCOUNT OFF
-----------------------------------------------------------------

I hope you can help me out on this
thnx..."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-24 : 09:25:45

This is a pretty typical method ...

You're best performance improvement will be to set up a full-text index on job.name ...

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources