0

Query was taking one hour and thirty minutes without index on the column and to improve the speed of the query created index on the column love2(name changed) but this time it was taking more than two hours to execute.

we are doing BCP to insert the data in the table. any suggestion would be appreciated.

CREATE NONCLUSTERED INDEX [IDX_ID] ON [dbo].[love2]
(
       [VariableIDly] ASC
)
INCLUDE (     [SerialKiller],
       [OrderChosenbutter],
       [Responsetowhat]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
  • 2
    Where is the query? – ypercubeᵀᴹ Jun 5 '18 at 11:04
  • 1
    It seems you are using singleton inserts based on the abysmal insert performance with or without the index. Consider using SqlBulkCopy or a table-valued parameter for bulk inserts. This will perform orders of magnitude better than individual inserts. – Dan Guzman Jun 5 '18 at 11:48
  • How many records are you inserting that it has to take between an hour and 5 hours? Did you monitor for blocking? – Tom V Jun 5 '18 at 13:40
2

It sounds like you're doing a bulk import, which is known to be causing performance issues together with indices.

Consider dropping your index, executing the bulk import, and recreating the index afterwards. This will probably be faster than your current approach, but how much is not easy to tell upfront.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.