Need help diagnosing a problem

Question

Need help diagnosing a problem

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Paul (12/22/2008)
From what I've read, there's an overhead associated with checking for locks that you incur even if there aren't any.
Adding NOLOCKS is supposed to skip that.

Very small overhead.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Which transaction level are you using?

Repeatable read?

N 56°04'39.16"
E 12°55'05.25"

Paul-962113 SSC Journeyman Points: 83 More actions · Answer 3

Thanks for the suggestions everybody.

I've rewritten the logging system to use an identity column and removed the code that identifies probable repeat records when inserting into the log.

I still need to do that bit however so any help writing an efficient query to do it would be appreciated.

The table looks like this (id is a primary key);

CREATE TABLE [dbo].[EventLog](

[Id] [int] IDENTITY(1,1) NOT NULL,

[EventType] [varchar](10) NULL,

[EventDate] [datetime] NULL,

[MediaId] [int] NULL,

[IPAddress] [varchar](15) NULL,

--irrelevant fields removed for clarity

[SuspectedCheating] [bit] NULL

and I need a query that will identify records where a 2nd record exists with the same IPAddress, MediaId and EventType less than a short time interval before the suspect record (call it 1 minute for now) where neither record has already been marked as suspect

I've started with this;

SELECT e1.id

FROM eventlog as e1 INNER JOIN eventlog as e2

ON e1.IPAddress=e2.IPAddress AND e1.MediaId=e2.MediaId AND e1.eventtype=e2.eventtype

WHERE e1.eventdate BETWEEN e2.eventdate AND dateadd(mi,1,e2.eventdate)

AND NOT(e1.id = e2.id)

AND e1.suspectedcheating=0

AND e2.suspectedcheating=0

It takes forever to run (duh!), and I've got a few questions;

1. Should I be using subqueries to restrict the number of records before the join?

2. Would the conditions comparing the dates and ids of the records be better off in the join or in the where clause?

3. What sort of index(es) should I set up? Multi-column or several singles? Which fields?

I'm a web developer not a DBA so I admit that I do a lot of this stuff by trial and error normally.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I don't know if this was brought up yet - but have you taken a look as whether Autogrowth might be firing? If that's not set up right, that could cause massive ugliness (especially if the growth factor is too big, etc....)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

kend-631641 Grasshopper Points: 14 More actions · Answer 5

kend-631641

Grasshopper

Points: 14

December 23, 2008 at 9:46 am

#916875

What indexes are on the eventlog table?