Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

how to replace this cursor with set based solution? Expand / Collapse
Author
Message
Posted Wednesday, September 25, 2013 3:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
I've got a cursor script that I want to replace with a set based solution because the cursor script takes a long time to run. All I've got is the cursor script, for which I've reverse engineered the DDL to solve this problem (without success). The cursor script is the actual script that is running in a production environment, and the DDL is something I've created (for myself) to work out a set based solution. I've posted it all below and I would so much appreciate if you took a look.

The cursor creates an array variable to hold a list of names (LabC1, LabC2, etc) and then fetches the name one by one and compares its value to a value found in the 'name' column of the Machines table.

I hope a copy of the script and DDL will make this explanation clearer.
Here's the cursor script:
DECLARE @benches TABLE (BenchName varchar(5))

INSERT INTO @benches

VALUES ('LabC1'),

('LabC2'),

('LabC3'),

('LabC4'),

('LabD1'),

('LabD2'),

('LabD3'),

('LabD4'),

('LabE1'),

('LabE2'),

('LabE3'),

('LabE4'),

('LabE5'),

('LabE6'),

('LabE7'),

('LabE8')



DECLARE @runsByBench TABLE (BenchName varchar(5), NumberOfRunsOnBench int, LastRun datetime)





-- for each value in the @benches array return the count of runs and the last createdate associated with that run.

DECLARE toQuery CURSOR LOCAL FAST_FORWARD FOR

SELECT b.BenchName

FROM @benches b



OPEN toQuery



-- loop through all of the scripts

DECLARE @bench varchar(5)

FETCH NEXT FROM toQuery INTO @bench

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @runsByBench (BenchName , NumberOfRunsOnBench , LastRun )

SELECT @bench as BenchName, COUNT(*) as NumberOfRunsOnBench, MAX(CreateDate) as LastRun

FROM

(select r.runid, r.creatorid, r.createdate, r.name, r.enddate, count(*) as MachinesInRun

from Runs r

join runs_machines rm on rm.runid=r.runid

join Machines m on m.machineid=rm.machineid
-----HERE'S WHERE IT COMPARES THE VALUE M.NAME TO THE VALUE FETCHED BY THE CURSOR--------------
where m.name like (@bench +'[0-9][0-9][0-9]')
----------------------------------------------------------------------------------------------------------
and r.createdate > dateadd(M,-3,getdate())

group by r.runid, r.creatorid, r.createdate, r.name, r.enddate

) as foo

FETCH NEXT FROM toQuery INTO @bench

END



CLOSE toQuery

DEALLOCATE toQuery





SELECT * FROM @runsByBench



and here's the DDL I created for the tables used by the SQL script to work on the problem:
create table Runs 
(RunID int,
CreatorID int,
CreateDate datetime,
EndDate datetime,
Name varchar(20),
MachineID int);

insert into Runs
values
(1, 201, DATEADD(mi,-3,getdate()-1), DATEADD(mi,+60,getdate()-1), 'LABC1521', 521),
(2, 202, DATEADD(mi,-3,getdate()), DATEADD(mi,+75,getdate()), 'LABC2681', 681),
(3, 203, DATEADD(mi,-3,getdate()), DATEADD(mi,+63,getdate()), 'LABC3123', 123),
(4, 204, DATEADD(mi,-3,getdate()), DATEADD(mi,+65,getdate()), 'LABC4765', 765),
(5, 205, DATEADD(mi,-1,getdate()), DATEADD(mi,+78,getdate()), 'LABC1521', 521);

create table Machines
(MachineID int,
Name varchar(20));

insert into Machines
values
(521, 'LABC1521'),
(681, 'LABC2681'),
(123, 'LABC3123'),
(765, 'LABC4765');

create table Runs_Machines
(Runid int,
MachineID varchar(20));

insert into Runs_Machines
values
(1, 521),
(2, 681),
(3, 123),
(4, 765),
(5, 521);

create table RunsByBench
(Benchname varchar(20),
NumberOfRunsOnBench int,
LastRun datetime);


the query inside the script will work against the upper DDL as long as the you hardcode the m.name value:
select r.runid, r.creatorid, r.createdate, r.name, r.enddate
from Runs r
join runs_machines rm on rm.runid=r.runid
join Machines m on m.machineid=rm.machineid
where m.name like 'LabC1%'

I would like to put the array into a #temp table and compare the results of the query to it. But I have failed. I tried a correlated subquery,
but without a unique key, I discovered it isn't the solution.

Can someone help me find the T-SQL equivalent for the above cursor script?

Thanks!
Post #1498596
Posted Wednesday, September 25, 2013 4:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
An OUTER APPLY might do the job. There might be another option, but this looks like it should work fine.

INSERT INTO RunsByBench
SELECT BenchName AS BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches b
OUTER APPLY (
SELECT r.createdate,
count(*) AS MachinesInRun
FROM Runs r
INNER JOIN runs_machines rm ON rm.runid = r.runid
INNER JOIN Machines m ON m.machineid = rm.machineid
-----HERE'S WHERE IT COMPARES THE VALUE M.NAME TO THE VALUE FETCHED BY THE CURSOR--------------
WHERE m.NAME LIKE (b.BenchName + '[0-9][0-9][0-9]')
----------------------------------------------------------------------------------------------------------
AND r.createdate > dateadd(M, - 3, getdate())
GROUP BY r.runid,
r.creatorid,
r.createdate,
r.NAME,
r.enddate
) AS foo
GROUP BY BenchName




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1498624
Posted Wednesday, September 25, 2013 5:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
This worked, Thank you Luis.
Post #1498625
Posted Wednesday, September 25, 2013 5:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
It was easy to find the solution with the DDL and sample data you posted (I had to run the original query to get the expected results).
The important thing is that you understand what's happening so you can mantain it and replicate it in other cases. If you need help with APPLY, take a look at these articles, search more information on the web or ask any questions here.

Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1498626
Posted Wednesday, September 25, 2013 5:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
Yes, I jumped to thank you but I am examining it. I agree, I must understand so I can reuse in other situations.
I may have some more questions about this before i'm through. For example, did you use a temp table for Benches?

eg.
create table benches (Bench varchar(10));

insert into benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');

????? what did you do to handle array? For me to run your query I create a basic table called Benches, but in reality no such table exists (and won't exist). What assumption did you make to handle for the array @benches? A script populating a temp table?
Post #1498627
Posted Wednesday, September 25, 2013 5:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
for some reason the first time I used #temp table with query it didn't work. Forget the error.
Now either #temp or table variable works.
So, I'll post the entire script/solution below:

DECLARE @benches TABLE (Bench varchar(10));

INSERT @benches
values
('LabC1'),
('LabC2'),
('LabC3'),
('LabC4');

INSERT INTO RunsByBench
SELECT Bench AS BenchName,
COUNT(CreateDate) AS NumberOfRunsOnBench,
MAX(CreateDate) AS LastRun
FROM @benches b
OUTER APPLY (
SELECT r.createdate,
count(*) AS MachinesInRun
FROM Runs r
INNER JOIN runs_machines rm ON rm.runid = r.runid
INNER JOIN Machines m ON m.machineid = rm.machineid

WHERE m.NAME LIKE (b.Bench + '[0-9][0-9][0-9]')

AND r.createdate > dateadd(M, - 3, getdate())
GROUP BY r.runid,
r.creatorid,
r.createdate,
r.NAME,
r.enddate
) AS foo
GROUP BY Bench




Post #1498633
Posted Thursday, September 26, 2013 8:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
Be careful on using table variables, they might reduce performance on your query depending on the number of rows. You should test what's better for your performance (temp table, table variable, other) and consider pros and cons.
Here's an article on the subject: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1498890
Posted Thursday, September 26, 2013 10:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
Excellent, thanks again.
The whole point is to improve performance so I really appreciate added notes about table variables versus temp etc. APPLY articles were great.
Post #1498960
Posted Thursday, September 26, 2013 7:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, February 17, 2014 10:08 PM
Points: 271, Visits: 728
I'm trying to apply what I've learned in the APPLY links you left me, Luis, to my solution. Thus, I want a TVF for the right half of the query. By right half, I mean the query to the right of the OUTER APPLY which gets the createdate for each bench in the input field.
I'm getting error when I try to create the function:
CREATE FUNCTION GetBench (@bench varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT r.createdate
FROM Runs r
INNER JOIN runs_machines rm ON rm.runid = r.runid
INNER JOIN Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@bench + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate())


error:
Msg 4512, Level 16, State 3, Procedure GetBench, Line 5
Cannot schema bind table valued function 'GetBench' because name 'Runs' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


Can you point out what's wrong in the TVF? Thanks.
Post #1499134
Posted Friday, September 27, 2013 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:24 PM
Points: 11,990, Visits: 11,007
polkadot (9/26/2013)
I'm trying to apply what I've learned in the APPLY links you left me, Luis, to my solution. Thus, I want a TVF for the right half of the query. By right half, I mean the query to the right of the OUTER APPLY which gets the createdate for each bench in the input field.
I'm getting error when I try to create the function:
CREATE FUNCTION GetBench (@bench varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT r.createdate
FROM Runs r
INNER JOIN runs_machines rm ON rm.runid = r.runid
INNER JOIN Machines m ON m.machineid = rm.machineid
WHERE m.NAME LIKE (@bench + '[0-9][0-9][0-9]')
AND r.createdate > dateadd(M, - 3, getdate())


error:
Msg 4512, Level 16, State 3, Procedure GetBench, Line 5
Cannot schema bind table valued function 'GetBench' because name 'Runs' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.


Can you point out what's wrong in the TVF? Thanks.


The error message is telling you exactly what the problem is, You MUST use 2 part naming when using schemabinding.

From BOL:


SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1499408
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse