is one of the SQL Aces that can answer you there.
But what you can do -- store the result query in the session scope, then
you can QoQ it to get the results that you want, without having to go to
the database. By the way -- 5000 records sounds like way too much (are
50+ pages with 100 hits each really needed to locate the piece of
information that you are looking for).
--
Hugo Ahlenius
-------------------------------------------------------------
Hugo Ahlenius E-Mail: [EMAIL PROTECTED]
Project Officer Phone: +46 8 230460
UNEP GRID-Arendal Fax: +46 8 230441
Stockholm Office Mobile: +46 733 467111
WWW: http://www.grida.no
-------------------------------------------------------------
| -----Original Message-----
| From: cf coder [mailto:[EMAIL PROTECTED]
| Sent: Thursday, July 29, 2004 12:25
| To: CF-Talk
| Subject: The search is killing the server. Please help!
|
| Hello everybody, I'm working on a logging system. The site
| search as you can imagine is very complicated and a lot depends on it.
|
| I have written a stored procedure for the search to make the
| search quicker with a lot of inner and outer joins (the
| usual). The seach is very quick most of the times, but if the
| search returns say for ex 5000 + records, it takes a while
| for it to display the results in the browser. I have
| impletemented "paging", the search displays 100 records per
| page and then you have the prev and next links to choose
| from. I'll give you an example to hopefully give you a better
| understanding.
|
| If I do a search that returns say 5000 records, the search
| takes on an average 15-20 seconds, displays the first 100
| records. Now if the user clicks on the next button,
| coldfusion runs the entire query once again, but this time
| displays 101 - 200 records. So each time you hit on the
| 'prev' or 'next' buttons, the seach is re-run. I was
| wondering if there was a way round this.
|
| We already know that the search has returned 5000 records and
| the first page is displaying the first 100 records.
| When the user hits on the 'next' button, I want to simply ask
| for the next 100 records and not run the entire query. Is
| this making any sense? I guess if sql has a 'between' clause
| or a 'startrow' and 'endrow' clause, this woud be very easy to do?
|
| Here is my code to call the stored procedure:
|
| <cftry>
|
| <CFSTOREDPROC datasource="#request.db#" PROCEDURE="iSearch"
| debug="YES" returncode="yes"> <CFPROCPARAM TYPE="IN"
| VALUE="#url.logNo#"
| DBVARNAME="@logNo"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCPARAM TYPE="IN"
| VALUE="#url.surname#"
| DBVARNAME="@surname"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCPARAM TYPE="IN"
| VALUE="#url.item#"
| DBVARNAME="@item"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCPARAM TYPE="IN"
| VALUE="#url.location_txt#"
| DBVARNAME="@location_txt"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCPARAM TYPE="IN"
| VALUE="#url.keyword_txt#"
| DBVARNAME="@keyword_txt"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCPARAM TYPE="IN"
| VALUE="#url.sortorder#"
| DBVARNAME="@sortorder"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCPARAM TYPE="IN"
| VALUE="#url.sortby#"
| DBVARNAME="@sortby"
| CFSQLTYPE="cf_sql_char">
|
| <CFPROCRESULT NAME="searchresults" />
| </CFSTOREDPROC>
|
| <cfcatch type="any">
| <tr>
| <td>Search Error! Please try again</td>
| </tr>
| <cfabort>
| </cfcatch>
| </cftry>
|
|
| <cfparam name="start" default="1">
| <cfset display = 100>
| <!---Calculate the "next" value--->
| <cfset nextX = start + display>
| <!---Calculate the "previous" value---> <cfset PrevX = start
| - display> <cfif PrevX LTE 0> <cfset PrevX = 1> </cfif>
|
| <table border="0" cellpadding="0" cellspacing="0"
| width="100%"> <tr> <cfoutput><td>#searchresults.recordcount#
| logs found. You are viewing log(s) #start#<cfif
| searchresults.recordcount lt 100 and
| searchresults.recordcount gt
| 1>-#searchresults.recordcount#<cfelseif
| searchresults.recordcount eq 1><cfelseif nextX gt
| searchresults.recordcount>-#searchresults.recordcount#<cfelse>
| -#nextX#</cfif> of #searchresults.recordcount#</td></cfoutput>
| <cfif start is not 1>
| <cfoutput>
| <td align="right">prev</td>
| </cfoutput>
| </cfif>
|
| <cfif nextX LTE searchresults.RecordCount> <cfoutput> <td
| align="right">next</td> </cfoutput> </cfif> </tr> </table>
|
|
| <cfoutput group="DatTime" query="searchresults"
| startrow="#start#" maxrows="#display#"> <tr>
| <td>#searchresults.lognumber#</td>
| <td class="searchresult">#FirstName# #LastName#</td> </tr> </cfoutput>
|
| As you can see the code above does the paging, returns the
| startrow and maxrows. 100 in each page, but to do this, it
| runs the query again.
|
| Here is the stored procedure:
|
| CREATE PROCEDURE iSearch (
| @logNo varchar(75) = NULL,
| @surname varchar(75) = NULL,
| @item varchar(75) = NULL,
| location_txt varchar(75) = NULL,
| @keyword_txt varchar(75) = NULL,
| @sortorder varchar(75) = NULL,
| @sortby varchar(75) = NULL)
|
| AS
| DECLARE
| @startSQL varchar(3000),
| @searchSQLStatement varchar(8000)
|
| SET @startSQL ='select priority.Description, log.DatTime,
| log.location1, log.comments AS Slug, log_comments.comments AS
| CallCom, log.lognumber, webLog.slug AS CallSlug,
| log.FirstName, log.Lastname, '
| IF (len(@item) > 0) SET @startSQL = @startSQL + ',
| log_Assets.lognumber, log_Assets.log_id '
| SET @startSQL = @startSQL + 'FROM log
| FULL OUTER JOIN webLog on webLog.lognumber = log.lognumber
| FULL OUTER JOIN log_comments ON log_comments.lognumber =
| log.lognumber INNER JOIN priority ON priority.logprioritycode
| = log.logprioritycode '
| IF (len(@item) > 0)
| SET @startSQL = @startSQL + 'FULL OUTER JOIN log_Assets ON
| log_Assets.lognumber = log.lognumber '
| SET @startSQL = @startSQL + ' WHERE 1 = 1 '
|
| SET @searchSQLStatement = ''
|
| IF len(@logNo) > 0
| SET @searchSQLStatement = 'AND log.lognumber LIKE ' +
| char(39) + @logNo + '%' + char(39)
|
| IF len(@surname) > 0
| SET @searchSQLStatement = @searchSQLStatement + 'AND log.ref1
| Like' + char(39) + @surname + '%' + char(39)
|
| IF len(@keyword_txt) > 0
| SET @searchSQLStatement = @searchSQLStatement + 'AND
| (log_comments.comments LIKE' + char(39) + '%' + @keyword_txt
| + '%' + char(39) + 'OR log.comments LIKE' + char(39) + '%' +
| @keyword_txt + '%' + char(39) + 'OR webLog.slug LIKE' +
| char(39) + '%' + @keyword_txt + '%' + char(39) + ')'
|
| SET @searchSQLStatement = @startSQL + @searchSQLStatement + '
| ORDER BY log.'
| SET @searchSQLStatement = @searchSQLStatement + @sortby + ' '
| SET @searchSQLStatement = @searchSQLStatement + @sortorder
|
| /* Execute the SQL */
| EXEC (@searchSQLStatement)
| GO
|
| Please can someone take a look at this and help me with this.
| I'll really apprecaite your help
|
| Best regards,
| cfcoder
|
|
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
