Please give me a replacement for a Cursor
-
HTH.
-
A set solution vs an equivalent cursor solution is at least 3 times faster, and usually 10 times faster. That's why a set solution (and the internal engine that makes the necessary search) should always be faster than a one by one procedural-solution.
I can't only imagine a cursor solution faster than a set solution, if the tables involved are frequently blocked or does not have the proper index defined.
-
The one and only case I'm aware of is this one :
Let's say you want to get the nth line of a big query, it's sometimes faster to open the query in a cursor and use the goto line# (sorry forgot the actual name call). This can be faster because you often result to some sort of unequal joing with group by and count(*) to generate the row number which takes longer than to just query the table once and goto the right line (faster than create table/insert into + select too).
-
Remi wrote:
Here's my thinking : Don't use cursors, ever. Then if you find a situation where a cursor is faster than a set based approach or there's no set based approach (some rare admin task), then go for it. But I assure you that it's not gonna happen often.
Now knowing what you mean by "set based approach", I agree that in many cases this can replace cursors and will be faster. But I am not convinced that it can be used as often as you describe. How would you write a stored procedure, with input parameter @n int, that inserts into a table t(id int) all integers between 1 and @n - without using a cursor (or a loop)?
-
How about another case.
I've got to send out the same email message to two different addresses
EmailAddressTable me@here you@there
MessageTable The job you wanted done is complete
I've got a procedure called usp_SendTheEmail that takes two parameters @To varchar(250) & @Msg varchar(150)
And this shouldn't count towards your post count
--------------------
Colt 45 - the original point and click interface
-
Just a theory...
How about using blind carbon copy
.I know that there are other situations where you have no choice but they should be few and far apart. But that could still use a loop instead of a cursor (not that a loop is really faster anyways).
-
"Now knowing what you mean by "set based approach", I agree that in many cases this can replace cursors and will be faster. But I am not convinced that it can be used as often as you describe. How would you write a stored procedure, with input parameter @n int, that inserts into a table t(id int) all integers between 1 and @n - without using a cursor (or a loop)?"
If you have something harder than this, please post, we love to have new challenges :
Declare @Qty as int
SET @Qty = 7500
create table #a (id int identity(1,1) primary key, dude int not null)
SET ROWCOUNT @Qty
GO
Insert into #a (dude)
Select O.number from master.dbo.spt_values O cross join master.dbo.spt_values O2
/*this takes a long time because it generates 532900 records in my db... A better table choice will speed this up a lot (if you don't expect to need more than 20k records for exemple, then you just create a table with 142 records and cross join to itself, this would run 26 times faster >> ±0.06 sec)*/
GO
SET ROWCOUNT 0 --unlimited
GO
alter table #a
drop column dude
GO
select min(id), max(id) from #a
GO
drop table #a
GO
--this still run in less than 2 secs on a slow server
-
I have a good one for you...
I have a table with 102 columns. The name of each column is an agency. The table has 59,000 rows. I have to look at each column in each row and if the column is TRUE (bit) then insert an entry into another table with the PK of the row and the PK of the associated agency entry from the agency table based on the agency name=column name. So that's 6M cells to process and it takes about 2 hours.
Would I have even designed it that way in the first place? Heck no!! I inherited it from someone and the entire app (Access front-end, SQL back-end, ewwwww!!) is based on this table structure. Nobody wants to pay to have it redone correctly.
I can post the code I'm using to do it if anyone really wants a challenge
-
Oh, and on the Email thing you can use COALESCE to create a comma-delimited string of all the entries in your email table, then use that as your To or BCC. I do that all the time. There's a good post on the board here somewhere on using COALESCE to create a comma-delimited string. You should be able to find it with search.
-
I post this code all the time :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
-
Shoot.
-
I have no trouble getting the column names from syscolumns, it's the iterating over the actual data columns (102) times 59,000 rows and determining for each one whether or not I need to insert a record into the association table...
-
Here's the code. I'm working on getting the CarID into the table variable up front so I don't do that repetitively in the second WHILE loop. If anyone can figure out how to do the code in the WHILE loop (minus the select on CarID), or any other way to speed this up I would be ecstatic!
-------------------------------------------------------------------------
DECLARE @maxcol int
DECLARE @maxid int
DECLARE @curid int
DECLARE @oldid int
DECLARE @ColID int
DECLARE @colname nvarchar(100)
DECLARE @carid int
DECLARE @count float
DECLARE @cols table(cid int identity, colname nvarchar(100))
SET NOCOUNT ON
SET @count = 1
TRUNCATE TABLE APPOINTMENTS
SELECT @maxcol=max(ordinal_position)-2 FROM information_schema.columns
WHERE table_name='2004 UIL Prospects Appointments'
SELECT @maxid=max(ProspectID) FROM [2004 UIL Prospects Appointments]
SELECT @curid=min(ProspectID) FROM [2004 UIL Prospects Appointments]
SET @oldid=@curid
set @ColID=11
While @ColID <=@maxcol BEGIN
insert into @cols
Select column_name FROM information_schema.columns
WHERE table_name=N'2004 UIL Prospects Appointments'
set @ColID=@ColID + 1
END
WHILE @curid<=@maxid BEGIN
IF @count/100=ceiling(@count/100) PRINT CAST(@count as varchar(8))
SET @ColID=11
WHILE @ColID <= @maxcol BEGIN
SELECT @colname=colname from @cols where cid=@ColID-10
SELECT @carid=carid FROM Carriers WHERE Carrier=@colname
INSERT INTO Appointments (ID, CarID) VALUES(@curid, @carid)
SET @ColID = @ColID + 1
END
SELECT @curid=min(ProspectID) FROM [2004 UIL Prospects Appointments]
WHERE ProspectID>@oldid
SET @oldid=@curid
SET @count = @count + 1
END
-
Can you give the rest of the detais??
Sample data/ddl and output in the other tables would really help a lot
. -
The DDL for the simple tables:
CREATE TABLE [dbo].[Appointments] (
[ID] [int] NULL ,
[CarID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Carriers] (
[Carrier] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CarID] [int] NOT NULL
) ON [PRIMARY]
GO
The DDL for the big table that I have to iterate through:
CREATE TABLE [dbo].[2004 UIL Prospects Appointments] (
[ProspectID] [int] NOT NULL ,
[Agency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [decimal](19, 0) NULL ,
[Fax] [decimal](19, 0) NULL ,
[HIG Match] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[1st Auto] [bit] NOT NULL ,
[Accident Fund] [bit] NOT NULL ,
[ACUITY] [bit] NOT NULL ,
[Allied] [bit] NOT NULL ,
[Amerisure] [bit] NOT NULL ,
[ASI] [bit] NOT NULL ,
[Atlantic Mutual] [bit] NOT NULL ,
[Austin Mutual] [bit] NOT NULL ,
[Badger] [bit] NOT NULL ,
[Barnstable] [bit] NOT NULL ,
[Beacon] [bit] NOT NULL ,
[Bituminous] [bit] NOT NULL ,
[Blue Ridge] [bit] NOT NULL ,
[Brethren Mutual] [bit] NOT NULL ,
[Briarcreek] [bit] NOT NULL ,
[Buckeye] [bit] NOT NULL ,
[Bunker Hill] [bit] NOT NULL ,
[Calif Insurance Group] [bit] NOT NULL ,
[Cameron] [bit] NOT NULL ,
[Casco] [bit] NOT NULL ,
[Central Mutual] [bit] NOT NULL ,
[Chubb] [bit] NOT NULL ,
[Cincinnati Financial] [bit] NOT NULL ,
[C N A] [bit] NOT NULL ,
[Colorado Casualty] [bit] NOT NULL ,
[Columbia] [bit] NOT NULL ,
[Commerce] [bit] NOT NULL ,
[Continental Western] [bit] NOT NULL ,
[crop insurer] [bit] NOT NULL ,
[Cumberland] [bit] NOT NULL ,
[Donegal] [bit] NOT NULL ,
[Encompass] [bit] NOT NULL ,
[Erie] [bit] NOT NULL ,
[Erie and Niagara] [bit] NOT NULL ,
[Everett Cash Mutual] [bit] NOT NULL ,
[FAMI] [bit] NOT NULL ,
[Farmers and Merchanics] [bit] NOT NULL ,
[Farmers Mutual of Nebraska] [bit] NOT NULL ,
[Financial Pacific] [bit] NOT NULL ,
[Finger Lakes] [bit] NOT NULL ,
[Firemans Fund] [bit] NOT NULL ,
[Foremost] [bit] NOT NULL ,
[Fremont Insurance] [bit] NOT NULL ,
[General Casualty] [bit] NOT NULL ,
[GMAC] [bit] NOT NULL ,
[GuideOne] [bit] NOT NULL ,
[Hanover] [bit] NOT NULL ,
[Harleysville] [bit] NOT NULL ,
[Hartford] [bit] NOT NULL ,
[Hastings Mutual] [bit] NOT NULL ,
[Hawaii Employers] [bit] NOT NULL ,
[IIABA] [bit] NOT NULL ,
[Iowa Mutual] [bit] NOT NULL ,
[Kansas Mutual] [bit] NOT NULL ,
[Lebananon] [bit] NOT NULL ,
[Liberty Northwest] [bit] NOT NULL ,
[Madison Mutual] [bit] NOT NULL ,
[Marysville Mutual] [bit] NOT NULL ,
[MD Injured Works fund] [bit] NOT NULL ,
[Merchants] [bit] NOT NULL ,
[Mercury] [bit] NOT NULL ,
[Miami Mutual] [bit] NOT NULL ,
[MICOA] [bit] NOT NULL ,
[Midwest Family Mutual] [bit] NOT NULL ,
[Montgomery Insurance] [bit] NOT NULL ,
[MSA] [bit] NOT NULL ,
[Mutual Fire] [bit] NOT NULL ,
[NLC] [bit] NOT NULL ,
[Northern Neck] [bit] NOT NULL ,
[Ohio Casualty] [bit] NOT NULL ,
[OneBeacon] [bit] NOT NULL ,
[Oregon Mutual] [bit] NOT NULL ,
[Peninsula] [bit] NOT NULL ,
[Penn National] [bit] NOT NULL ,
[Pioneer State] [bit] NOT NULL ,
[PRAC] [bit] NOT NULL ,
[Preferred Mutual] [bit] NOT NULL ,
[PROG] [bit] NOT NULL ,
[Rockford Mutual] [bit] NOT NULL ,
[Rockwood Casualty] [bit] NOT NULL ,
[Safeco] [bit] NOT NULL ,
[Safety] [bit] NOT NULL ,
[SAIF] [bit] NOT NULL ,
[Secura] [bit] NOT NULL ,
[Selective] [bit] NOT NULL ,
[Southern Family] [bit] NOT NULL ,
[Southern Mutual] [bit] NOT NULL ,
[St Paul] [bit] NOT NULL ,
[State Auto] [bit] NOT NULL ,
[Superior] [bit] NOT NULL ,
[The Columbus OH Grange] [bit] NOT NULL ,
[Travelers] [bit] NOT NULL ,
[Tuscarora-Wayne] [bit] NOT NULL ,
[Unigard] [bit] NOT NULL ,
[Unitrin] [bit] NOT NULL ,
[West Bend] [bit] NOT NULL ,
[Western Reserve] [bit] NOT NULL ,
[Westfield] [bit] NOT NULL ,
[Wilson Mutual] [bit] NOT NULL ,
[Windsor] [bit] NOT NULL ,
[Wisconsin Mutual] [bit] NOT NULL ,
[Wolverine] [bit] NOT NULL ,
[Added] [bit] NOT NULL ,
[Undesirable] [bit] NOT NULL
) ON [PRIMARY]
GO
The columns starting with [1st Auto] and ending with [Wolverine] are the ones I have to scan.
Sample of Carriers:
1st Auto 1
Accident Fund 2
ACUITY 3
Allied 4
Amerisure 5
ASI 6
Atlantic Mutual 7
Austin Mutual 8
Badger 9
Barnstable 10
Sample of Appointments:
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
Sample of the big table:
1 A Kenai Alaska Insurance Agency NULL 5791 Kenai Spur Hwy Kenai AK 99611 9072834191 NULL N 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0
2 Acordia of Alaska NULL 35681 Kenai Spur Hwy Soldotna AK 99699 9072622515 9072622646 N 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
Viewing 15 posts - 16 through 30 (of 70 total)
You must be logged in to reply to this topic. Login to reply