CODEWITHSUNDEEP
sqlsql-server
Wael Arbi
Wael Arbi

Reputation: 27

Sql Query taking too long to execute in server , not in local

I have an sql query to update a column through a count result from a view, this is my query

UPDATE [dbo].[Table]
   SET [ColumnName] = (select Count(View.Column) from View
                       where table.Column = View.ColumnN and View.Column1>0)                               
WHERE [dbo].[Table].Column in (select Column from View)

this query is taking 1 second when i execute it in my local SqlServer but when i executed in the server where the application is deployed it takes about 1.36 minute , is there something wrong i'm doing :) Thnks in advance

Upvotes: 0

Views: 260

Answers (1)

Garry Xiao
Garry Xiao

Reputation: 242

It's common. Sometimes records from 10 to 100 will cause efficiency loss apparently. Try to group first then use join to combine update records. Your columns relation is not clear for me, just for reference.

UPDATE [dbo].[Table]
   SET [ColumnName] = d.Result
FROM (SELECT View.ColumnN, COUNT(View.Column) AS Result
      FROM View
      WHERE View.Column1 > 0
      GROUP BY View.ColumnN 
     ) d INNER JOIN
     [dbo].[Table] t
     ON d.ColumnN = t.Column

Upvotes: 1

Related Questions