Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Have you checked the execution plan once the time goes up? The plan may change depending on statistics. Since your data grow fast, stats will change and that may trigger a different execution plan.</p> <p>Nested loops are good for small amounts of data, but as you can see, the time grows with volume. The SQL query optimizer then probably switches to a hash or merge plan which is consistent for large volumes of data.</p> <p>To confirm this theory quickly, try to disable statistics auto update and run your test again. You should not see the "bump" then.</p> <p>EDIT: Since Falcon confirmed that performance changed due to statistics we can work out the next steps.</p> <p>I guess you do a one by one insert, correct? In that case (if you cannot insert bulk) you'll be much better off inserting into a heap work table, then in regular intervals, move the rows in bulk into the target table. This is because for each inserted row, SQL has to check for key duplicates, foreign keys and other checks and sort and split pages all the time. If you can afford postponing these checks for a little later, you'll get a superb insert performance I think. </p> <p>I used this method for metrics logging. Logging would go into a plain heap table with no indexes, no foreign keys, no checks. Every ten minutes, I create a new table of this kind, then with two "sp_rename"s within a transaction (swift swap) I make the full table available for processing and the new table takes the logging. Then you have the comfort of doing all the checking, sorting, splitting only once, in bulk.</p> <p>Apart from this, I'm not sure how to improve your situation. You certainly need to update statistics regularly as that is a key to a good performance in general. </p> <p>Might try using a single column identity clustered key and an additional unique index on those three columns, but I'm doubtful it would help much.</p> <p>Might try padding the indexes - if your inserted data are not sequential. This would eliminate excessive page splitting and shuffling and fragmentation. You'll need to maintain the padding regularly which may require an off-time.</p> <p>Might try to give it a HW upgrade. You'll need to figure out which component is the bottleneck. It may be the CPU or the disk - my favourite in this case. Memory not likely imho if you have one by one inserts. It should be easy then, if it's not the CPU (the line hanging on top of the graph) then it's most likely your IO holding you back. Try some better controller, better cached and faster disk...</p>
    singulars
    1. This table or related slice is empty.
    1. POSql Server 2008 R2 DC Inserts Performance Change
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRbjz
    1. USRbjz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSql Server 2008 R2 DC Inserts Performance Change
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI will take a look at it and let you know what I found.
      singulars
      1. PO
      1. USFalcon
    2. COI have tested it, and after turning off the statistics, I did not see the "bump". Through out the test results were very consistent, and I have noticed significant - 10% - increase in performance.
      singulars
      1. PO
      1. USFalcon
    3. COVery well Falcon :) happy. Now you know what's happening and you may be able to fix that ;) I'll try to give some suggestions editing my answer.
      singulars
      1. PO
      1. USRbjz
 

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