Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Must declare the scalar variable 1

Status
Not open for further replies.
HestonJames

HestonJames

Programmer
Aug 11, 2008
187
GB
Guys,

I'm sure this is a simple enough question but I'm unable to solve it, I'm obviously using something wrong:

Code: 
DECLARE @MessageStaging TABLE (
	DeviceAddress char(17) Collate Database_Default,
	PushPort smallint ,
	LogDateTime datetime,
	LogClass varchar(50) Collate Database_Default,
	Media_ID int,
	Campaign_ID int
)

INSERT 
  INTO OUI
     ( OUI 
     , Manufacturer )
SELECT DISTINCT LEFT(@MessageStaging.DeviceAddress, 8)
     , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121)
  FROM @MessageStaging
LEFT OUTER
  JOIN OUI
    ON OUI.OUI = LEFT(@MessageStaging.DeviceAddress, 8) 
 WHERE OUI.OUI IS NULL

throws an exception saying:

Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@MessageStaging".

When looking through a few tutorials on using these in memory table variables I see a couple of mentions of this error being thrown but real explanation of why.

Can someone give this beginner a kick in the right direction ? ;-)

Cheers all,

Heston
 
Sort by date Sort by votes
It's bugging me that 70% of the execution time is spent dealing with a table that only has 10,000 rows.

I was thinking... if there isn't an index on OUI, then there should be. But you do have that index. So there must be something else going on. You said that OUI doesn't grow very often, so this concerns me even more.

When was the last time you updated your statistics?




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hey George,

Yeah OUI doesn't grow very often and is only 10k records so does seem strange that the INSERT for that is so slow.

When was the last time you updated your statistics?

Judging purely by the fact that I don't know what updating my statistics is; I would say quite some time ;-)

Heston
 
Upvote 0 Downvote
I was just thinking about this a little more. It could be that there are 100 rows for OUI in the ExecutionTime table and only one or 2 rows have an extremely large duration, which would skew the average.

Anyway... I have a job set up on my database (which is kinda small at 500 megs). This job runs once a week when there is little to no activity in the database because it can take several minutes to run each part.

It looks like this...

Code: 
Exec sp_msforeachtable 'SET ARITHABORT ON DBCC DBREINDEX([?]) WITH NO_INFOMSGS'
Exec sp_updatestats
DBCC updateUsage(0)

For my database, it takes 5 - 10 minutes to run. There are probably other maintenance type things that would also help you out, but you should probably start with these.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hey George,

OK I ran that query to update the stats, took about 10 minutes to run on my development box here. I then ran the insert query a few times and the results look like this now.

Insert @MessageStaging - 0
Insert Device - 2
Insert Log Class - 0
Insert Message Log - 2
Insert OUI - 214

Even more performant! that OUI still sticks out like a sore thumb though, god only knows why.

I'll look into setting this as a task on my production box if you think its something I should do, I'll form up a query which figures out the day/time which the server is seeing the least traffic and run it at that period to help it along.

Very cool indeed George, all very cool indeed!

Heston
 
Upvote 0 Downvote
I'm very curious to see what the execution plan is for that part of the query.

Can you add this:

Code: 
SET SHOWPLAN_TEXT ON
Go
-- All your other stuff here

Then, set the results to text. Right click the query window -> Results -> To text. Finally, run the query.

You should now see a text representation of the execution plan. Can you copy/paste that here?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hello George, Here's the execution plan for you, hope it shows something.

Code: 
StmtText
-----------------------
SET SHOWPLAN_TEXT ON

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
						
DECLARE @MessageStaging TABLE (
	DeviceAddress char(17) Collate Database_Default,
	PushPort smallint ,
	LogDateTime datetime,
	LogClass varchar(50) Collate Database_Default,
	Media_ID int,
	Campaign_ID int
)						

INSERT INTO @MessageStag

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Table Insert(OBJECT:(@MessageStaging), SET:([DeviceAddress] = [Expr1004],[PushPort] = [Expr1005],[LogDateTime] = getdate(),[LogClass] = [Expr1007],[Media_ID] = [Expr1008],[Campaign_ID] = [Expr1009]), DEFINE:([Expr1004]='00:00:00:00:00:00', [Expr1005]=

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT 
  INTO OUI
     ( OUI 
     , Manufacturer )
SELECT DISTINCT LEFT(MessageStaging.DeviceAddress, 8)
     , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121)
  FROM @MessageStaging As MessageStaging
LEFT OUTER
  JOIN OUI
    ON OUI.OUI

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Clustered Index Insert(OBJECT:([NewLoggingModel].[dbo].[OUI].[UK_OUI]), OBJECT:([NewLoggingModel].[dbo].[OUI].[PK_OUI]), SET:([NewLoggingModel].[dbo].[OUI].[OUI] = RaiseIfNull([Expr1012]),[NewLoggingModel].[dbo].[OUI].[Manufacturer] = RaiseIfNull([Exp
       |--Compute Scalar(DEFINE:([Expr1012]=CONVERT_IMPLICIT(char(8),[Expr1009],0), [Expr1013]=CONVERT_IMPLICIT(varchar(100),[ConstExpr1014],0)))
            |--Compute Scalar(DEFINE:([Expr1011]=getidentity((69575286),(7),NULL)))
                 |--Top(ROWCOUNT est 0)
                      |--Sort(DISTINCT ORDER BY:([Expr1009] ASC))
                           |--Compute Scalar(DEFINE:([ConstExpr1014]='Unknown OUI, '+CONVERT(varchar(25),getdate(),121)))
                                |--Filter(WHERE:([NewLoggingModel].[dbo].[OUI].[OUI] IS NULL))
                                     |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Expr1009]))
                                          |--Compute Scalar(DEFINE:([Expr1009]=substring(@MessageStaging.[DeviceAddress] as [MessageStaging].[DeviceAddress],(1),(8))))
                                          |    |--Table Scan(OBJECT:(@MessageStaging AS [MessageStaging]))
                                          |--Clustered Index Seek(OBJECT:([NewLoggingModel].[dbo].[OUI].[UK_OUI]), SEEK:([NewLoggingModel].[dbo].[OUI].[OUI]=[Expr1009]) ORDERED FORWARD)

(11 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT
  INTO Device
     ( OUI_ID
     , Address
     , PushPort )
SELECT DISTINCT
       OUI.OUI_ID
     , SUBSTRING(MessageStaging.DeviceAddress,10,8)
     , MessageStaging.PushPort
  FROM @MessageStaging As MessageStaging
INNER
  JOIN OUI

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Assert(WHERE:(CASE WHEN [Expr1019] IS NULL THEN (0) ELSE NULL END))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([NewLoggingModel].[dbo].[Device].[OUI_ID]), DEFINE:([Expr1019] = [PROBE VALUE]))
            |--Clustered Index Insert(OBJECT:([NewLoggingModel].[dbo].[Device].[PK_DeviceAddress]), OBJECT:([NewLoggingModel].[dbo].[Device].[idx_Device_OUI_ID_Address]), SET:([NewLoggingModel].[dbo].[Device].[OUI_ID] = [NewLoggingModel].[dbo].[OUI].[OUI_I
            |    |--Compute Scalar(DEFINE:([Expr1014]=CONVERT_IMPLICIT(char(8),[Expr1012],0)))
            |         |--Compute Scalar(DEFINE:([Expr1013]=getidentity((133575514),(7),NULL)))
            |              |--Top(ROWCOUNT est 0)
            |                   |--Sort(DISTINCT ORDER BY:([NewLoggingModel].[dbo].[OUI].[OUI_ID] ASC, [Expr1012] ASC, [MessageStaging].[PushPort] ASC))
            |                        |--Filter(WHERE:([NewLoggingModel].[dbo].[Device].[OUI_ID] IS NULL))
            |                             |--Nested Loops(Left Outer Join, OUTER REFERENCES:([NewLoggingModel].[dbo].[OUI].[OUI_ID], [Expr1012]) OPTIMIZED)
            |                                  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018]))
            |                                  |    |--Compute Scalar(DEFINE:([Expr1012]=substring(@MessageStaging.[DeviceAddress] as [MessageStaging].[DeviceAddress],(10),(8)), [Expr1018]=substring(@MessageStaging.[DeviceAddress] as [MessageStaging].[Devi
            |                                  |    |    |--Table Scan(OBJECT:(@MessageStaging AS [MessageStaging]))
            |                                  |    |--Clustered Index Seek(OBJECT:([NewLoggingModel].[dbo].[OUI].[UK_OUI]), SEEK:([NewLoggingModel].[dbo].[OUI].[OUI]=[Expr1018]) ORDERED FORWARD)
            |                                  |--Index Seek(OBJECT:([NewLoggingModel].[dbo].[Device].[idx_Device_OUI_ID_Address]), SEEK:([NewLoggingModel].[dbo].[Device].[OUI_ID]=[NewLoggingModel].[dbo].[OUI].[OUI_ID] AND [NewLoggingModel].[dbo].[Device].
            |--Index Seek(OBJECT:([NewLoggingModel].[dbo].[OUI].[PK_OUI]), SEEK:([NewLoggingModel].[dbo].[OUI].[OUI_ID]=[NewLoggingModel].[dbo].[Device].[OUI_ID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT 
  INTO LogClass (Name)
SELECT DISTINCT MessageStaging.LogClass
  FROM @MessageStaging As MessageStaging
LEFT OUTER
  JOIN LogClass
    ON LogClass.Name = MessageStaging.LogClass
 WHERE LogClass.Name IS NULL

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Clustered Index Insert(OBJECT:([NewLoggingModel].[dbo].[LogClass].[PK_LogClass]), SET:([NewLoggingModel].[dbo].[LogClass].[Name] = RaiseIfNull(@MessageStaging.[LogClass] as [MessageStaging].[LogClass]),[NewLoggingModel].[dbo].[LogClass].[LogClass_ID] 
       |--Compute Scalar(DEFINE:([Expr1009]=getidentity((2105058535),(7),NULL)))
            |--Top(ROWCOUNT est 0)
                 |--Sort(DISTINCT ORDER BY:([MessageStaging].[LogClass] ASC))
                      |--Filter(WHERE:([NewLoggingModel].[dbo].[LogClass].[Name] IS NULL))
                           |--Nested Loops(Left Outer Join, WHERE:([NewLoggingModel].[dbo].[LogClass].[Name]=@MessageStaging.[LogClass] as [MessageStaging].[LogClass]))
                                |--Table Scan(OBJECT:(@MessageStaging AS [MessageStaging]))
                                |--Clustered Index Scan(OBJECT:([NewLoggingModel].[dbo].[LogClass].[PK_LogClass]))

(8 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

INSERT
  INTO MessageLog
     ( Device_ID
     , LogDateTime 
     , LogClass_ID
     , Media_ID   
     , Campaign_ID
     , ThinkTank_ID )
SELECT Device.Device_ID
     , MessageStaging.LogDateTime
     , LogClass.LogClass_ID
     , MessageSta

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Assert(WHERE:(CASE WHEN [Expr1029] IS NULL THEN (0) ELSE CASE WHEN [Expr1030] IS NULL THEN (1) ELSE CASE WHEN [Expr1031] IS NULL THEN (2) ELSE NULL END END END))
       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([NewLoggingModel].[dbo].[MessageLog].[ThinkTank_ID]), DEFINE:([Expr1031] = [PROBE VALUE]))
            |--Nested Loops(Left Semi Join, OUTER REFERENCES:([NewLoggingModel].[dbo].[MessageLog].[LogClass_ID]), DEFINE:([Expr1030] = [PROBE VALUE]))
            |    |--Nested Loops(Left Semi Join, OUTER REFERENCES:([NewLoggingModel].[dbo].[MessageLog].[Device_ID]) OPTIMIZED, DEFINE:([Expr1029] = [PROBE VALUE]))
            |    |    |--Clustered Index Insert(OBJECT:([NewLoggingModel].[dbo].[MessageLog].[PK_MessageLog]), OBJECT:([NewLoggingModel].[dbo].[MessageLog].[_dta_index_MessageLog_7_277576027__K4_K3_K7_K2_K5]), SET:([NewLoggingModel].[dbo].[MessageLog].[Dev
            |    |    |    |--Compute Scalar(DEFINE:([ConstExpr1026]=CONVERT_IMPLICIT(int,'#LOCAL.ThinkTank.ThinkTank_ID#',0)))
            |    |    |         |--Compute Scalar(DEFINE:([Expr1015]=getidentity((277576027),(7),NULL)))
            |    |    |              |--Top(ROWCOUNT est 0)
            |    |    |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([NewLoggingModel].[dbo].[OUI].[OUI_ID], [Expr1027]) OPTIMIZED)
            |    |    |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1028]))
            |    |    |                        |    |--Hash Match(Inner Join, HASH:([MessageStaging].[LogClass])=([NewLoggingModel].[dbo].[LogClass].[Name]), RESIDUAL:(@MessageStaging.[LogClass] as [MessageStaging].[LogClass]=[NewLoggingModel].[dbo].[LogCl
            |    |    |                        |    |    |--Compute Scalar(DEFINE:([Expr1027]=substring(@MessageStaging.[DeviceAddress] as [MessageStaging].[DeviceAddress],(10),(8)), [Expr1028]=substring(@MessageStaging.[DeviceAddress] as [MessageStaging].
            |    |    |                        |    |    |    |--Table Scan(OBJECT:(@MessageStaging AS [MessageStaging]))
            |    |    |                        |    |    |--Clustered Index Scan(OBJECT:([NewLoggingModel].[dbo].[LogClass].[PK_LogClass]))
            |    |    |                        |    |--Clustered Index Seek(OBJECT:([NewLoggingModel].[dbo].[OUI].[UK_OUI]), SEEK:([NewLoggingModel].[dbo].[OUI].[OUI]=[Expr1028]) ORDERED FORWARD)
            |    |    |                        |--Index Seek(OBJECT:([NewLoggingModel].[dbo].[Device].[idx_Device_OUI_ID_Address]), SEEK:([NewLoggingModel].[dbo].[Device].[OUI_ID]=[NewLoggingModel].[dbo].[OUI].[OUI_ID] AND [NewLoggingModel].[dbo].[Device].
            |    |    |--Clustered Index Seek(OBJECT:([NewLoggingModel].[dbo].[Device].[PK_DeviceAddress]), SEEK:([NewLoggingModel].[dbo].[Device].[Device_ID]=[NewLoggingModel].[dbo].[MessageLog].[Device_ID]) ORDERED FORWARD)
            |    |--Clustered Index Seek(OBJECT:([NewLoggingModel].[dbo].[LogClass].[PK_LogClass]), SEEK:([NewLoggingModel].[dbo].[LogClass].[LogClass_ID]=[NewLoggingModel].[dbo].[MessageLog].[LogClass_ID]) ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([NewLoggingModel].[dbo].[ThinkTank].[PK_ThinkTank]), SEEK:([NewLoggingModel].[dbo].[ThinkTank].[ThinkTank_ID]=[NewLoggingModel].[dbo].[MessageLog].[ThinkTank_ID]) ORDERED FORWARD)

(19 row(s) affected)

Rather you than me trying to figure that one out ;-) its all double dutch to me.

Thanks,

Heston
 
Upvote 0 Downvote
Hmm....

I was looking to see which index is used for the query. Just so you know...

[tt][blue]
|--Compute Scalar(DEFINE:([Expr1009]=substring(@MessageStaging.[DeviceAddress] as [MessageStaging].[DeviceAddress],(1),(8))))
| |--[!]Table Scan[/!](OBJECT:(@MessageStaging AS [MessageStaging]))
|--[!]Clustered Index Seek[/!](OBJECT:([NewLoggingModel].[dbo].[OUI].[UK_OUI]), SEEK:([NewLoggingModel].[dbo].[OUI].[OUI]=[Expr1009]) ORDERED FORWARD)
[/blue][/tt]

From a performance perspective clustered index seek is the fastest and table scan is the slowest. Notice this is table scanning the @MessageStaging table, but there's only a handful of rows, so that's no big deal. The important part is the clustered index seek on the OUI table. This is good.

I'm nearly out of ideas here. You could try this, but I doubt it will make much difference (if any).

Replace:

[tt][blue]
INSERT
INTO OUI
( OUI
, Manufacturer )
SELECT DISTINCT LEFT(MessageStaging.DeviceAddress, 8)
, 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121)
FROM @MessageStaging As MessageStaging
LEFT OUTER
JOIN OUI
ON OUI.OUI = LEFT(MessageStaging.DeviceAddress, 8)
WHERE OUI.OUI IS NULL
[/blue][/tt]

With...

Code: 
                        INSERT 
                          INTO OUI
                             ( OUI 
                             , Manufacturer )
                        Select A.OUI
                               , 'Unknown OUI, ' + CONVERT(VARCHAR(25), GETDATE(), 121)
                        From   (
                                 Select Distinct Left(DeviceAddress, 8) As OUI
                                 From   @MessageStaging
                               ) As A
                               Left Join OUI
                                 On A.OUI = OUI.OUI
                        Where  OUI.OUI IS NULL

Since this is substantially changing the query, I encourage you to test this very well, just to make sure it works properly. Even then... if it doesn't speed up the query, You might as well put it back to the original version.

Hope this helps.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Hey George,

Thank you for your effort with that, seems we're flogging a dead horse though as that hasn't made any noticeable difference to the performance, I guess we'll just have to admit defeat for now :)

I'm so happy with this solution now, it should seem me through for quite some time now the performance is so much better, the server will be all the happier for it as the load should be greatly reduced now and a much lower chance of deadlocking too!!

I've also leaned a great deal from the thread about indexing, I always assumed that indexing would only ever hurt and INSERT process however after working with this today we saw that huge drop from the addition/change of the index on the Devices table.

Really great work George, this is very cool!

Many thanks for your help.

Heston
 
Upvote 0 Downvote
You're welcome.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
gmmastros said:
table variables (@table) exists for the [life of the] session
Click to expand...
Actually, table variables exist for the life of the batch, not the session.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
502
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
1K
Chris Miller
Chris Miller
leifoet
  • Locked
  • Question
How can I update the input of a form (rows and columns) to the database in one query ?
Replies
1
Views
371
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question
Part Id 3900 take wrong technology id as 7 and it must Be 2 because Feature Name and Value Exist?
Replies
4
Views
267
mikrom
mikrom
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
687
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top