Better and more efficient way to retrieve data when user requests for information

Dear experts!

I am just wondering if there is a way for me to convert information from relational database such as MSSQL, MySQL etc... into a NO SQL or simply something like memcache in a key -value pair? This is what I want to achieve:

1. instead of having user hitting the SQL DB with requests when they want to get product information etc....
2. store the information into NO SQL or memcache (for a faster and dynamic data retrieval)
3. block the user from hacking SQL DB with sql injection
4. allow a faster update with the data by updating product information in NO SQL or memcache from the SQL DB. ****(I wish to know some good suggestions if I can have a real time update) **** before I have 2 SQL DB and using bash script to do the product information sync like every 30mins or so

Any good idea is appreciated. Thanks!
Kinderly WadeprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DarrenSenior Software EngineerCommented:

What technologies are you currently using?

Whats the web server, what are the services written in?

I'm also a little confused as to why MySQL or SQL Server are not fast enough for you. If it's simple product information then it should not be a problem to retrieve the data.

If it's cached then it has to be updated in the cache and so if you have product information like quantity and someone purchases something then you want the next person to know that the amount left has reduced. If you use caching then the amount will not update until it is refreshed in the cache.

A little more info would be good.


David FavorLinux/LXD/WordPress/Hosting SavantCommented:
You're questions...

1. instead of having user hitting the SQL DB with requests when they want to get product information etc....
2. store the information into NO SQL or memcache (for a faster and dynamic data retrieval)

You can + keep in mind, i/o over TCP or domain sockets (whatever MS equivalent might be) will be far slower than memory storage.

Pull down a copy of WordPress + peruse the code for a super elegant way to do this.

Keep in mind, you have to store data somewhere, so you'll never save memory storing data in a separate process + you will always slow down your entire code, because now your code becomes i/o bound to your external backing store process, rather than running at RAM memory speed.

3. block the user from hacking SQL DB with sql injection

SQL injections relate to how SQL is written, rather than where data is stored. Maybe you're thinking less SQL will reduce injections.

Better to just write stellar code + scan your code with so you know your code is free of injections... well... you'll be fairly sure...

4. allow a faster update with the data by updating product information in NO SQL or memcache from the SQL DB.

Moving data out of memory + accessing over a TCP or domain socket will always be far slower than accessing data out of local memory (like a hash) in your own code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kinderly WadeprogrammerAuthor Commented:
Hi Experts!

Sorry that I didn't provide enough information.

I was thinking of storing all the product information in the memory (likely a key value pair). I was doing a test as in storing all product information in the memory or using a query to pull all the products information, my result is that memory is faster than pulling the data each time from the database. (of course if just pulling a single or just a few product information will not be an issue). Say if I have over 10K of products and I need to pull like 20 columns per sku then it will make a difference (I may need to pull more 10K of rows later).

If I stored the product information in the memory  (there wasn't much update of the product per day unless if I wanted to process sales orders or similar type of transaction) there will not be much update to the product information even if there was an update it will be the selected few products which I can simply just update those products only.

So this why I was thinking of just store the information into the memory instead of using the query to query for product information each time. I just followed basic rule as in memory will out perform data in database, files, etc.. because I may need to trigger the I/O instead of simply pulling the data out of memory. Of course if I shutdown the server the information would be gone (I assumed this will be the same if I shutdown or restart the sql server the information will not be available as well) and I would need to push the product information from DB into memory just once (when I started the server) and information will stay in the memory (these server do not need to be shutdown often unless need to run some sort of maintenance).

One of the question that I am trying to overcome is this:

Say if I am using MS SQL or MYSQL as my database. I simply pushed the product information into the memory (using memcache as example). If company's employee needs to update a few product (not the full product list). What will be the best for me to update the product information in the memcache from database?

METHOD 1: using sql procedural/function
Can I achieve via the sql procedural/function that allows me to do an update or insert if new product when I set the trigger as in calling the procedural or function call when the product has been updated?

METHOD 2: bash script run by a cron job
Can I simply write a bash script code to update the memcache at a defined time interval (like every 30mins etc....) to update the product in memcache?

Or none of the two from above is good and there is a better method out there that can achieve this? Thanks!

2. I can run a bash script to
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
Storing in memory is different than having another process manage your data, like memcached, because accessing your data in a separate process  will always be slower, because you must access memcached over a socket connection... so your data access speed via external process will never come close to memory speed... or MySQL buffer speed, which uses page based, shared memory, so runs almost as fast as memory mapped directly into your program.

If speed is a consideration, you'll immediately drop the idea of an external data server.

Also, really think through this.

You're trying to... come up with better caching than MySQL itself, which caches all data in memory buffers anyway.

Better to just run your code + retune with mysqltuner every few days, will no diagnostics emit.

If you try + write your own cache... there are many factors to consider, such as how + when to expire data, so you don't just swap all your processes out of memory... because you're storing massive amounts of memory data.

MySQL is a very smart set of code. Highly unlikely you can come up with a better scheme than the MySQL developers over decades of work.

Not impossible, just unlikely.
Kinderly WadeprogrammerAuthor Commented:
I see now. Can I use the mysql database and define my table as engine memory which will have a better performance as compared to memache? If so is there a similar mechanism in MS SQL as well with storing the data in engine memory like the mysql? Thanks
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
You can + keep in mind, the memory storage engine data is ephemeral... it disappears each time MySQL restarts.

So you'll have to copy all your data in/out, based on your situation.

I should also mention, if you're concerned about performance, you'll never install MySQL. Install MariaDB from the MariaDB repositories.

Also, run Ubuntu Bionic as your OS, because Bionic ships with Kernel-4.15 which provides dramatic performance improvements over any of the pre-4.0 Kernels.

Also... sigh... so many things...

In /etc/fstab add the mount options of noatime,dioread_nolock which will...

1) noatime - Dramatically increase speed writing log files, database + all system file access.

2) dioread_nolock - Fixes a horrible bug that's been around for years for any code using O_DIRECT which is what most MySQL/MariaDB installs use.
DarrenSenior Software EngineerCommented:
MySQL buffer speed, which uses page based, shared memory, so runs almost as fast as memory mapped directly into your program


I didn't know abut the above which is a really good feature.

I was watching this question to see if there was anything that I could learn (which I did).

Apart from using some other form of storage such as NoSQL or memory  as you have already suggested I have no more info to add to this.


Kinderly WadeprogrammerAuthor Commented:
Thanks David!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.