Posted by Erland Sommarskog on 10/01/50 11:18

(chriskoch@iwon.com) writes:
> I am using the loop because of the size of the claims and
> authorizations databases. When I use any JOIN the query times out.
> This way of fetching a record at a time doesn't overpower the server.

Then you have something to fix. The temp table has 1494 rows that
is not much for a set-based query. For an interative loop, it's starting
to be a bit. You will take more power out of the server with the loop
that a good set-based query that inserts all rows in one go. What appears
to happen in your case, is that the loop times out as well. This is
possibly something you can control within DTS, but DTS is not my playground.

And, before I go on, if you really insist on the loop, make sure that
IdentityID is indexed.

Here are some possible variations of Stu's query:

INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)
SELECT t.PRPID,
HasClaims = CASE WHEN EXISTS (SELECT *
FROM dbo.CMC_CLCL_CLAIM CLCL
WHERE t.PRPID = CLCL.PRPID)
THEN 'Y'
ELSE 'N'
END,
t.IdentityID
FROM #TempFACETSNODupes

Or simply add a HasClaims to #TempFACETSNODupes:

UPDATE #TempFACETSNODupes
SET HasClaims = CASE WHEN EXISTS (SELECT *
FROM dbo.CMC_CLCL_CLAIM CLCL
WHERE t.PRPID = CLCL.PRPID)
THEN 'Y'
ELSE 'N'
END
FROM #TempFACETSNODupes t

Or add this column with a default of 'N' and do:

UPDATE #TempFACETSNODupes
SET HasClaims = 'Y'
FROM #TempFACETSNODupes t
JOIN dbo.CMC_CLCL_CLAIM CLCL ON t.PRPID = CLCL.PRPID

If all these executes poorly, you need investigate why. I assume that
CMC_CLCL_CLAIM.PRDID is a primary key, so there is a good index to
use, but the Sybase optimizer may for some reason do a table scan.
But you should be able to convince it with an index hint.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]