Temp DB locking prob

[whool]

How's this for weird.

The following query is times out. While it is running an exclusvie Extent lock is created in temp db which doesn't let go.

SELECT  jobid FROM INVSwitchTransvalidtbl
WHERE JobID IN (SELECT JobID FROM INVJobsTbl WHERE     periodid = 23)

If i use a differnet periodid in the sub query then it works fine. (Even if the result set in the sub query is larger).

I also find it works if I create a temp table from the sub query:

SELECT  JobID into #jobs FROM INVJobsTbl WHERE periodid = 23

SELECT  jobid 	FROM INVSwitchTransvalidtbl WHERE JobID IN (select jobid from #jobs)

I'm really stumped here. Can anyone explain this?

Cheers
Yael

 

[iker3000]

Try these selects. I found that an inner join is alway a better solution then everything else (especialy if you have some indexes defined on the given columns)

SELECT
jobid
FROM
INVSwitchTransvalidtbl
INNER JOIN (SELECT DISTINCT JobID FROM INVJobsTbl
WHERE periodid = 23) AS Table_1
ON Table1.JobID = INVSwitchTransvalidtbl

OR

SELECT
jobid
FROM
INVSwitchTransvalidtbl
INNER JOIN INVJobsTbl
ON INVJobsTbl.JobID = INVSwitchTransvalidtbl
WHERE
INVJobsTbl.periodid = 23

(There might be a problem with the second one, if periodid is not unique in INVJobsTbl)


You could also try writing a DISTINCT in your original code:

SELECT jobid FROM INVSwitchTransvalidtbl
WHERE JobID IN (SELECT DISTINCT JobID FROM INVJobsTbl WHERE periodid = 23)

Try, which is the fastest.

Iker

 

[whool]

Thanks Iker,

I actually got round the prob by putting an index on INVSwitchTransvalidtbl.JobID. Not exactly sure why this was required, it still should have run without the index, albeit slowly. The JobID datatype is uniquidentifier, perhaps it was a quirk of that datatype.

Re the the JOIN idea, i recall having the prob before and it made no diff.

Cheers,
Yael