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
Improve this question
3
  • 2
    Where is the query?
    – ypercubeᵀᴹ
    Jun 5, 2018 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, 2018 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, 2018 at 13:40

1 Answer 1

Reset to default
4

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.