multiple insert call for a table having insert trigger

Hi

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1

ThanksLooks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||Thanks for reply me back..
I am using cursor to call multiple insert inside that curser.. here is the code..

OPEN DestinationIDList
FETCH NEXT FROM DestinationIDList INTO @.DestinationID

WHILE @.@.FETCH_STATUS = 0

BEGIN

INSERT INTO Table(ID, DestinationID) VALUES(@.ID, @.DestinationID)

FETCH NEXT FROM DestinationIDList INTO @.DestinationID
END
CLOSE DestinationIDList
DEALLOCATE DestinationIDList

What should I have to do so that trigger fire for each insert ?

Thanks

Originally posted by aldo_2003
Looks like SQL Server is treating these multiple inserts as a batch and therefore only assuming one insert.

Try using the GO statement between the inserts and this will cause your trigger to fire with every insert.

INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO
INSERT INTO T1 -- ETC
GO

Cheers|||What you could do is to write the Insert as a stored procedure.

Then call this proc from your cursor passing the variables that you have defined in your cursor

something like :

create proc Insert_T1
(@.id as int , @.DestinationID as int)
as

INSERT INTO T1(ID, DestinationID) VALUES(@.ID, @.DestinationID)
go

All you do call this proc in your cursor land this should make your trigger fire for every insert you perform

Give it a go and see if it works

Cheers|||No Luck :-(|||Can you post the code for your trigger

Cheers|||Here is the trigger's code

CREATE TRIGGER TR_TD
ON dbo.TRHistory FOR INSERT

AS

DECLARE @.ID int
DECLARE @.DSTID int
DECLARE @.RT decimal (18,4)
DECLARE @.Time datetime
DECLARE @.GTID int
DECLARE @.GTExist int
DECLARE @.intErrorCode INT
SET @.GTID = 0
SET @.GTExist = 0
SET @.ID = 0

-- CHECK ID' S VALIDITY

SELECT @.ID = i.ID, @.DSTID= i.DSTID,
@.RT = i.RT, @.Time = i.Time
FROM Inserted i
INNER JOIN ITSPS ON ITSPS.ID = i.ID

IF @.ID <> 0

BEGIN

-- FIND OUT NO OF GTS FOR SPECIFIC DSTS TO UPDATE FOR CORRESPONDING GT
DECLARE GTList CURSOR FOR
-- SELECT GTS FOR DSTID
SELECT GTs.GTID
FROM GTs INNER JOIN
GTDSTs ON GTs.GTID = GTDSTs.GTID INNER JOIN
ITSPs ON GTs.ID = ITSPs.ID
Where GTs.ID = @.ID AND DSTID = @.DSTID

OPEN GTList

FETCH NEXT FROM GTList INTO @.GTID

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)
BEGIN
SELECT @.intErrorCode = 1
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM

END
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0

BEGIN

-- CHECK WIEHTER GT ENTERY EXISTS IN TR TABLE
-- IF SO THEN UPDATE ELSE INTER NEW FOR GT

SELECT @.GTExist = GTid
FROM TR
WHERE GTid = @.GTid AND DSTID = @.DSTID

IF @.GTExist > 0

UPDATE TR
SET RT = @.RT,
Time = getdate()

WHERE GTID = @.GTid


ELSE

INSERT INTO TR (GTID, DSTID, RT)
VALUES (@.GTID,@.DSTID,@.RT)
-- IN CASE FOR ANY EXCEPTION GO TO PROBLEM PARA AND CLOSE & DEALLOCATE CURSOR
SELECT @.intErrorCode = @.@.ERROR
IF (@.intErrorCode <> 0)
BEGIN
CLOSE GTList
DEALLOCATE GTList
GOTO PROBLEM
END
FETCH NEXT FROM GTList INTO @.GTID


END
CLOSE GTList
DEALLOCATE GTList
COMMIT TRAN
END
ELSE
-- INVALID ID
BEGIN
RAISERROR (' Invalid ID ', 16, 1)
ROLLBACK TRAN
END


PROBLEM:
IF (@.intErrorCode <> 0)
BEGIN
IF (@.intErrorCode = 1)
RAISERROR (' Insert is rejected due to invalid GT DST info', 16, 1)
ELSE
RAISERROR ('Error occured to udated info for tr' s. Please contact administrator ', @.intErrorCode, 1)
ROLLBACK TRAN


END|||Hi aldo_2003

I am Waiting.. Only problem is that if i use multiple insert with GO then multiple insert works.. but when i call is in cursor for multiple then it add only first insert..|||Just got back from lunch mate

Am goiung to try a couple if things and get back to you

Cheers|||Had a quick look at the trigger

Don't think it is the GO statement as I managed to get a test trigger to fire twice without the GO.

I'm not sure why your trigger is only firing once.

What you might want to do is to try and reduce the complexity of the code within the trigger by putting that logic within the stored proc and calling this from your trigger.

You have cursors within cursors when you take the trigger into account and this may be causing you issues that you are not aware of.

Get the trigger to fire properly without any logic in it i.e

Get the first cursor inserting into your table and the trigger firing a simple insert into a test table. Once you have that then implement your trigger logic in a stored proc and call that from the trigger.

I hope this helps, let me know how you get on.

Cheers|||Well there are just so many things...

first you don't need a cursor...collapse the cursor and the insert in to 1...

second a cursor in a trigger can't be a good idea performance wise...but like I said collapse them

third

-- IF NO GT FOUND OF SPECIFIC DST FOR ITSP THEN REJECT
IF (@.GTID = 0)

isn't a check for existance...

Look at @.@.ROWCOUNT

fourth...never mind...fixe the insert first...

Multiple Forms

I have multiple page form that may differ each time they are call. Each may also be mutilple page long. Having trouble trying to print page 1 then swithcing to new form and print it......ect. Any Help would be wonderful. Crystal support don't unserstand my problem.

Thanks In Advance

HenryMe neither, can u please describe ur problem little bit more.|||Let me try one more once.

I have to print an Invoice Form

It has three diferent formats in other words each page has a diff Vb Form

Sometimes we must repeat the same looking form other times we want to switch to another form.

This seem to be such a simple problem have done this kind of thing for years but Crystal Reporter just isn't very willing to do what I want.

I am willing to fax said forms to anyone willing to help.

I feel sure there must be other people out there that would love to be able to do what I have described.

Thanks in advance

Henrysql

Multiple Executions of same package using DTEXEC

Hi,

I have a file-deployed package which I need to call N times at once using DTEXEC. When I attempt to execute N instances of the package, the second instance doesn't execute. I can execute different packages at the same time, but never the same package more than once. Does anyone know how to get this scenario working? Just to clarify a bit more - I can call the package as many times as I like as long as I wait until the previous run of DTEXEC is finished. Please help!

Thanks,

dcb99

I have accomplished similar results but calling the package from another package. You could use a Foreach loop; but that will not allow you parallel executions, though. In my case, I used multiple execute package tasks pointing to the same package; which BTW I don't think is the best approach but the only one I could figure out without writting custom code.|||How about writing a .NET console app to execute the packages?

Multiple db query call from within different context into #temp table

The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @.exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.

DECLARE @.exec_context varchar(30)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.exec_context = @.DBNAME + '.dbo.sp_executesql '
set @.sql = N'select top 10 * from products'
exec @.exec_context @.sql
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
--------------------------
CREATE TABLE #Test (field list here)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)

DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.sql = N'select top 10 * from '+@.DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@.sql)
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #TestOn Mar 21, 12:57 pm, santaferub...@.gmail.com wrote:

Quote:

Originally Posted by

The first query returns me the results from multiple databases, the
second does the same thing except it puts the result into a #temp
table? Could someone please show me an example of this using the first
query? The first query uses the @.exec_context and I am having a
challenge trying to figure out how to make the call from within a
different context and still insert into a #temp table.
>
DECLARE @.exec_context varchar(30)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)
DECLARE companies_cursor CURSOR FOR
SELECT DBNAME
FROM DBINFO
WHERE DBNAME NOT IN ('master', 'tempdb', 'msdb', 'model')
ORDER BY DBNAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.exec_context = @.DBNAME + '.dbo.sp_executesql '
set @.sql = N'select top 10 * from products'
exec @.exec_context @.sql
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
-----------------------*----
CREATE TABLE #Test (field list here)
declare @.sql nvarchar(4000)
DECLARE @.DBNAME nvarchar(50)
>
DECLARE companies_cursor CURSOR FOR
SELECT NAME
FROM sysdatabases
WHERE OBJECT_ID(Name+'.dbo.products') IS NOT NULL
ORDER BY NAME
OPEN companies_cursor
FETCH NEXT FROM companies_cursor INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.sql = N'select top 10 * from '+@.DBNAME+'.dbo.products'
INSERT INTO #Test
exec (@.sql)
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END
CLOSE companies_cursor
DEALLOCATE companies_cursor
SELECT * from #Test
DROP TABLE #Test

Not sure if this solves the problem:

WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.sql = ' INSERT INTO #Test select top 10 * from '+@.DBNAME
+'.dbo.products'
exec (@.sql)
FETCH NEXT FROM companies_cursor INTO @.DBNAME
END

Multiple Datasets and Report Design problems

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
How do I accomplish this?
Thanks in advance.
PamHi Pam,
I'm not sure how your databases are set up (are they on the same
server?), but I would probably choose to combine the data from the
database instead of combining it at the report level - that way you
only need 1 report table and no sub reports and grouping/toggling the
data will be a piece of cake:
SELECT * from User INNER JOIN DatabaseB.dbo.CallLogs CallLogs ON
User.username = CallLogs.username ORDER BY Department, UserName
I hope this helps.
Take Care!
Michelle

Multiple Datasets and Report Design confusion

I have a table in Database A with users and a table in Database B with
call log records. I need to report the call log for each user in the
User table, sorting by Dept (User table) and then user (user table).
Call data will be detailed for each user. Report will run with a date
range selection. I do not know how to design this. I originally started
with a subreport to print the call detail for a user. Unfortunately, I
need to be able to total and avg call detail for each user, dept which I
believe must happen in the main report. By using a sub report I don?t
think this is possible.
Any ideas on how to accomplish this scenario?
Thanks in advance.If both these databases are in the same SQL Server then it is a piece of
cake to do this in a Stored Procedure, just join the two tables. If they are
in different servers it is a little more difficult, you would need to use
linked servers. Same strategy though, you need to look at creating a stored
procedure. Based on your description it does seem to me that a subreport
will not work for you.
Oh, another idea. You don't even need a stored procedure if they are on the
same server, use the generic query designer and create the sql:
select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
b.somedatefield > @.startdate and b.somedatefield < @.enddate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"pb" <pbrechlin@.hotmail.com> wrote in message
news:%23v$M9Z7AFHA.2112@.TK2MSFTNGP09.phx.gbl...
> I have a table in Database A with users and a table in Database B with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally started
> with a subreport to print the call detail for a user. Unfortunately, I
> need to be able to total and avg call detail for each user, dept which I
> believe must happen in the main report. By using a sub report I don?t
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||I believe that your best bet would be to either have some process that
creates an intermediary resultant table combining the abstract data
relationship between the two tables, or design your stored procedure so
that you are joining the two together.
As a design key I always try to leave the real work to the best of
breed. In this case you are going to be better off doing your real data
work in the database layer(SQL Server), not in the application layer
(Reporting Services).
-Brian
pb wrote:
> I have a table in Database A with users and a table in Database B
with
> call log records. I need to report the call log for each user in the
> User table, sorting by Dept (User table) and then user (user table).
> Call data will be detailed for each user. Report will run with a date
> range selection. I do not know how to design this. I originally
started
> with a subreport to print the call detail for a user. Unfortunately,
I
> need to be able to total and avg call detail for each user, dept
which I
> believe must happen in the main report. By using a sub report I
don't
> think this is possible.
> Any ideas on how to accomplish this scenario?
> Thanks in advance.|||THanks Brian, Bruce and Michelle. I have chosen to link my servers (data
on two different servers). I created some views and stored procedures.
All is woking great. Thanks for the direction!
Bruce L-C [MVP] wrote:
> If both these databases are in the same SQL Server then it is a piece of
> cake to do this in a Stored Procedure, just join the two tables. If they are
> in different servers it is a little more difficult, you would need to use
> linked servers. Same strategy though, you need to look at creating a stored
> procedure. Based on your description it does seem to me that a subreport
> will not work for you.
> Oh, another idea. You don't even need a stored procedure if they are on the
> same server, use the generic query designer and create the sql:
> select a.field1, a.field2, b.field1, b.field2 from dbname.dbo.usertable a
> innerjoin dbname2.dbo.calllog b on a.whatever = b.whatever where
> b.somedatefield > @.startdate and b.somedatefield < @.enddate
>

multiple calls to SP

Hi group,

I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))

All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.

The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.

When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.

Can someone give me some pointers on where to look for performance wins?

If you like I can add the SP's

Regards,

Sander

Call it one time instead of 180 times =;o)

But seriously, it does indeed look like a 'looping' symptom.
Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right.

Have you considered to - if possible - do fewer calls? Ideal would probably be just one instead of 180. It's a bit hard to come up with something tangible without knowing more. Why is it 180 calls? Are they all parts of something that is complete once 180 is done?

/Kenneth

|||"Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right."

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

|||If you could provide some details about what exactly is your SP doing, what are you calculating in general, and maybe the SP code and the caller code too, that would be nice-we could be more specific.|||Here is the source code, btw: SP_A and SP_B cannot be combined (technicly they can of course....)

This is SP_A (uspRetrieveHourlyFactor)
ALTER PROCEDURE uspRetrieveHourlyFactor
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.Normalise bit
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.RC as numeric(25,20)
DECLARE @.CurrDate as datetime
DECLARE @.WeightedAverage AS numeric(25,20)

SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType


IF @.Normalise = 0
BEGIN
--No normalisation required!
SET @.WeightedAverage = 1
END
ELSE
BEGIN
EXEC @.RC = uspCalcWeightedAverage @.StartDate2, @.EndDate2, @.InMarket, @.InProductType, 1, @.WeightedAverage OUTPUT
END

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1

CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)

SET @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT
DBI.DATE [DATE],
[PDF].[HOUR] [HOUR],
FLAG [FLAG],
ISNULL((HHF.Factor * flag) / @.WeightedAverage,0.0) [FACTOR]
FROM ##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] - 1
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(dw, DATEPART(D,[DBI].[DATE])) = [HHF].[DayID]
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,[DBI].[DATE]) = [HHF].[Month]
WHERE PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND
(([PDF].[WD-WE] = 1 AND DATEPART(dw, [DBI].[DATE] ) <= 5) OR
([PDF].[WD-WE] = 0 AND DATEPART(dw, [DBI].[DATE] ) > 5)
)
AND HHF.MARKETID = @.InMarketID
ORDER BY DBI.DATE
DROP TABLE #DatesBetweenInterval


This is SP_B (uspCalcWeightedAverage)
ALTER PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage numeric(25,20) OUTPUT
AS

SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.CurrDate as datetime
DECLARE @.helpfloat as numeric(25,20)

--Get ID's for selected parameters
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1
--Create temp table
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
Set @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END

SELECT @.WeightedAverage = (SUM(HHF.FACTOR) / COUNT(PDF.FLAG))
FROM
##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND --[PDF].[WD-WE] = @.InWeekDay
(([PDF].[WD-WE] = 1 AND DATEPART(dw, DBI.DATE ) <= 5) or
([PDF].[WD-WE] = 0 AND DATEPART(dw, DBI.DATE ) > 5)
)
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
DROP TABLE #DatesBetweenInterval

|||

SDerix wrote:

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

Hmmmm... I'm still not convinced that you have to do 180 calls, even though I don't doubt your word on it =;o)

On the other hand, it looks more or less like the overall is grouped by year and month, so it may be doable all at once anyway.. At least in theory. Depending on the datavolume, hardware may restrain the performance if resources aren't available for the 'full' set.

It seems like the proc itself isn't really a problem, since 0.14 sec exec time seems quite acceptable? Though, 180 * 0.14 = 25.2 seconds... And that's the problem.

Would it be possible to rethink the current 'single-month-at-a-time' strategy into something that involves the entire range all at once?

Perhaps you could consider replacing the temporary date-hour table that gets created and thrown away 360 times each run, for a permanent table to join against instead?

spA ends with an order by - is that necessary?
(it would only serve it's ordering purpose if the result is sent to the client, or inserted into a table with some other ordering attribute)

In any case, I believe that the best tuning would be to lower the number of calls from 180 to some lower number, but that would probably involve some rethinking/redesigning of what these procs does....

So... why just a single month each call for a 15 year period? Would it be possible to produce the same result for all 12 months within a year? Or for all months and years in just a single call?

/Kenneth

|||

I think you can do without this temp table #DatesBetweenInterval and use a between clause for the input start and end date.

Did you try creating indexes on the global temp tables?