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

Kinderly Wade
Kinderly Wade used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DarrenSenior Software Engineer

Commented:
Hi,

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.

Thanks,

Darren
Fractional CTO
Distinguished Expert 2018
Commented:
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 https://codex.wordpress.org/Transients_API 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 http://sqlmap.org 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.
Kinderly Wadeprogrammer

Author

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
Ensure you’re charging the right price for your IT

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

David FavorFractional CTO
Distinguished Expert 2018

Commented:
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 Wadeprogrammer

Author

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 FavorFractional CTO
Distinguished Expert 2018

Commented:
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 Engineer

Commented:
MySQL buffer speed, which uses page based, shared memory, so runs almost as fast as memory mapped directly into your program

Hi,

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.

Thanks,

Darren
Kinderly Wadeprogrammer

Author

Commented:
Thanks David!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial