Solved

Design for SQL Server data solution to handle constant inserts/updates/deletes and also constant searching

Posted on 2016-09-09
4
31 Views
Last Modified: 2016-10-29
Hi,

We are developing a database solution that will run on SQL Server 2014/2016 and wanted to see if any experts could pass comment or suggest a better/different approach.

The solution will involve a collection of tables (we'll call these the 'data' tables) that will be constantly updated by an external multi-threaded application running on a collection of external servers, probably around 20 data tables.  The data in these tables is updated using SQL MERGE and performs well already.  

The part we need to design is regularly (minimum of every minute) moving that data into the search tables which will be constantly searched on from a public website.

Our plan is to create a view/query to perform some basic calculations to create a subset of relevant data from each of the data tables, and use the output of that query to push the data into our search tables every minute.  We plan to use a stored procedure that will run every minute for each data table. Each stored procedure will select the relevant data from the data table and use SQL MERGE to insert/update/delete the records into the search tables.

We then plan to use the (NOLOCK) hint when querying the search tables in case any locking slows down the searches.  We'll probably create a view that UNIONS all the search tables to enable us to search across them.

This is our basic plan, along with optimizing the indexes to try and find a balance between optimizing the searches without degrading the time it takes to perform each merge.

Key points to consider are...

* There are around 20 'data' tables
* A subset of the data in each 'data' table is (after some basic calculations are used select the relevant subset) merged into the search tables
* Queries on search tables need to be as fast as possible with no delays (due to locking etc)
* Queries on search tables CAN allow for dirty reads
* Total amount of records in the search tables will be around 500,000 to 1m

Think that's the basics - please ask if you need any more information, but would love to hear any potential issues you see with our plan, or a better suggestion on how to approach this to put the most efficient solution in place.

Thank you :)
0
Comment
Question by:cp 30
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 41791307
<knee-jerk one minute short attention span answer>

Sounds like SP's that fire every x minutes to drop a table (20 tables..), create the table per your specs, then slap a COLUMNSTORE index on it to optimize SELECT statements on it.

You'll want to time this as it might not be conducive to an every-minute update scenario, and if it's not then we're talking a MERGE statement without the columnstore index.
0
 

Author Comment

by:cp 30
ID: 41792534
Hi Jim,

Thanks for the suggestion.  Do you know how the  queries on the search tables be handled if the tables were in the process of being dropped and recreated?

Thanks
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 41795059
Use a separate db for the search data.  Set RCSI on for that db.

You would only need the data that was modified since the previous minute.  Add a column with mod datetime and index it appropriately.  Then simply SELECT from the tables where the mod datetime > the time of the previous pull.

The data in these tables is updated using SQL MERGE and performs well already.  
Interesting ... typically MERGE does not perform as well as a separate UPDATE and INSERT (so-called "UPSERT").
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Login 17 38
SQL server is using more virtual memory. 5 68
Getting same value for every field in SQL 2 27
SQL bit field not working as expected 3 20
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now