Merge Join

Hello all,

I have a package where I use merge join for two sorted inputs and the output is stored in a raw file.

In another package, the raw file from above package is again merge joined with another sorted input. Now my question is....do we need to sort again the raw file from first package? or is it OK to set the isSorted property to True and define the sort keys?

Thank you.

As long as you know the input is sorted, you can use the issorted property.

If it turns out not to be sorted, you could miss joins.

BobP

|||

Thank you for the reply...

So here in my case I am using a left join (in merge join)....how would I know if the raw file output from first package i sorted or not?

There is no problem to sort it again but really takes a lot of time(as the size is too big).

|||

If you are sorting it and then writing it out, it should stay sorted.

BobP

|||Ok....if it stays sorted then OK.......many Thanks

Merge Agent System Stored Procedures and sp_Recompile

Hello,

We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?

Thanks

Can you be more specific - which procs, and under what conditions?|||The databases are setup to use push merge replication with the polling interval set to every 15 seconds. It is replicating 4 databases to one subscriber. The procs that are involved are the ones generated by the merge agent when you set up replication. Hope this helps!|||Thanks Candice, but merge replication involves many many procs, including triggers. Can you please be more specific about which ones? And is this occuring at the publisher, or subscriber?

Merge Agent Profile downloadreadchangesperbatch

If a MS represenative monitors this group, I am curious if there is a known
bug where the merge agent quadruples the value stored in the
downloadreadchangesperbatch?
Evidence of this is seen by capturing calls to the procedure shown below
where the first paramter is the number of rows to select. This specific
example occurs when the downloadreadchangesperbatch = 50.
exec sp_MSenumpartialchanges 200, N'#belong_agent_409150406', 2205125,
'1A842C8F-EDD2-4853-9BA4-979BBDF31ADE', 'DE4291F8-D4AE-48FC-A120-486818DFE5A9'
Forgot to mention SP3a @.@.VERSION = 8.00.760 on both publisher and subscriber
running development edition.
"Brian Reuter" wrote:

> If a MS represenative monitors this group, I am curious if there is a known
> bug where the merge agent quadruples the value stored in the
> downloadreadchangesperbatch?
> Evidence of this is seen by capturing calls to the procedure shown below
> where the first paramter is the number of rows to select. This specific
> example occurs when the downloadreadchangesperbatch = 50.
>
> exec sp_MSenumpartialchanges 200, N'#belong_agent_409150406', 2205125,
> '1A842C8F-EDD2-4853-9BA4-979BBDF31ADE', 'DE4291F8-D4AE-48FC-A120-486818DFE5A9'
>

Merge a graphic image with a result set via SQL/Stored Proc

SQL Server 2000
================================== Howdy All -
I have a table of inventory items with a PK of itemnum (varchar(30)).
I have very small image files (gifs) that I wish to merge with the
resultset of a query to use in reporting. Each image file has a name
that matches extactly with itemnum PK it belongs to.
How can I load the images and return the data nd images to my
reporting system?
Yes, I know it is not pretty. I know how to do it in concept but what
do I use to read in the file image?
Thanks,
CraigHi Criag
You may find this useful
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
and http://support.microsoft.com/default.aspx?scid=kb;en-us;258038
The displaying of the images is dependent on what your client is, for
instance Reporting services has an image control.
John
"Craig" wrote:
> SQL Server 2000
> ==================================> Howdy All -
> I have a table of inventory items with a PK of itemnum (varchar(30)).
> I have very small image files (gifs) that I wish to merge with the
> resultset of a query to use in reporting. Each image file has a name
> that matches extactly with itemnum PK it belongs to.
> How can I load the images and return the data nd images to my
> reporting system?
> Yes, I know it is not pretty. I know how to do it in concept but what
> do I use to read in the file image?
> Thanks,
> Craig
>

Menu Hierarchy Display Order (Recursion)

Hi -
I'm using the following Table and Stored Procedure below, to store and
access a menu hierarchy. The recursive stored procedure is working
perfectly and returns a text string outlining my current menu hierarchy.
Currently the stored procedure returns the hiearchy in the order that the
records were created (MenuItemID)
However, I've recently added a "DisplayOrder" column to my table. For
example, let's say that I want Biking to appear before Skiing. Changing the
item's MenuItemID is not an option. So, I'm using a DisplayOrder column. I
need to integrate the DisplayOrder column into my stored procedure so that
it returns the hierarchy in the correct order. I'm not sure what I'm doing
wrong, but I just can't seem to get this part working.
Any help would be appreciated.
Thanks,
Brian
CREATE TABLE MenuItem
(
MenuItemID INT,
ItemName VARCHAR(30),
ParentID INT,
DisplayOrder INT
)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(1, 'Activities', NULL, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(2, 'Fishing', 1, 0)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(3, 'Skiing', 1, 1)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(4, 'Hiking', 1, 2)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(5, 'Biking', 1, 3)
INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
(6, 'Swimming', 1, 4)
CREATE PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT
SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID)
WHILE @.MenuItemID IS NOT NULL
BEGIN
EXEC spCreateMenu @.MenuItemID
SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND MenuItemID > @.MenuItemID)
END
END
EXEC spCreateMenu 1Hi Brian,
Try this.. Hope this helps.
ALTER PROCEDURE spCreateMenu (
@.TargetMenuItemID INT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.MenuItemID INT,
@.ItemName VARCHAR(30),
@.ParentID INT,
@.DisplayOrder INT,
@.rowcount int
SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
MenuItem WHERE MenuItemID = @.TargetMenuItemID
PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
WHILE (@.rowcount <> 0)
BEGIN
EXEC spCreateMenu @.MenuItemID
SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
DisplayOrder FROM MenuItem WHERE ParentID =
@.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
set @.rowcount = @.@.rowcount
END
END|||Excellent - Thanks very much for your help!!
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:B9AE2855-8675-4E07-BF3D-52A2BF892335@.microsoft.com...
> Hi Brian,
> Try this.. Hope this helps.
> ALTER PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT,
> @.rowcount int
> SELECT @.ItemName=ItemName, @.ParentID=ParentID FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> WHILE (@.rowcount <> 0)
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SELECT TOP 1 @.MenuItemID = ISNULL(MenuItemID,0), @.DisplayOrder =
> DisplayOrder FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND DisplayOrder> @.DisplayOrder ORDER BY DISPLAYORDER
> set @.rowcount = @.@.rowcount
> END
> END|||Look up CTEs (common table expressions). They handle recursive logic
without actually having a recursive stored procedure. One SQL statement
will do it for you.
"Brian Patrick" <bpatrick100@.hotmail.com> wrote in message
news:OUFoLJKaGHA.3652@.TK2MSFTNGP03.phx.gbl...
> Hi -
> I'm using the following Table and Stored Procedure below, to store and
> access a menu hierarchy. The recursive stored procedure is working
> perfectly and returns a text string outlining my current menu hierarchy.
> Currently the stored procedure returns the hiearchy in the order that the
> records were created (MenuItemID)
> However, I've recently added a "DisplayOrder" column to my table. For
> example, let's say that I want Biking to appear before Skiing. Changing
the
> item's MenuItemID is not an option. So, I'm using a DisplayOrder column.
I
> need to integrate the DisplayOrder column into my stored procedure so that
> it returns the hierarchy in the correct order. I'm not sure what I'm
doing
> wrong, but I just can't seem to get this part working.
> Any help would be appreciated.
> Thanks,
> Brian
>
> CREATE TABLE MenuItem
> (
> MenuItemID INT,
> ItemName VARCHAR(30),
> ParentID INT,
> DisplayOrder INT
> )
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (1, 'Activities', NULL, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (2, 'Fishing', 1, 0)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (3, 'Skiing', 1, 1)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (4, 'Hiking', 1, 2)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (5, 'Biking', 1, 3)
> INSERT INTO MenuItem (MenuItemID, ItemName, ParentID, DisplayOrder) VALUES
> (6, 'Swimming', 1, 4)
>
> CREATE PROCEDURE spCreateMenu (
> @.TargetMenuItemID INT
> )
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.MenuItemID INT,
> @.ItemName VARCHAR(30),
> @.ParentID INT,
> @.DisplayOrder INT
> SELECT @.MenuItemID=MenuItemID, @.ItemName=ItemName, @.ParentID=ParentID
FROM
> MenuItem WHERE MenuItemID = @.TargetMenuItemID
> PRINT REPLICATE('-', @.@.NESTLEVEL * 4) + @.ItemName
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID)
> WHILE @.MenuItemID IS NOT NULL
> BEGIN
> EXEC spCreateMenu @.MenuItemID
> SET @.MenuItemID = (SELECT MIN(MenuItemID) FROM MenuItem WHERE ParentID =
> @.TargetMenuItemID AND MenuItemID > @.MenuItemID)
> END
> END
>
> EXEC spCreateMenu 1
>

Memory used

I've got this problem: I execute a bulk insert via a
stored procedure of about 500000 records. The bulk insert
works fine, but the memory used grows up very fast. Then,
it never goes down.
So if I try to do another bulk insert via the same stored
procedure, the sp doesn't work and the statement have been
terminated without any error.
The server is a pentium 4 (single processor) 1,5 Ghz and 1
Gb of memory, with windows 2000 server service pack 3 and
it is used for development purposes.
Thank you.marco
Are you sure there are no other activites on the server at the same time?
Have you looked at Profiler ?
"marco" <angilerm@.lloydadriatico.it> wrote in message
news:21e701c3a925$4cad3650$a601280a@.phx.gbl...
> I've got this problem: I execute a bulk insert via a
> stored procedure of about 500000 records. The bulk insert
> works fine, but the memory used grows up very fast. Then,
> it never goes down.
> So if I try to do another bulk insert via the same stored
> procedure, the sp doesn't work and the statement have been
> terminated without any error.
> The server is a pentium 4 (single processor) 1,5 Ghz and 1
> Gb of memory, with windows 2000 server service pack 3 and
> it is used for development purposes.
> Thank you.sql