The search is killing the server. Please help!

[cfdeveloper]

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"><a href="results.cfm?start=#PrevX#&display=#display#">prev</a></td>
</cfoutput>
</cfif>

<cfif nextX LTE searchresults.RecordCount>
<cfoutput>
<td align="right"><a href="results.cfm?start=#nextX#&display=#display#">next</a></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

 

[webmigit]

I had a problem like this except my search's scope was running in the range of 2-3 seconds and there was good reason that it was running so long.. it was a big complex query with regex and everything.. for all it did, it actually ran really fast.

Now what I did is this.. I ran the query the first time.. and then saved the result IDs and then on subsequent pages, read from wherever I saved the IDs tow and run my query based on those IDs..

Get what I mean?

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[SidianConsulting]

I remember writing something like that a year or so ago. I called it PKCache (Primary Key Cache), but I never finished it. It does exactly what you described, webmigit. I never tried it out in production. The most important thing about caching is knowing when and when not to do it. With PKCache, if I had thousands of users and I was caching their results in a session variable, it would be more efficient than caching the full query, but I suspect that with significant load that the memory would fill up sooner or later, anyway.

Caching always seems like a great idea, but I've learned to look at each scenario separately so I can make the best decision.

-Tek

 

[webmigit]

Here's a great idea..

I just thought of this..

Save the IDs to a file.. name it the criteria.. hashed.. like this...

<cffile file="#hash("dbval1=" & val1 & "&dbval2=" & val2 & "&dbval3=" & val3 & "&dbval4=" & val4)#.srch" action="write" output="#valuelist(myQry.pkID)#">

The hashing does two things.. It protects what the user is earching for.. there's no way to tell by looking at the names, and it automatically weeds out nuisance characters that might be in your data.

And then, you could even check for existence of the file before your query is run, And if it is, load the contents to a variable and run your query off of that..

that'd be fast...

Tony

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[cfdeveloper]

Thanks for replying to my thread. I was wondering if you could provide some code to get me started. I was thinking to store the results in a application variable.

<cfif NOT IsDefined('application.searchresults')>

<cfquery name="application.searchresults" datasource="#request.dbName#">
EXEC iSearch blah blah
</cfquery>

<cfoutput query='application.searchresults'>
blah blah
</cfoutput>

Any thoughts?

 

[webmigit]

I detailed this in summary to a guy a while back.. if you can wait til tonight, I think I've still got that email..

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[cfdeveloper]

many thanks! Can you post it?


Cheers
cfcoder

 

[webmigit]

Well, I forgot to get the code..

So, I'll write it.. for this example, I'll write a basic version.. got work to do but, I said I would get you the code.

<cfif parameterexists("url.idsVar")>
  <cfquery datasource="#request.dsn#" name="srch">
    select * from Table
     where IDField in (#url.idsVar#)
  </cfquery>
<cfelse>
  ...full monstrous query that you've got...
</cfif>
<cfparam name="url.idsVar" default="#valuelist(srch.IDField)#">

And then in your prev/next'ing..

<A href="?blah=foo&tek=tips&...[b]&idsVar=#url.idsVar#[/b]">Next</A>

Revision though..

Like I said, store the ids in a file.. and name the file #hash(cgi.query_string)#. And then load from the file..

Then you can pass the hashed query string in the url and actually drop the rest of your query string..

Its not a one-size-fits-all technique.. It makes things a lot faster (at least 40% for me) but it requires tweaking.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[cfdeveloper]

I'll give this a bash, many thanks for your help

 

[cfdeveloper]

Thank you all for your help. I really appreciate it. I decided to go with with "session" scope route. I'm storing the results in a session variable. I'm not caching anything, it just adds more complexity to a already complex solution.

Here is the code for anyone who is interested:

<!--- first time a user performs a search --->
<cfif NOT IsDefined('session.searchresults') and isDefined("url.DELETESESSIONINFO")>

<cfquery name="session.searchresults" datasource="#request.dbName#">

EXEC iSearch blah blah...
</cfquery>

<!--- If the user does another search (new search) I'm deleting the session info --->
<cfelseif IsDefined('session.searchresults') and isDefined("url.DELETE
SESSIONINFO")>


<cflock scope="SESSION" timeout="10" type="EXCLUSIVE">

<cfset temp = structdelete(session,"searchresults")>

</cflock>

<cfquery name="session.searchresults" datasource="#request.dbName#">

EXEC iSearch blah blah...

</cfquery>
</cfif>

<cfoutput group="DatTime" query="session.searchresults" startrow="#start#" maxrows="#display#">


<!--- output the columns --->

</cfouput>

Many thanks again for all your help
regards,
cfcoder