Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2004-03-22 : 17:19:34
|
| Hi,We have a table having 25 columns and primary key on int column and unique key on nvarchar(100) column and having 55M rows. If we insert a row from QA it only took about 16ms while from our application it took 8-12sec. execution plan looks same for both the cases. If we drop unique index insert only takes 16 ms from application also.What can be possible reason that insert takes so much time from application when index is present?We are doing update statistics with 30% sampling ratioThanks--Harvinder |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 17:47:48
|
| You will receive a performance hit on INSERTs when you have indexes. That's why it is important not to index a table too much if the goal is fast inserts. A unique constraint is important if you are trying to guarantee uniqueness. Do not remove the index. Maybe we can help you rewrite the query though.And what do you mean the execution plan looks the same in both cases? How are you viewing the execution plan from your application? I realize it can be done with the SET option, but I doubt it has been coded to show the information.Tara |
 |
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2004-03-22 : 18:25:58
|
| we use sql profiler for execution plan. insert statement just looks like:insert into table1 select * from tab2if insert statement is bad how can it runs in 16ms from QA... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-22 : 18:28:58
|
| Are you serious? That's your query? You do have a column list, don't you? How about a WHERE clause?Tara |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-22 : 21:43:56
|
| Since Tara just had a heart attack and will not be back on the board for quite some time, I'll take this one.You REALLY, REALLY, REALLY need to buy a SQL Server development book though.1. When you do an insert or select, "always" use column lists.INSERT table1( col1, col2, col3) SELECT col1, col2, col3 FROM table2(If you are actully inserting all 25 rows from the table, you don't need a where clause. If you are not, look up SELECT on Books Online.)2. If you don't know what Books Online is, it can be found at Start/Programs/Microsoft SQL Server/Books Online. You should probably go through the index and just read the whole thing. :)3. Never use * in SQL Server if you can avoid it. It does all kinds of nasty things, which you can learn about in Books Online.4. It will always go way faster if you drop the unique index. If you think about that one for a second it makes sense. It's not having to compare those 55M rows to see if those 25 values exist.6. Where you testing that insert from QA in production or on dev? Did you have the same amount of columns in both? Were the two servers matched with the exact same load? This can help us answer the speed question between QA and application.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2004-03-23 : 10:23:43
|
| I know u people are very smart....but i wrote a sample insert statement.......in fact it includes the column list.......and also how our application works is it insert 1000 rows at a time using bcp into staging database that is in bulk insert mode for fast insert and then asyn we insert into oltp database using this insert statement...i will surely look into books online as i always do..but if u have any suggestion about what can make insert slow when we have unique index that will be great......Thanks--Harvinder |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-23 : 10:30:42
|
| We really do get a lot of people who do what your example was showing for real. :) The unique index makes the inserts slow because it has to compare every row you are inserting to make sure it does not already exist. If you are doing this in a batch insert, are sure nothing else is running, and are sure you have no duplicates, you might be able to greatly speed up your batch process by dropping the unique constraint and inserting with a left join to make sure there are no duplicates. I would definitely test it and have used this several times in the past for giant bulk inserts.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Insert Problem