Search

elzar · Post by **elzar** » Wed Jan 30, 2008 12:18 pm

i have large number of products in database and each search query takes 3-9 secs in local machines, is there any way we can implement Full Text Search in AC?

AbleMods · Post by **AbleMods** » Wed Jan 30, 2008 6:54 pm

We're going to need a little more information to help, but we're happy to try.

When you say "Search", are you referring to the search pages or through programming code using SQL commands?

My site has over 20,000 items in it. I can search a product by description, SKU or name in less than 2 seconds locally. This is on a dual-core AMD 2.8Ghz Windows XP with SQL 2005 Express on a separate PC.
My live install is even faster as it's a dual processor Xeon 3.06Ghz with 4Gb RAM completely dedicated to AC7.

If you're running IIS, SQL 2005 and AC7 all on the same box locally, performance will be less than stellar without decent RAM and CPU power.

NC Software · Post by **NC Software** » Wed Jan 30, 2008 8:47 pm

elzar wrote:i have large number of products in database and each search query takes 3-9 secs in local machines, is there any way we can implement Full Text Search in AC?

I think this is an issue on your SQL Server or resources available. AC7 is extremely fast. You may want to test against a production SQL Server and see if the results are the same.

elzar · Post by **elzar** » Thu Jan 31, 2008 8:12 am

SolunarServices wrote:We're going to need a little more information to help, but we're happy to try.

When you say "Search", are you referring to the search pages or through programming code using SQL commands?

My site has over 20,000 items in it. I can search a product by description, SKU or name in less than 2 seconds locally. This is on a dual-core AMD 2.8Ghz Windows XP with SQL 2005 Express on a separate PC.
My live install is even faster as it's a dual processor Xeon 3.06Ghz with 4Gb RAM completely dedicated to AC7.

If you're running IIS, SQL 2005 and AC7 all on the same box locally, performance will be less than stellar without decent RAM and CPU power.

yes was referring to search pages and sql commands executed by search pages

I think this is an issue on your SQL Server or resources available. AC7 is extremely fast. You may want to test against a production SQL Server and see if the results are the same.

yes i agree it might be an issue to my sql server or resources available, im running ac7 with 40K items and more to come in local machine with Core2 Duo 1.5, 2gig of RAM, IIS7,MS SQL2005 Vista OS and runs a lot of applications at the same time. maybe a dedicated server for ac7 might give a different result.

but in my testing, some sql commands executed by AC7 takes 3-9 secs when filtered the SearchKeywords field in ac_products table. and i tried to execute a sql command with full search text in filtering SearchKeywords, and took only 0 sec. So in theory if the search page executed a sql command with searchKeywords filtering 5times, using full text search can make a lot of secs faster in my local machine, if thats gives me better performance result in my local machine, i think using full search text will make a difference. correct me if i am wrong

NC Software · Post by **NC Software** » Thu Jan 31, 2008 8:16 am

Is your SQL Server also running on this same machine as AC7 (Web) and other applications or is SQL Server on its own dedicated machine?

elzar · Post by **elzar** » Thu Jan 31, 2008 8:34 am

NC Software wrote:Is your SQL Server also running on this same machine as AC7 (Web) and other applications or is SQL Server on its own dedicated machine?

yes running on the same machine

AbleMods · Post by **AbleMods** » Thu Jan 31, 2008 1:09 pm

Core2 Duo 1.5 would barely run Vista at a decent speed by itself with only 2Gb.

You're way underpowered for that setup and amount of data.

The performance differences you ar seeing have nothing to do with full text search. You're comparing a direct SQL command shot directly against the database versus about 7 layers of various software engines trying to do the same.

Split it up. Get the SQL on a different box. Get rid of Vista. Get a dual-core 2.8Ghz or better machine if you're going to run IIS, host AC7 and develop all on the same box.

A single SATA drive won't handle much load either - they have decent throughput but multi-task that drive and it'll bog down. Run a pair of SATA-150's in RAID-0 or switch to Ultra3-SCSI/SAS drives to keep performance respectable.

NC Software · Post by **NC Software** » Thu Jan 31, 2008 1:23 pm

Vista is fine, the point is that's not what you're going to run your web site or SQL Server on. Your web site should be on a Windows Server 2003 machine specifically for running web sites. Your SQL Server should be on a dedicated SQL box running nothing else and tuned for SQL Server. Regarding RAID, the preferred SQL Server disk configuration is RAID 1 + 0 (striped is fastest and then mirror the striped arrays).

Bottom line, you are NOT going to see any performance in your current configuration, you know that! The problem is clearly not AC, it is your setup I'm afraid. Does it matter? No! You are using Vista strictly for development I assume. If you want to see the performance of AC, simply sign up for a demo store on the AC site or place your configuration on the proper configuration you plan to use in production, you'll see how it screams!

elzar · Post by **elzar** » Thu Jan 31, 2008 1:56 pm

NC Software wrote:Vista is fine, the point is that's not what you're going to run your web site or SQL Server on. Your web site should be on a Windows Server 2003 machine specifically for running web sites. Your SQL Server should be on a dedicated SQL box running nothing else and tuned for SQL Server. Regarding RAID, the preferred SQL Server disk configuration is RAID 1 + 0 (striped is fastest and then mirror the striped arrays).

Bottom line, you are NOT going to see any performance in your current configuration, you know that! The problem is clearly not AC, it is your setup I'm afraid. Does it matter? No! You are using Vista strictly for development I assume. If you want to see the performance of AC, simply sign up for a demo store on the AC site or place your configuration on the proper configuration you plan to use in production, you'll see how it screams!

i agree with you. thanks anyway. i also really dont see it as a problem with AC since im just running it in my local without the the recommended IIS version, OS. i was just trying to enhance my customized AC that even in my kind of setup AC can scream. and that gives me no worries when i deploy.

elzar · Post by **elzar** » Thu Jan 31, 2008 4:43 pm

by the way i uploaded the data from my local to AC Demo store http://ac7.ablecommerce.com/700093478127/

my account was locked out in the middle of the uploading so only around 32K items was uploaded

Post by **Logan Rhodehamel** » Thu Jan 31, 2008 5:08 pm

In early development we were hopeful to include MySQL support like we did in AC55. That didn't materialize, but it was a limiting factor that we tried to plan for.

Making use of full text search is on the table for discussion for 7.1, because yes, if done properly, it could vastly improve the search speed.

Post by **Shopping Cart Admin** » Thu Jan 31, 2008 6:30 pm

Hello Elzar,

by the way i uploaded the data from my local to AC Demo store http://ac7.ablecommerce.com/700093478127/

This is a single server running about 500 copies of AbleCommerce with MS-SQL Server Express. The DB isn't being cached like it could be on a dedicated sql box with 4gb of ram.

elzar · Post by **elzar** » Thu Jan 31, 2008 10:18 pm

Logan Rhodehamel wrote:In early development we were hopeful to include MySQL support like we did in AC55. That didn't materialize, but it was a limiting factor that we tried to plan for.

Making use of full text search is on the table for discussion for 7.1, because yes, if done properly, it could vastly improve the search speed.

thanks logan, thats good to hear

This is a single server running about 500 copies of AbleCommerce with MS-SQL Server Express. The DB isn't being cached like it could be on a dedicated sql box with 4gb of ram.

thats understandable

thanks for the info, im learning a lot from all of your ideas.

AbleCommerce Shopping Cart Software

Search

Search

Re: Search