I'm Stumped (Complex Joins With and Updates)

My head is spinning trying to figure this one out. Ok. Here is the schema...

--

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [snmpPoller_HostConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[friendlyName] [nvarchar](50) NOT NULL,

[hostname] [nvarchar](255) NOT NULL,

[community] [nvarchar](255) NOT NULL,

[version] [int] NOT NULL,

[queryTimeout] [int] NOT NULL,

[isActive] [bit] NOT NULL,

[passedSanityCheck] [bit] NOT NULL CONSTRAINT [DF_snmpPoller_HostConfig_passedSanityCheck] DEFAULT ((0)),

CONSTRAINT [PK_snmpPoller_HostConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [snmpPoller_TargetConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[hostId] [int] NOT NULL,

[description] [nvarchar](50) NOT NULL,

[oid] [nvarchar](255) NOT NULL,

[interval] [int] NOT NULL,

[lastPollTimestamp] [datetime] NOT NULL,

[nextPollTime] AS (dateadd(second,[interval],[lastPollTimestamp])),

[isActive] [bit] NOT NULL,

[isPolling] [bit] NOT NULL,

CONSTRAINT [PK_snmpPoller_TargetConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[snmpPoller_TargetConfig] WITH CHECK ADD CONSTRAINT [FK_snmpPoller_TargetConfig_snmpPoller_HostConfig] FOREIGN KEY([hostId])

REFERENCES [snmpPoller_HostConfig] ([id])

Ok. So I have this query

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

AS

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

)

SELECT * FROM WorkTable

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

That generates this xml

<Hosts>

<Host hostId="6" hostname="XXX.XXX.XXX.XXX" community="XXXXX" version="2" timeout="30">

<Target targetId="1757" oid=".1.3.6.1.2.1.31.1.1.1.6." interval="30" />

<Target targetId="1758" oid=".1.3.6.1.2.1.31.1.1.1.10." interval="30" />

<Target targetId="1759" oid=".1.3.6.1.2.1.31.1.1.1.7." interval="30" />

<Target targetId="1760" oid=".1.3.6.1.2.1.31.1.1.1.11." interval="30" />

</Host>

<Host hostId="8" hostname=" XXX.XXX.XXX.XXX " community="XXXXXX" version="2" timeout="30">

<Target targetId="2037" oid=".1.3.6.1.2.1.31.1.1.1.6." interval="30" />

<Target targetId="2038" oid=".1.3.6.1.2.1.31.1.1.1.10." interval="30" />

<Target targetId="2039" oid=".1.3.6.1.2.1.31.1.1.1.7." interval="30" />

<Target targetId="2040" oid=".1.3.6.1.2.1.31.1.1.1.11." interval="30" />

</Host>

</Hosts>

Now the problem is that the xml that I generate is to ‘big’. When I submit a get request to the host, I can only fit 128 OIDs into the request PDU. My query, as it is, returns many hosts with upwards with 1000 targets. So question number 1 is how do return the top 128 targets for each host?

Now here is the real problem. I am replacing a working design in an effort to pick up a scale point. The current design has a dispatcher that will hand off the hostId to a work queue where it is picked up by a ‘thread’. This thread will then execute this sql.

SqlCommand^ command = connection->CreateCommand();

command->Parameters->AddWithValue("@.hostId", hostId);
command->CommandText =

"DECLARE @.resultTable TABLE("+

" targetId int NOT NULL,"+

" hostId int NOT NULL,"+

" oid nvarchar(255) NOT NULL"+

")"+

""+

"UPDATE TOP(128) snmpPoller_TargetConfig"+

" SET isPolling = 1 "+

" OUTPUT INSERTED.id, INSERTED.hostId, INSERTED.oid INTO @.resultTable"+

"WHERE (isPolling = 0) AND (isActive = 1) AND (id = @.hostId) AND (lastPollTimestamp < { fn NOW() })"+

""+

""+

"SELECT * FROM @.resultTable";

SqlDataReader^ reader = command->ExecuteReader();

So rather than make (host * (number of targets / 128)) calls per dispatch cycle, I need to make it all in one call.

So SQL gurus…. What’s the kung-fu? How do I get all my hosts with the top 128 targets AND set a flag on all targets in one atomic shot (I believe this ‘one-shot’ statement can be built off the with(), but I’m nothing but confused at this point)

Paul

Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com

is it possible to use ROW_NUM() ranking function within your query and then get the outputs based on row num. Also if you paste your sample data it shall aid the understanding|||

I actually just came up with this. It works just fine, but I'd like to consolidate it down to one statement. Any thoughts?

--

BEGIN TRANSACTION

DECLARE @.buffer TABLE

(

Tag int,

Parent int,

[Host!1!hostId] int,

[Target!2!targetId] int,

[Host!1!hostname] nvarchar(255),

[Host!1!community] nvarchar(255),

[Host!1!version] int,

[Host!1!timeout] int,

[Target!2!oid] nvarchar(255),

[Target!2!interval] int

);

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

AS

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

AND t.id IN(

SELECT TOP(128) id FROM snmpPoller_TargetConfig

WHERE isPolling = 0 AND isActive = 1 AND lastPollTimestamp < { fn NOW() }

AND hostId = h.id

)

)

INSERT INTO @.buffer

SELECT * FROM WorkTable

UPDATE t SET isPolling = 1

FROM snmpPoller_TargetConfig t,

@.buffer b

WHERE

t.id = b.[Target!2!targetId]

SELECT * FROM @.buffer

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

COMMIT TRANSACTION

Sorry, I can't post the data, the schema should be suffecient. Just say there for 100 hosts records and 750 target records per host.

|||

Please take a look at the OUTPUT clause. You can just perform the UPDATE statement and get the relevant columns (even expressions) from the inserted/deleted tables into a table variable for example. You can then do the SELECT against the table variable. Please take a look at my blog post below for one such example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx