Databases
StackOverflow2013
Postsdbo
POSQL Server Efficiently dropping a group of rows with millions and millions of rows
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
    primarykey
    data
    text
    <p>I recently asked this question: <a href="https://stackoverflow.com/questions/2519183/ms-sql-share-identity-seed-amongst-tables">MS SQL share identity seed amongst tables</a> (Many people wondered why) </p> <p>I have the following layout of a table:</p> <p><strong>Table: Stars</strong><br> starId bigint<br> categoryId bigint<br> starname varchar(200) </p> <p>But my problem is that I have millions and millions of rows. So when I want to delete stars from the table Stars it is too intense on SQL Server.</p> <p>I cannot use built in partitioning for 2005+ because I do not have an enterprise license.</p> <p><strong>When I do delete though, I always delete a whole category Id at a time.</strong></p> <p>I thought of doing a design like this:</p> <p><strong>Table: Star_1</strong><br> starId bigint<br> CategoryId bigint constaint rock=1<br> starname varchar(200) </p> <p><strong>Table: Star_2</strong><br> starId bigint<br> CategoryId bigint constaint rock=2<br> starname varchar(200) </p> <p>In this way I can delete a whole category and hence millions of rows in O(1) by doing a simple drop table.</p> <p>My question is, is it a problem to have hundreds of thousands of tables in your SQL Server? The drop in O(1) is extremely desirable to me. Maybe there's a completely different solution I'm not thinking of?</p> <p><strong>Edit:</strong> </p> <p>Is a star ever modified once it is inserted? No.</p> <p>Do you ever have to query across star categories? I never have to query across star categories. </p> <p>If you are looking for data on a particular star, would you know which table to query? Yes </p> <p>When entering data, how will the application decide which table to put the data into? The insertion of star data is done all at once at the start when the categoryId is created.</p> <p>How many categories will there be? You can assume there will be infinite star categories. Let's say up to 100 star categories per day and up to 30 star categories not needed per day.</p> <p>Truly do you need to delete the whole category or only the star that the data changed for? Yes the whole star category.</p> <p>Have you tried deleting in batches? Yes we do that today, but it is not good enough. od enough.</p> <p>Another technique is mark the record for deletion? There is no need to mark a star as deleted because we know the whole star category is eligible to be deleted.</p> <p>What proportion of them never get used? Typically we keep each star category data for a couple weeks but sometimes need to keep more.</p> <p>When you decide one is useful is that good for ever or might it still need to be deleted later?</p> <p>Not forever, but until a manual request to delete the category is issued. If so what % of the time does that happen? Not that often.</p> <p>What kind of disc arrangement are you using? Single filegroup storage and no partitioning currently. </p> <p>Can you use sql enterprise ? No. There are many people that run this software and they only have sql standard. It is outside of their budget to get ms sql enterprise. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USNet Citizen
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
      1. USNet Citizen
      1. VTBountyStart
    1. COLet me guess, you have "billions and billions" of records.
      singulars
      1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
      1. USRedFilter
    2. COThe tables don't match mine exactly but I think it's a good example that gets my point across :)
      singulars
      1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
      1. USNet Citizen
    3. COPlease explain what "too intense on MS SQL" means. SQL Server can handle very large tables.
      singulars
      1. POSQL Server Efficiently dropping a group of rows with millions and millions of rows
      1. USPaul Williams
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload