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

closed as unclear what you're asking by Erik Darling, Scott Hodgin, Colin 't Hart, Vérace, Tom V Jun 5 '18 at 13:41

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.

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