SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 No Cursor.. need some help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

slaurent
Starting Member

6 Posts

Posted - 06/10/2004 :  05:08:59  Show Profile  Reply with Quote
Hi All,

I have this query that I have built using a cursor. I know it is bad practice and horribly slow. I just can't find a way to do it without a cursor.
Can a guru give me some help?
Thanks
Stephanie

Here is the stored proc:
CREATE PROCEDURE [dbo].[usp_Index_ListStatements]

@AccountNumber as nvarchar(19),
@FromDate as datetime,
@ToDate as datetime
AS
SET NOCOUNT ON

DECLARE @TablePrefix as int


CREATE TABLE #TmpTable
(StatementDate datetime ,
AccountNumber nvarchar(19) COLLATE Latin1_General_CI_AS NOT NULL
)

DECLARE TableC CURSOR FOR
SELECT TablePrefix FROM Statements.dbo.directory WHERE rundate>=@FromDate AND rundate <=@ToDate
OPEN TableC
FETCH NEXT FROM TableC
INTO @TablePrefix
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TmpTable (StatementDate,AccountNumber)
EXEC ('SELECT StatementDate, AccountNumber FROM Statements.dbo.[' + @TablePrefix + '_headers] WHERE AccountNumber=' + @AccountNumber)


FETCH NEXT FROM TableC
INTO @TableIndex
END
CLOSE TableC
DEALLOCATE TableC
SELECT * FROM #TmpTable

sachinsamuel
Constraint Violating Yak Guru

India
383 Posts

Posted - 06/10/2004 :  05:52:29  Show Profile  Reply with Quote
Since the insert need to fired dynamicly, which are stored in the "Statements.dbo.directory" I don't think there is any other way which u r doing. But We can improve the way u r doing.

1) U can use table data types rather than temporary table.
2) U can use the sp_executeSQL rather than exec, which will help u to use the cache.

Let me know in case of any improvement.

Regards

Sachin
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 06/10/2004 :  06:01:22  Show Profile  Reply with Quote
Simply just puting the original select into a #temp table will speed things up a bit...


CREATE PROCEDURE [dbo].[usp_Index_ListStatements]

@AccountNumber as nvarchar(19),
@FromDate as datetime,
@ToDate as datetime
AS
SET NOCOUNT ON

DECLARE @TablePrefix as int
DECLARE @TmpTable TABLE 
(
StatementDate datetime ,
AccountNumber nvarchar(19) COLLATE Latin1_General_CI_AS NOT NULL
)
SELECT TablePrefix INTO #tmpPrefix FROM Statements.dbo.directory WHERE rundate>=@FromDate AND rundate <=@ToDate

DECLARE TableC CURSOR FOR
SELECT TablePrefix FROM #tmpPrefix
OPEN TableC
FETCH NEXT FROM TableC
INTO @TablePrefix
WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @TmpTable (StatementDate,AccountNumber)
		EXEC ('SELECT StatementDate, AccountNumber FROM Statements.dbo.[' + @TablePrefix + '_headers] WHERE AccountNumber=' + @AccountNumber)

		FETCH NEXT FROM TableC
		INTO @TableIndex
	END
CLOSE TableC
DEALLOCATE TableC
SELECT * FROM @TmpTable

Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1630 Posts

Posted - 06/10/2004 :  06:53:48  Show Profile  Reply with Quote
How many potential tables can TableC cursor return?
Have you considered UNION?

Edited by - ehorn on 06/10/2004 07:01:59
Go to Top of Page

slaurent
Starting Member

6 Posts

Posted - 06/10/2004 :  07:04:15  Show Profile  Reply with Quote
Sachin,

Thanks for your suggestions. It seems like I can't use the executeSQL as the prefix parameter is not a clause paramter but a table name parameter.

Also, if I use sp_executeSQL, I need to specify EXEC sp_executeSQL and it is not possible to fill up a table with the return of an exec. I get the following warning:
"execute cannot be used as a source when inserting into a table variable"

Thanks
Stephanie
quote:
Originally posted by sachinsamuel

Since the insert need to fired dynamicly, which are stored in the "Statements.dbo.directory" I don't think there is any other way which u r doing. But We can improve the way u r doing.

1) U can use table data types rather than temporary table.
2) U can use the sp_executeSQL rather than exec, which will help u to use the cache.

Let me know in case of any improvement.

Regards

Sachin

Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 06/10/2004 :  07:09:13  Show Profile  Reply with Quote
Whoops, forgot about that... Leave it as a pre-created #tmp table...
Go to Top of Page

slaurent
Starting Member

6 Posts

Posted - 06/10/2004 :  07:09:26  Show Profile  Reply with Quote
Hi,

there are 60 tables each containing 2.5 million records or more.
I have considered unions however i will not always want the records from all the tables. so i only want to "union" the required number to make it faster (union on 60 tables with that amount a record can be long..I'm assuming here...). the no of records returned by "SELECT TablePrefix FROM Statements.dbo.directory WHERE rundate>=@From AND rundate <=@To" tells me which tables out of the 60 to use.
thanks
stephanie
quote:
Originally posted by ehorn

How many potential tables can TableC cursor return?
Have you considered UNION?

Go to Top of Page

slaurent
Starting Member

6 Posts

Posted - 06/10/2004 :  07:11:57  Show Profile  Reply with Quote
no problem

quote:
Originally posted by RickD

Whoops, forgot about that... Leave it as a pre-created #tmp table...

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000