Database Query Search Time

[green-watch.org]

Hey Guys,

I am having a bit of trouble with SOLR. Here is my code:

Code:

<cfsetting requestTimeOut = "6000">

<cfcollection action="list" name="COLLECTION_LIST" engine="solr">

<cfquery name="PRODUCT_COLLECTION_CHECK" dbtype="query">
SELECT * FROM COLLECTION_LIST  
WHERE COLLECTION_LIST.NAME = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="product_collection">
</cfquery>  

<cfif PRODUCT_COLLECTION_CHECK.RecordCount NEQ 0>

  <p>Collection Exists</p>
  
  <cfquery datasource="#dsnName#" name="PRODUCT_LIST">
  SELECT TOP 100 PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_PRIORITY_ID, PRODUCT.BUSINESS_ID, PRODUCT.PRODUCT_NAME,
  PRODUCT.PRODUCT_PRICE, PRODUCT.PRODUCT_DESCRIPTION, PRODUCT.PRODUCT_PHOTO, PRODUCT.PRODUCT_KEYWORD1,
  PRODUCT.PRODUCT_KEYWORD2, PRODUCT.PRODUCT_KEYWORD3, PRODUCT.PRODUCT_KEYWORD4, PRODUCT.PRODUCT_GOOGLE_IMAGE_URL,
  PRODUCT.PRODUCT_VALIDATED
  FROM PRODUCT
  WHERE PRODUCT.PRODUCT_INDEXED = <cfqueryparam cfsqltype="CF_SQL_NUMERIC" value="0">
  </cfquery>
  
  <cfset myColumns = 'PRODUCT_NAME,PRODUCT_DESCRIPTION,PRODUCT_KEYWORD1,PRODUCT_KEYWORD2,PRODUCT_KEYW​ORD3,PRODUCT_KEYWORD4'>
  
  <cfindex action="update" collection="product_collection" query="PRODUCT_LIST" type="custom" key="PRODUCT_ID"
  title="PRODUCT_NAME" body="#myColumns#" custom1="BUSINESS_ID" custom2="PRODUCT_PRICE" custom3="PRODUCT_PRIORITY_ID"
  custom4="PRODUCT_VALIDATED">

  <cfsearch collection="product_collection" criteria="custom2: 0 TO 350" name="results" status="r" suggestions="always"
  contextPassages="0">

  <cfdump var="#results#">

  <cfsearch collection="product_collection" criteria="" name="results" status="r" suggestions="always" contextPassages="0">

  <cfdump var="#results#">

<cfelse>

  <p>Collection Does Not Exist</p>

</cfif>

Here is my current testing page for it:
http://www.green-watch.org/test.cfm

How do I get it to return products where the product price is between 0 and 350 dollars? I am trying this in the code: criteria="custom2: 0 TO 350" - but it is not working.

Thanks in advance for any suggestions.

Sincerely,
Travis Walters

[sajal]

I dont know anything abt cold fusion, so cant be much of a help. but in your code above I see SQL queries. are you sure you are running them by solr?

If you can PM me the url of the solr instalation, i can take a look and figure out the paramaters needed for this...

[green-watch.org]

Hey Sajal,

I am positive that the "product_collection" is a SOLR collection. I verified this by looking in the coldfusion administrator. I am not sure what URL you would need? Our hosting company installed coldfusion 9 on the server and SOLR comes with it.

The "PRODUCT_COLLECTION_CHECK" cfquery at the beginning of the code checks to make sure a SOLR collection exists. The "PRODUCT_LIST" cfquery grabs the top 100 products in the database to populate the SOLR index through cfindex. Finally, the cfsearch call searches that index via SOLR.

I know I have to be close to having this right and it is probably something really simple that is needed to get it working correctly.

Sincerely,
Travis Walters

[sajal]

hmm... SOLR is usually an independent web service and applications connect to it using REST...

check out the docs for filter query : http://wiki.apache.org/solr/CommonQueryParameters#fq

Try changing

Code:

<cfsearch collection="product_collection" criteria="custom2: 0 TO 350" name="results" status="r" suggestions="always"
  contextPassages="0">

to

Code:

<cfsearch collection="product_collection" criteria="custom2:[0 TO 350]" name="results" status="r" suggestions="always"
  contextPassages="0">

[green-watch.org]

Hey There,

I tried changing that value to what you said, but it is returning 69 out of the 100 results. It still has results over 350 dollars though as you can see here:

http://www.green-watch.org/test.cfm

Any other suggestions are greatly appreciated. Thanks in advance

Sincerely,
Travis Walters

[sajal]

Ask the hosting company on how to access SOLR directly....

It should look something like this...


Then play in it... see the records in it.... maybe the values ur setting are being stored as string?

I think this is a question for a programing site like http://stackoverflow.com/ or similar...

[green-watch.org]

Hey Sajal,

I ended up finding the location of that utility on my own through the coldfusion administrator.

I found some code on the internet as well.

Code:

<cfif isdefined("url.create")>
<cfcollection action="create" categories="yes" collection="sitecontent2" engine="solr" path="#expandpath('./')#">
Collection created OK.
</cfif>

<cfif isdefined("url.index")>
<cfset mq = queryNew("id,camera,cameratype,price","integer,varchar,varchar,varchar")>
<cfset tmp = queryAddRow(mq,5)>

<cfset tmp = querySetCell(mq,"id",1,1)>
<cfset tmp = querySetCell(mq,"id",2,2)>
<cfset tmp = querySetCell(mq,"id",3,3)>
<cfset tmp = querySetCell(mq,"id",4,4)>
<cfset tmp = querySetCell(mq,"id",5,5)>

<cfset tmp = querySetCell(mq,"camera","Olympus 500",1)>
<cfset tmp = querySetCell(mq,"camera","Canon D30",2)>
<cfset tmp = querySetCell(mq,"camera","Konica Minolta Dimage A2",3)>
<cfset tmp = querySetCell(mq,"camera","Sony Cybershot 300X",4)>
<cfset tmp = querySetCell(mq,"camera","Sony Cybershot 50D2",5)>

<cfset tmp = querySetCell(mq,"price","1499",1)>
<cfset tmp = querySetCell(mq,"price","1500",2)>
<cfset tmp = querySetCell(mq,"price","700",3)>
<cfset tmp = querySetCell(mq,"price","200",4)>
<cfset tmp = querySetCell(mq,"price","1400",5)>

<cfset tmp = querySetCell(mq,"cameratype","Professional",1)>
<cfset tmp = querySetCell(mq,"cameratype","Pro",2)>
<cfset tmp = querySetCell(mq,"cameratype","SLR",3)>
<cfset tmp = querySetCell(mq,"cameratype","SLR",4)>
<cfset tmp = querySetCell(mq,"cameratype","Pocket",5)>

Query to be indexed:
<cfdump var="#mq#">

Indexing...
<cfindex action="update" collection="sitecontent2" body="camera,cameratype,price" key="id" type="custom" query="mq" title="camera" custom1="price" custom2="cameratype">
OK<BR>
</cfif>

<cfif isdefined("url.search")>
    <cfsearch collection="sitecontent2" name="mysearch1" criteria="custom1:700 TO 1500">
    Search 1:
    <cfdump var="#mysearch1#">
    <cfsearch collection="sitecontent2" name="mysearch2" criteria="custom2:pro">
    Search 2:
    <cfdump var="#mysearch2#">
    <cfsearch collection="sitecontent2" name="mysearch3" criteria="custom1:700 TO 1500 AND custom2:pro">
    Search 3:
    <cfdump var="#mysearch3#">
</cfif>

You can see how the "key" is set to an "integer" here. However, under that SOLR utility, it says "Field Type: STRING". For custom1, it says the field type is TEXT. I am not sure if there is any way to change a field to have an integer type?

I found it strange how the code example's price column was set to varchar instead of integer.

I noticed "custom1:700 TO 1500" will return keys 3 and 2. However, "custom1:700 TO 1499" will return keys 3 and 1. Obviously the 1499 key should be returned when the range is 700 to 1500 but it's not. Why is this?

Thanks for anymore information. I would really love to figure this out so I can finish my product section.

By the way, I am willing to pay for assistance with this:
http://www.vworker.com/RentACoder/misc/B...Id=1474266

Sincerely,
Travis Walters

[green-watch.org]

Hey There,

Many somebody will know these answers to these questions:

1. When I am indexing a collection of data for SOLR, for the product price field, should I input price ranges instead of the actual product price? For example, if a product costs 393.54, the product price could be [100_400][100_500][100_600][200_400][200_500] etc. Then when I use a product search I could look for the range within that list of strings. The problem I see with this is there are so many different combinations of price ranges that the collection may get bloated?

2. Look at this solr query string:

http://localhost:8983/solr/sitecontent2/select/?q=custom1:700%20TO%201500&version=2.2&start=0&rows=10&indent=on

If I have a product priced 1499, why is that product not returned in the results?
http://www.green-watch.org/test2.cfm?index=yes (thats whats in this index)

Thanks for any suggestions.

Sincerely,
Travis Walters

[green-watch.org]

Hey Guys,

I figured out how to do price ranges.

After I finish up the script, I will write a little tutorial on here to help other people out that come across this thread.

Sincerely,
Travis Walters

[pmeenan]

Awesome. How does the end result look for performance compared to when you started the thread?

I'm in the process of setting up a performance knowledgebase here: http://www.webperformancecentral.com/wiki/Main_Page so if you want to document it there and link to it from here that would be great (or I can clone it over there if you'd like). There are a lot of gems of knowledge that are starting to turn up as people work through issues and I thought it would be good to have an actual home for them.

Thanks,

-Pat