WOW - The need for (query) speed

Archived Topics
1

“t_stamp” - I know this was covered in previous postings, BUT I just wanted I just wanted to share this significant success story, and how such a small thing has such a significant impact on performance:

I basically “threw” this system together and got the recording up and running.

I’ve performed pretty much ZERO maintenace on the system in the first 12 months, (a tribute to the Factory SQL/FactoryPMI software)

I’ve been collecting data for the last 15 months using FactorySQL. There are about 200 tags in my “Archive” data base (fortunantly, I originally selected “t_stamp” to be saved). and currently over 700,000 records in the table.

We occationally access this data to determine problems with a batch.

i.e. we’ve found a couple cans that have blown up in the warehouse (from 6 months ago) and want to double check cooker temperatures to make sure this isn’t a problem.

I noticed that the FactoryPMI Charts were taking longer and longer to load and print, and on occation, I’d get a timeout error from the MySQL connection.

I finally had some free time to dedicate to the “recorder”.

Using DBManager, I looked at the tables and found that I didn’t have “t_stamp” set up as an index. Hum, I seem to remember reading something about this on this web site.

I selected NEW INDEX in DBManager and told it to make “t_stamp” an index. The new index found a few duplicate “t_stamp” records that I had to manually delete. It finally went through (took about 5 minutes for 700,000 records) and added “t-stamp” as an index in all the records.

WOW, WHAT A DIFFERENCE - I can give the FactoryPMI Chart any date, and within two seconds, I’m rewarded with a chart.

Thanks again for your product and support

John McAvoy
Program Manager
Hirzel Canning Inc
Toledo, Ohio

2

Thanks for your post, and I’m glad that it helped! It certainly can make a significant difference.

I just wanted to mention for the sake of those who stumble onto this thread that FactorySQL (as of version 4.1 or so, I think) will now create an index automatically on the timestamp column of the tables it creates, though it won’t add them to tables that already exist (so if you started with FSQL a while ago and upgraded, it wouldn’t go back and add them).

One more note: Indexes don’t always need to be unique values, so you may have accidentally had the unique flag set or made it into the primary key. No big deal, but I just wanted to mention that in case you start running into problems trying to log multiple records with the same time.

Regards,