Convert to set-based query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert to set-based query

Author

Topic

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-10-18 : 17:25:50

Well, I've been trying to improve some sprocs since the general consensus is that deadlocks occur from poor SQL. I am having some trouble figuring out how to convert this one procedure -


DECLARE @Intersection INT
SET @Intersection = 1

SELECT 	RSID, SKU, SCID, PSID, CID
FROM 	tblReleaseSequencing 
WITH 	(NOLOCK)
WHERE 	MDID & @Intersection <> 0 AND 
	PLID & @Intersection = 0 AND 
	Released = 0 AND 
	RSID = (SELECT MIN(RSID) 
		FROM tblReleaseSequencing
		WITH (NOLOCK)  
		WHERE (MDID & @Intersection <> 0) AND 
		      (PLID & @Intersection = 0))

This statement feeds information to a conveyor intersection as part of a MES system. MDID, PLID and @Intersection are binary values where the bits correspond to various intersection. MDID contains the entire path that an item will travel, PLID indicates whether the intersection has already requested and received the information and @Intersection indicates the intersection needing the information.

Ex.
MDID = 0000 0000 0000 1001 - intersection 1 and 4 will use this record

If Intersection 1 requests and recieves the data
PLID = 0000 0000 0000 0001
If Intersection 4 requests and receivs the data after intersection 1
PLID = 0000 0000 0000 1001

just a bunch of bit-wise operations


-- Table structure
CREATE TABLE [dbo].[tblReleaseSequencing] (
	[RSID] [int] NULL ,
	[RID] [int] NULL ,
	[PSID] [int] NULL ,
	[PLID] [int] NULL ,
	[Released] [bit] NULL ,
	[MDID] [int] NULL 
) ON [PRIMARY]

Any suggestions or solutions are appreciated.

Thanks,
Sean

Kristen
Test

22859 Posts

Posted - 2004-10-19 : 01:33:11

That isn't going to block anything, is it?

Kristen

Soulweaver
Starting Member

18 Posts

Posted - 2004-10-19 : 09:08:16

I have to agree with Kirsten, you have nolocks on both the main query and the subquery, it shouldn't lock anything.

if, however, you have nolock only on the main query, the subquery will take locks (i found this out the hard way).

Tiaan

-----------------------
Black Holes exist where God divided by Zero
-----------------------

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-10-19 : 09:50:36

The statement I provided is repeated for each intersection. There are 14 intersections so @Intersection = 1 to 14. I was trying to figure out how I could execute this statement once and return a recordset that contained the information for all 14 intersections. I have a similar query used by another part of the system that I converted to a set-based solution. It really improved the execution speed (went from 300+ ms to 16ms).

With the current form of the query, the faster I try to execute this query, the more often I get resource deadlocks.

This query has two entry points or triggers (programmatic not SQL table based) that cause the query to be executed. Hopefully without getting too technical, there are two kinds of conveyor intersections, merges or diverges. Each intersection type has its own trigger. The (two) triggers operate independently. I could marshal the triggers so that one can't fire until the other completes, but this would slow the system down and eliminate the performance gains I hoped to get if SQL Server executes them in parallel. If I have to, I can consolidate the triggers to a single event, but in the end, I need the SQL query to execute faster.

Some additional information, RSID is indexed, yet when this query executes (as viewed using a trace) it reads the whole table. The table is small, but does have 10,000 - 30,000 records.

Sean

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-19 : 11:35:42

Here is an idea create a table with ints from 1 to 14 and use those ints in conjunction with your querie
to retrieve the values in one set (i haven't tested just an idea)

Declare @tblInter table (intersection int)
Insert into @tblInter
select   ints.i
from 
(select i = i1.i + i2.i + i3.i + i4.i
 from
  (select i = 0 union select 1) as i1 ,
  (select i = 0 union select 2) as i2 ,
  (select i = 0 union select 4) as i3 ,
  (select i = 0 union select 8) as i4 ) as ints 

SELECT 	  RSID, SKU, SCID, PSID, CID
FROM 	  tblReleaseSequencing, @tblInter
WITH 	 (NOLOCK)
WHERE 	 (intersection between 1 and 14) AND
          MDID & Intersection <> 0 AND
          PLID & Intersection = 0 AND
          Released = 0 AND
          RSID = (SELECT   MIN(RSID)
                  FROM     tblReleaseSequencing
                  WITH    (NOLOCK)
                  WHERE   (MDID & Intersection <> 0) AND
                          (PLID & Intersection = 0))

and the other not so good idea (but better than calling the sp 14 times) will be to make a loop


DECLARE @Intersection INT
SET @Intersection = 1
While @intersection <15
Begin
    SELECT   RSID, SKU, SCID, PSID, CID
    FROM     tblReleaseSequencing 
    WITH    (NOLOCK)
    WHERE    MDID & @Intersection <> 0 AND 
    	     PLID & @Intersection = 0 AND 
    	     Released = 0 AND 
    	     RSID = (SELECT   MIN(RSID) 
    	             FROM     tblReleaseSequencing 
    	             WITH    (NOLOCK) 
    	             WHERE   (MDID & @Intersection <> 0) AND 
    	                     (PLID & @Intersection = 0))
    Set @intersection = @intersection +1
end

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources