Query taking longer and longer every time

• fmg000

  Newbie

  Points: 9

  More actions

  November 28, 2008 at 1:02 pm

  #132527

  Hello, my problem is the following.

  I have an Access Control System running that uses SQL DB to store all the transactions (entries, exits, cardholders,etc) so it's reading and writing the DB all time.

  The HR people uses the system to get time and attendance data, through a series of jobs. Recently they started to realize the jobs started taking more and more time each day.

  I used the following query

  SELECT count(*)

  FROM PANELLOG

  WHERE (PCDate>= CONVERT(DATETIME, '2008-11-10 00:00:00', 102))

  AND (PCDate< CONVERT(DATETIME, '2008-11-11 00:00:00', 102))

  that used to take seconds, now taking 10 minutes or more.

  I did a backup of the database for testing purposes and I'm using it now to try to solve the problem.

  I droped and created the indexes (except the PK clustered one) and after that the query took 3 seconds. A couple of minutes later I ran the query again and took 10 mins.

  I used DBCC DBREINDEX ('PanelLog') A little time improvement, 4 mins first, and 10+ later

  I used the following query to rebuild the PK index

  CREATE UNIQUE CLUSTERED

  INDEX [PK_AlarmLog] ON [dbo].[AlarmLog] ([RecordID])

  WITH

  DROP_EXISTING

  ON [PRIMARY]

  Then ran the count query again four times and the time increased: 1:30,2:45, 5:04

  10:18

  I've noticed that everytime I run this query, it goes to blocked processes, and says it blocked by itself.

  Do you have any idea of what I could do next?

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  November 28, 2008 at 1:29 pm

  #904967

  Is PCDate an index?

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• Gail Shaw

  SSC Guru

  Points: 1004514

  More actions

  November 28, 2008 at 1:54 pm

  #904976

  How fast is data changing in that table? What's the table schema?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass