There are several ways to reduce your SQL usage.

Delete Old Content

Before deleting anything in your store you should first back up the table that contains it.*

Old products
If you've stopped selling a product, delete it. 

Products that aren't selling
To see which products are selling poorly, go to Reports > Reporting. Click Create/Edit Report. Select Order Details report, choose By Product Code and specify a Time Period. Then, click Generate. This will show the products which are selling well and which aren't. Products that have not sold at all will not be on this list. 

You only need to keep your current newsletters, so you can delete any that are over a month old. 

Any Nav Menu Promotions that you no longer use should be deleted. 

CRM Tickets
Back up and delete old closed tickets. 

Reports and saved exports
One-off reports and saved exports that can't be reused can be deleted. 

Old customers
Delete customers who haven't placed an order for a long time. This might be six month or a year, although it depends on your business. If you sell products people buy regularly at fairly long intervals, like holiday supplies, water filters, etc., you may want to keep customers on your records for longer. 

Old abandoned carts
These should be deleted after a few days. The following bulk update will delete any abandoned cart older than two days:

delete from cartidlog where lastmodified < DATEADD(day,DATEDIFF(day,0,GETDATE())-2,0) 

Test settings
Anything set up for test purposes can be deleted. 


Gift certificates
Make sure individual customers do not have two or more gift certificates. If they do, delete all but one and apply the remaining balance to it. 

Use fewer categories
Using a lot of categories, each with a small number of products, generates more SQL usage that one category containing many, so avoid creating categories with just have a few products. Also try to limit the use of subcategories. 

More tips

Trim product description fields
Avoid filling the product description fields with unnecessary text. If a product needs a lot of technical detail, consider linking to an external page instead, making sure you open it in a new window. 

Don't create too many child products
Use options rather than creating child products. 

Free Accessories
If you sell kits/bundles using the Free Accessory(s) field, this creates one order detail record rather than an individual record for each item in the bundle. Note creating extra products for bundles increases SQL usage, which may reduce the benefit of decreased SQL usage from less order details. 


You can use the Inventory > Import/Export page to export all entries in a specific table, but this method does not always include all table fields. The best way to ensure that you're saving the data for all fields is to click the three-dot actions menu and select Export Results on each page of a table's results. You can save up to 500 entries per export by setting the table display to 500 results (click the three-dot actions menu icon, select Customize Columns, select Results Per Page, and click Apply Changes). If you're experienced with SQL, you can also write a custom query to export all fields and entries in a specific table.

Did this answer your question?