Try to find duplicates, but creating LOCKS?

Question

Post reply

Try to find duplicates, but creating LOCKS?

Nicholas Wang

Default port

Points: 1463
More actions
May 5, 2005 at 11:33 am

#67323

Hi there,
I'm trying to compare products listed in our customer database (CRM) to the live internet database.
The way our screwy system is set up, it's possible to have multiple, active products in the CRM db linking to one product in the live db.
So, this is the way I did it:
SELECT lap.crm_id,
lap.account,
lap.username,
lap.endtime,
ri.row_id,
ri.status_hj,
ri.pf_id,
CONVERT(char(10),od.order_start_date_hj,102) AS 'orderStart',
CONVERT(char(10),od.order_end_date_hj,102) AS 'orderEnd',
CONVERT(char(10), getdate(),102)AS 'dateStamp'
FROM receipt_item ri
INNER JOIN order_detail od
ON ri.row_id = od.row_id
INNER JOIN order_variable fov
ON od.row_id = fov.row_id
INNER JOIN live_active_prods lap
ON CASE fov.pf_category_hj
WHEN '001' THEN col11
WHEN '002' THEN col8
WHEN '019' THEN col6
ELSE 'WTF?!' END = lap.account
WHERE ri.status_hj IN (1,7,12,18)
AND lap.account IN (SELECT lap2.account
FROM receipt_item ri2
INNER JOIN order_variable fov2
ON ri2.row_id = fov2.row_id
INNER JOIN live_active_prods lap2
ON CASE fov2.pf_category_hj
WHEN '001' THEN col11
WHEN '002' THEN col8
WHEN '019' THEN col6
ELSE 'WTF?!' END = lap2.account
WHERE lap2.ETIME >= CONVERT(char(10),getdate(),102) -- ETIME >= Today
AND lap2.ACTIVE = 1
AND ri2.status_hj IN (1,7,12,18)
GROUP BY lap2.account
HAVING count(lap2.account) > 1) -- ACCOUNT can be linked to 2 active products in CRMS
-------------------------------------------------------------------
Unfortunately, it's getting locked up all the time. I'm not sure if it's something I did in this query, so is it somebody else's fault. Any idea?

Viewing 6 posts - 1 through 5 (of 5 total)

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

john-132685 SSCertifiable Points: 6134 More actions · Answer 1

You need to be more clear in your question (your use of the word 'lock'):

Is it just that the query takes forever?

Or is it in a livelock condition that you are experiencing?

Go look at sp_who results, "blocking", etc. in the books online.

mike gercevich SSCrazy Points: 2961 More actions · Answer 2

Since you are doing a compare.. and you do not want to lock any of the tables... Why dont you try using the WITH (NOLOCK) hint. I bet this query performs better with the NOLOCK added to all accessed tables.

-Mike Gercevich

Nicholas Wang Default port Points: 1463 More actions · Answer 3

I haven't tried "NO LOCK" yet, I let the query run on last night before I leave and it ran successful albeit taking 3 hours to complete.

During daytime, it always give me the message "Your transaction was locked with another process and was chosen to be killed... etc". But I guess the query really isn't locking itself, it just takes way too long.

I had the Execution Plan turned on (see below), I think I see where the problem is... at the bottom right, it does an Index Seek on the "active_prods" table: OBJECT: lap2 SEEK lap2.account = lap.account. This returns 51925 rows.

Next is a Bookmark Lookup, I don't know what that does, the same 51925 rows came out. Then, a Filter: WHERE lap2.ETIME > today AND lap2.active = 1... 37908 rows left.

Now here's the problem... a "Table Spool/Lazy Spool" - "Store the data from the input into a temporary table in order to optimize rewinds"... 1,554,957,564 rows output from it!!!! I think this huge I/O to the temp_db is what causing the query to run so slow. I don't understand why this had happened. If I just run the inner subquery to find duplicated "accounts", the query runs in 3 secs and return only 119 rows. What went wrong?? Please help...

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

How about trying it this way, create temp tables to hold your sub query output and replace the IN clause with a join to the temp table .

Thanks

Prasad Bhogadi
www.inforaise.com

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 5

CASE fov.pf_category_hj

WHEN '001' THEN col11

WHEN '002' THEN col8

WHEN '019' THEN col6

ELSE 'WTF?!' END = lap.account

Refering above part

If try and keep a mapping of your order_variable.pf_category_hj in a temp table with desired match of columns as appears from your query. It will avoid duplicate efforts by sql engine. Moreover I guess this will also help to optimize the efforts by active_prods table also.

I hope above fix will do the job. Next step you might need would be to separate the IN clause to a temp table if above solution doesn't help. Usually microsoft recommend to use shorter queries with better indexes.

Gopal