Fast data retrieval without indexes on a table with data insertions at every 10 seconds (short time span)

user_14221469 0
Viewed 140

I am fetching data from a table having 20K row from a third-party source where the way of filling the table can't be changed table.

On the third party, the table is filled as following

  1. New data is coming at every 15 seconds approx 7K rows.
  2. At any given time only the last three timestamps will be available rest data will be deleted.
  3. No index on the table is there. Neither it can be requested due to unavoidable reasons and might be slowness in the insert.

I am aware of the following

  1. Row locks and up the hierarchy other locks are being taken while data insert.
  2. The problem persists with select with NO LOCK.
  3. There is no Join with any other table while fetching as we are joining the tables when data is at the local with us in the temp table.
  4. When the data insertion is stopped at the third party the data comes in 100ms to 122ms.
  5. When service is on it takes 3 to 5 seconds.

Any help/suggestion/approach is appreciated in advance.

data-retrieval sql-server performance
1 Answers

The following is a fairly high-end solution. Based on what you have said I believe it would work, but there'd be a lot of detail to work out.

Briefly: table partitions.

  • Set up a partition scheme on this table
  • Based on an article I read recently, this CAN be done with unindexed heaps
  • Data is loaded every 15 seconds? Then the partitions need to be based on those 15 second intervals

For a given dataload (i.e. once per 15 seconds):

  • Create the "next" partition
  • Load the data
  • SWITCH the new partition (new data) into the main table
  • SWITCH the oldest partition out (data for only three time periods present at a time, right?
  • Drop that "retired" partition

While potentially efficient and effective, this would be very messy. The big problem I see is, if they can't add a simple index, I don't see how they could possibly set up table partitioning.

Another similar trick is to set up partitioned views, which essentially is "roll your own partitioning". This would go something like:

  • Have a set of identically structured tables
  • Create a view UNION ALLing the tables
  • On dataload, create a new table, load data into that table, then ALTER VIEW to include that newest table and remove the oldest table.

This could have worse locking/blocking issues than the partitioning solution, though much depends on how heavy your read activity is. And, of course, it is much messier than just adding an index.

user_74910
Related