SQL Server Performance

Speeding up INSERTs

Discussion in 'Performance Tuning for DBAs' started by jitesht, Jun 11, 2003.

  1. jitesht New Member

    I have a simple SQL Server database table with 45 fields, which I use for INSERTs
    using my app.
    I do selects very rarely.

    I want to speed up the time it takes for an insert.
    currently I have about 45 fields in my table, and inserts take about
    10 milliseconds.

    Question is,
    If I take my 44 fields (excluding primary key) and throw them together
    in one big char field, will the insert speed up?
    has anyone done a test like this?

    Thanks in advance,
  2. satya Moderator

    It depends if you have clustered index on more than one column.
    And I believe 10ms for an insertion is not bad compared to performance, did you had any issues so far.

    Satya SKJ
  3. Luis Martin Moderator

    Agree with Satya.
    Do you have RAID 5 o RAID 10 to insert?

  4. bradmcgehee New Member

    Here are a variety of tips that can help speed up INSERTS, they may or may not apply to you.

    1) Use RAID 10 or RAID 1, not RAID 5 for your physical disk array. RAID 5 is slow on INSERTs. Also, get faster drives, faster controller, and consider tuning on write caching on the controller (although this has its disadvantages).
    2) The fewer the indexes on the table, the faster INSERTs will be.
    3) Try to avoid page splits. Ways to do this include having an appropriate fillfactor, rebuild indexes often, or add a clustered index on an incrementing key for the table.
    4) Keep the columns lengths as narrow as possible.
    5) If data length in a column is consistent, use CHAR columns, or if data length varies a lot, use VARCHAR columns.
    6) Try to batch INSERTs rather than to INSERT one row at a time. But this can also cause problems if the batch of INSERTs is too large.

    Your suggestion of putting all of the columns into one large column will probably speed INSERTs, but will make SELECTs more difficult and time consuming.

    Even if you do all of these, your current 10ms rate probably won't increase substantially, although it should increase some.

    Brad M. McGehee, MVP
  5. gaurav_bindlish New Member

    I agree with the team here. The insert may speed up but the fact is that the preprocessing time (of joining the 44 columns) should also be taken into consideration. And as brad said, the select is also going to be difficult.


  6. jitesht New Member

    Thank you for your quick replies.

    I am not using any RAID drives currently.
    My table has only one index, created by the primary key.

    I just did a quick test, I was using MSDE not SQL Server (does that make a difference as far as performance? Only one app is connecting to it.. so i think it shouldn't)

    I connected my app to a MySQL database, with exactly the same table structure, and inserts are taking on the average 1 to 2 milliseconds..
    I could do this, since I'm using pure SQL queries, no T-SQL.

    I took a sample of 500 inserts on both MSDE and MySQL.
    MySQL is not as feature rich as SQL server, but it seems to be lightning fast.
    are there any negative points about MySQL? I'm pretty new to it.

    what do you guys think of MySQL?
    i know i will be getting some heat, since this is a sql server site.
  7. satya Moderator

    Never had experience using MySQL, but I was told good tool to use.
    But looking at the opportunities(utilities/features) in SQL Server (MSDE) I would go for it rather depending on other tools.

    Satya SKJ
  8. Chappy New Member

    I have extensive experience in MySQL, having written a bunch of websites using it. Its fast, accessible, lightweight (very important for websites) and supports a good set of SQL commands. However, I would not reccomend using MySQL as an enterprise tool, MySQL advocates will argue against me but I think its just not cut out for it. There are some very good supporting tools (mostly 3rd party, mostly free), but some areas can sometimes be a little flaky.

    Disaster recovery is poor. It has occurred once or twice where a MySQL table has become inexplicably corrupted and had to be restored from backup.

    The main let down for me is a lack of subquery. It simply does not support it. If youre fairly new to SQL, you will perhaps not understand the significance of this, but I guarantee most experienced MSSQL people will choke at the prospect of having to write everything purely using flat joins.
    To be fair, subqueries is on the to do list (which is well worth a read even to aquaint yourself with what MySQL does NOT currently support).

    I dont have any figures to back me up but Id be fairly certain that more websites run off of MySQL than MSSQL, and as youd expect it has a thriving and large community of users. But for anything corporate or mission critical, Id stick to MSSQL (or oracle I guess).

    I dont think its fair to compare MySQL to MSSQL in terms of the speed of inserts. MySQL was written with speed as quite a large focus, and to appeal as a tool to power websites. To me, data integrity is more important than speed, and possibly the reason MSSQL is a little slower in your tests is simply the amount of extra work it is doing (as you said yourself, it has a larger feature set, and some of these inevitably have time overhead associated with them).

    I hope this helps you somewhat. Remember not to write any tool off ever. Use whatever tool is best for a particular job.

  9. gaurav_bindlish New Member

    Thanks Chappy for this insight. I really appreciate your attitude.

  10. satya Moderator

    Paul, thanks for the flakes on MySQL. I never knew it has such a capability.

    Satya SKJ
  11. Luis Martin Moderator

    Paul thanks ,I learn a new subject to day.
  12. jitesht New Member

    Thank you for your valuable insight.

Share This Page