Just like people, computers speak their own language. While a computer’s language may look strange to those who aren’t programmers, its technology enables us to search for and retrieve information from more sources on the web than we can count — including your store!
To make sure your store is running at peak performance so visitors can find what they are looking for, you need to make sure the data around your SQL usage is a tight ship!
SQL (formerly known as SEQUEL) is a computer programming language designed for managing data (querying it, inserting it and modifying it). To do this, SQL uses memory within the computer, so it’s important to have enough on hand, to monitor SQL’s usage and reduce any content that may slow it down.
Delete Old Content
One way to speed up your SQL performance is to delete unnecessary content, so it doesn’t have to sort through it all to find what it needs. Before deleting anything in your store you should first back up the table that contains it.*
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.
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.
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)
Or, to remove ALL abandoned carts, use this script instead:
DELETE FROM CartIdLog WHERE LastModified > DATEADD(day,DATEDIFF(day,0,GETDATE())-0,0)
Anything set up for test purposes can be deleted.
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.
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.
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.
⚠️ Heads Up: Back it Up!
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.
When it comes to online shopping, speed is the name of the game. You want your shoppers to be able to find what they’re looking for as fast as possible, so that they don’t go searching elsewhere. That means your computer — and the languages and systems it uses — needs to be a clean, well oiled machine, capable of helping your customers cross the finish line with an order!