Slow Keyword search

[Rick F.]

Hi all -
   
  I ran about 100,000 products (1/10th of our total) into OFBiz and generated keywords for them. Now the keyword searches are very slow and can take 30sec to complete. I used the psql COPY command to populate the products table and then clicked the "generate keywords for all items" button. When that finished, I vacuumed/analysed the database.
   
  I'm using Postgresql 8.1 on a 2gz machine running windows xp - only 512 in memory though. We have a larger database on this machine which searches description for these products and we use the TSearch type and it's very fast...
   
  Is there a setting  in the framework that I need to tweak or setup to enable faster keyword searches?
   
  Thanks in advance!
   
  Rick

[David E Jones]

On Feb 9, 2007, at 8:51 AM, Richard Fleming wrote:

> Hi all -
>
>   I ran about 100,000 products (1/10th of our total) into OFBiz and  
> generated keywords for them. Now the keyword searches are very slow  
> and can take 30sec to complete. I used the psql COPY command to  
> populate the products table and then clicked the "generate keywords  
> for all items" button. When that finished, I vacuumed/analysed the  
> database.
>
>   I'm using Postgresql 8.1 on a 2gz machine running windows xp -  
> only 512 in memory though. We have a larger database on this  
> machine which searches description for these products and we use  
> the TSearch type and it's very fast...
>
>   Is there a setting  in the framework that I need to tweak or  
> setup to enable faster keyword searches?

The speed problem here most likely has nothing to do with the  
framework, but rather with the database.

If you have a lot of data in any database you HAVE to maintain and  
optimize the database for use with the application. There is no way  
around that. Some databases have stuff to guess and make things  
faster but that's not always as effective as a human who watches  
common queries and applies a little creativity.

In this case, it may be as simple as doing a "vacuum" in Postgres to  
get the indexes updated. Either way, watch the amount of time the  
database requires for the operations, and have an experienced DBA  
look at it.

-David

[Jacopo Cappellato]

Hi Richard,

I'd suggest to working with indexes in the database.

Jacopo

Richard Fleming wrote:

> Hi all -
>    
>   I ran about 100,000 products (1/10th of our total) into OFBiz and generated keywords for them. Now the keyword searches are very slow and can take 30sec to complete. I used the psql COPY command to populate the products table and then clicked the "generate keywords for all items" button. When that finished, I vacuumed/analysed the database.
>    
>   I'm using Postgresql 8.1 on a 2gz machine running windows xp - only 512 in memory though. We have a larger database on this machine which searches description for these products and we use the TSearch type and it's very fast...
>    
>   Is there a setting  in the framework that I need to tweak or setup to enable faster keyword searches?
>    
>   Thanks in advance!
>    
>   Rick
>

[Rick F.]

Thanks guys,
   
  I'll look into the indexing and let you know what I find.
   
  Rick

Jacopo Cappellato <[hidden email]> wrote:
  Hi Richard,

I'd suggest to working with indexes in the database.

Jacopo

Richard Fleming wrote:

> Hi all -
>
> I ran about 100,000 products (1/10th of our total) into OFBiz and generated keywords for them. Now the keyword searches are very slow and can take 30sec to complete. I used the psql COPY command to populate the products table and then clicked the "generate keywords for all items" button. When that finished, I vacuumed/analysed the database.
>
> I'm using Postgresql 8.1 on a 2gz machine running windows xp - only 512 in memory though. We have a larger database on this machine which searches description for these products and we use the TSearch type and it's very fast...
>
> Is there a setting in the framework that I need to tweak or setup to enable faster keyword searches?
>
> Thanks in advance!
>
> Rick
>

[Rick F.]

Thanks guys,
   
  I'll look into the indexing and let you know what I find.
   
  Rick

Jacopo Cappellato <[hidden email]> wrote:
  Hi Richard,

I'd suggest to working with indexes in the database.

Jacopo

Richard Fleming wrote:

> Hi all -
>
> I ran about 100,000 products (1/10th of our total) into OFBiz and generated keywords for them. Now the keyword searches are very slow and can take 30sec to complete. I used the psql COPY command to populate the products table and then clicked the "generate keywords for all items" button. When that finished, I vacuumed/analysed the database.
>
> I'm using Postgresql 8.1 on a 2gz machine running windows xp - only 512 in memory though. We have a larger database on this machine which searches description for these products and we use the TSearch type and it's very fast...
>
> Is there a setting in the framework that I need to tweak or setup to enable faster keyword searches?
>
> Thanks in advance!
>
> Rick
>