Must declare the scalar variable 1

[HestonJames]

Guys,

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

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

 

[gmmastros]

When you use a table variable, you need to provide in an alias, like this...

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 [!]As MessageStaging[/!]
LEFT OUTER
  JOIN OUI
    ON OUI.OUI = LEFT(MessageStaging.DeviceAddress, 8) 
 WHERE OUI.OUI IS NULL

Notice that there were 2 other places in the query that had @MessageStaging.DeviceAddress. I created an Alias for the table variable named MessageStaging, and then used the alias in the other places (by removing the @ symbol).

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Right you are George, that works a real treat!!!

How long does the @table exist in memory? for the life of the transaction? do I need to explicitly destroy it?

Cheers,

Heston

 

[gmmastros]

table variables (@table) exists until for the session. When you close your connection, they are automatically destroyed for you. In fact, you cannot destroy them. You can remove all the rows from a table variable, but you cannot remove it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

George, thanks for the heads up on that!

So let me pose another quick one if you don't mind, perhaps a little outside of the scope of your experience, I don't know.

Lets say I'm developing the app with a server-side language like Python or Coldfusion who pool database connections, presumably that would mean I run the risk of duplicate data in a scenario where one user picks up another users connection on their request!?

Also, from a concurrency point of view am I right to assume that the variable is connection local? so if someone using another connection declares an @table then it is assigned to a separate memory space?

Thanks George, I appreciate your help.

Heston

 

[gmmastros]

Even if you are pooling connections, table variables are only valid in their own memory space. In fact, the scope of a table variable is very limited, which make them ideal for many circumstances.

For example:

[highlight blue][white]Declare @Temp Table(Id Int)

Insert Into @Temp Values(1)[/white][/highlight]

Select * From @Temp

Copy paste this to SQL Server Management Studio, highlight the part indicated, and Press F5.

Now, Higlight the last line, you'll get this:

[tt][red]Msg 137, Level 15, State 2, Line 1
Must declare the variable '@Temp'.[/red][/tt]

Why? Because the table variable already went out of scope. Now, try with a temp table.

[highlight blue][white]Create Table #Temp(Id Int)

Insert Into #Temp Values(1)[/white][/highlight]

Select * From #Temp

Highlight the first two lines and press F5. Then, highlight the last line and press F5. The temp table still exists.

There's also a global temp table that you should be made aware of (I suppose). I've never needed to use one, but I guess they have their uses. Do this...

With the previous query window open (the one with the temp table), open a second query window and run:

Select * From #Temp

You'll get this:
[tt][red]Msg 208, Level 16, State 0, Line 1
Invalid object name '#Temp'.[/red][/tt]

Now, open another window and run this:

Create Table ##GlobalTempTable(Id Int)
Insert Into ##GlobalTempTable Values(1)

Open another window and run:

Select * From ##GlobalTempTable

This time you will get the results from the table, eventhough it's a temp table and it's run in a different window.

@TableVariable
#LocalTempTable
##GlobalTempTable

Each one has a different scope. Table variables have the narrowest scope and is well suited for server side apps.


From a concurrency perspective, table variables are very safe to use.

Hopefully this answers your questions, but if it doesn't, let me know and I will clarify it more.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Wow George, you always give such thorough explanations!!

That does make a great deal of sense and perfectly illustrates how the scopes of the tables work. I'll continue that with some testing on my part to satisfy my interest that its not duplicating the data across queries.

This is actually a move away from temp tables for me, I have a data import process on a web service which at the moment is based on a temp table however as we're getting more requests on the service I'm seeing deadlock errors being thrown, particularly in busy periods.

My plan is to move away from the temp table and use the in memory table as this should result in less disk I/O and resource locking I hope also means pretty much 0 modification to the queries apart from swapping # for @. Makes for light work :-D

I'm currently load testing and I seem to not be seeing the deadlock errors however under heavier loads is all starts to grind to a halt, I'll keep testing and tweaking but I'm pretty sure that's down to the low server spec we're currently running, CPU cranks up to 100% and system memory sits real high too.

Does that sound like a sensible use case for @ variables?

Cheers again George,

Heston

 

[gmmastros]

This is a tough question to answer. In my experience, table variables perform better than temp tables, so you are probably headed in the right direction.

That being said, table variables do not get statistics built for them, but temp tables do, so sometimes you get better performance with a temp table.

Most people don't realize this, but you can put an index on a table variable. Unfortunately, this can only be implemented through a primary key, so each key must be unique. To put an index on a table varaible...

Declare @Temp Table(IdColumn Int [!]Primary Key[/!], OtherColumn VarChar(20))

You can even have a composite key, like this...

Declare @Temp Table(Id Int, OtherColumn VarChar(20) Primary Key(OtherColumn, id))

Now, understand that this can sometimes slow down the performance too. It all depends on how the table variable is used. It takes longer to insert rows in to a table variable (or any table, for that matter) if there is an index. But, later, when you use the table variable, the query may be faster.

Also, when using a composite primary key, the order in which you list the columns is important.

Primary Key(OtherColumn,id)
Primary Key(Id, OtherColumn)

If you are joining with another table based on ID, then ID should be listed first. If you are joining on another column, then OtherColumn should come first.

For example, in your original question....

[tt][blue]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
)
[/blue][/tt]

You appear to be using DeviceAddress for Distinct and also for a join. What you could do is add an identity column and then use the DeviceAddress and identity column as a composite key, like this...

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[!],
    RowId Int Identity(1,1)
    PrimaryKey(DeviceAddress, RowId)[/!]
)

Since you want to add a primary key (which must be unique), we can add an identity column to the table variable and use that in combination with the DeviceAddress to make it unique. Since DeviceAddress is listed first, you may get better performance.

Of course, you shouldn't assume this will be better, but you should also try it, too. You may be pleasantly surprised.

You should also check your execution plans for the query(s). In another thread, you told me you had a table without any indexes. This is horrible for performance. I honestly believe that every table should have at least one (clustered) index. Since primary keys are implemented through indexes, this means you have tables without a primary key defined. If you have a lot of tables without primary keys and/or indexes, then that is probably the best way to fix your problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

This query will show you tables that do NOT have a clustered index created for it.

Select sysobjects.name
From   sysobjects 
       Left Join (
         Select Id 
         From   SysIndexes 
         Where  Status & 16 = 16
         ) As A
         On sysobjects.id = A.id			
Where  sysobjects.xtype = 'U'
       And A.id is NULL
Order By sysobjects.name

If this returns a table that has more than 1000 rows (especially if the table is heavily used), then you should probably consider adding a clustered index to it.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hello George,

Thank you for your suggestions they certainly look promising and I'm glad to hear you favour the idea behind moving to the table variable. I've just run the query you've posted above to check my indexes and we appear to be all good I seldom have tables without some form of index applied, I think the one we stumbled across in the other thread was an oversight on my part (or the guy that built the table).

Now, let me give you a peek at the full query that we're talking about here, you've seen snippets of if here and there but I might as well present the whole beast to you, just so you can confirm your prior thoughts about placing an index on table variable.

There is a little Adobe ColdFusion code in this example but I'm sure you'll be able to guess what it represent :-D

						/* ----------------------------------------------------------
						   Declare the temporary message staging table in memory. 
						   ---------------------------------------------------------- */
						
						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
						)
						
						/* ----------------------------------------------------------
						   Load the data from service call into the temporary table. 
						   ---------------------------------------------------------- */									
						
						<cfloop from="1" to="#arrayLen(ARGUMENTS.Statistics_Data)#" index="LOCAL.i">
							INSERT INTO @MessageStaging (
														DeviceAddress,
														PushPort,
														LogDateTime,
														LogClass,
														Media_ID,
														Campaign_ID
														)
							VALUES (
									<cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].address#" cfsqltype="cf_sql_varchar" />,
									'0',
									<cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].created#" cfsqltype="cf_sql_timestamp" />,
									<cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].result#" cfsqltype="cf_sql_varchar" />,
									<cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].message_id#" cfsqltype="cf_sql_integer" />,
									<cfqueryparam value="#ARGUMENTS.Statistics_Data[LOCAL.i].campaign_id#" cfsqltype="cf_sql_integer" />
									)
						</cfloop>
						
						/* ----------------------------------------------------------
						   Capture OUIs we have not seen before from MessageStaging 
						   ---------------------------------------------------------- */

						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
						 
						 /* ----------------------------------------------------------
						   Capture Devices we have not seen before from ##MessageStaging  
						   ---------------------------------------------------------- */
						
						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
						    ON OUI.OUI = left(MessageStaging.DeviceAddress,8) 
						LEFT OUTER
						  JOIN Device
						    ON Device.OUI_ID = OUI.OUI_ID
						   AND Device.Address = SUBSTRING(MessageStaging.DeviceAddress,10,8)
						 WHERE Device.OUI_ID IS NULL
						 						
						/* ----------------------------------------------------------
						   Import any new log classes.
						   ---------------------------------------------------------- */
						
						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
						 
						 /* ----------------------------------------------------------
						   Import into MessageLog table
						   ---------------------------------------------------------- */
						
						INSERT
						  INTO MessageLog
						     ( Device_ID
						     , LogDateTime 
						     , LogClass_ID
						     , Media_ID   
						     , Campaign_ID
						     , ThinkTank_ID )
						SELECT Device.Device_ID
						     , MessageStaging.LogDateTime
						     , LogClass.LogClass_ID
						     , MessageStaging.Media_ID
						     , MessageStaging.Campaign_ID
						     , '#LOCAL.ThinkTank.ThinkTank_ID#'
						  FROM @MessageStaging As MessageStaging
						INNER
						  JOIN LogClass
						    ON LogClass.Name = MessageStaging.LogClass
						INNER
						  JOIN OUI
						    ON OUI.OUI = LEFT(MessageStaging.DeviceAddress,8) 
						INNER
						  JOIN Device
						    ON Device.OUI_ID = OUI.OUI_ID
						   AND Device.Address = SUBSTRING(MessageStaging.DeviceAddress,10,8)

Now, I hope you can use your super-human intuition to decipher what's going on here but allow me to help explain a little.

We start by creating our table variable and populating it with the data from our application, all quite simple stuff, nothing very complicated at all, can that process be made more efficient? I don't know, I would guess not.

Now, the next step is that we add any OUI records not seen before to the OUI table, the OUI is made up from the first 8 characters of the @MessageStaging.DeviceAddress field.

The next step is to create any devices that have not been seen before, using the right 8 characters of @MessageStaging.DeviceAddress as the address and joining it to the OUI's... still with me?

We then do the same for log classes, same boring routine.

Finally we come to our main event, where we move the data from the table variable into its perminant home in the database joining the other tables as we go.

Does that make sense? The first few queries are to ensure that we have the FK's in place for our final insert as most 'devices' and 'oui' we see are new and don't already exist, sometimes however they do.

Now you have a bigger picture what would be your assessment? should I be looking at indexing the table variable? or are there some more glaringly obvious optimizations which could be made here? I'm really open to any suggestions you have. I mean, I'm not really keen on changing the schema as this will cause too many logistics challenges but short of that I'm up for giving anything a shot.

Cheers again George, you're a bloody super star.

Heston

 

[gmmastros]

Other than the Cold Fusion <shudder> stuff, it doesn't look too bad.

The biggest thing that bothers me is.... you're joining tables based on Left and Substring. That's gonna slow things down considerably.

The other thing that bothers me is the distinct stuff.

Since you can't really change the schema.... perhaps there is something you can do to improve the situation.

Unfortunately, I need to go home now, so let me just briefly outline my thoughts.

Before we go down that road, how many rows do you typically get in the @MessageStaging table? I mean... if it's a dozen or so, then no big deal. If you're tossing in 1000's of rows, then that would be a good thing to check, you know.

I'm rambling (because I'm tired and hungry). I'll do some thinking on this tonight and probably get back to you tomorrow.



You could create a computed column in your table (for DeviceAddress). Then, you can index the computed column. Then, if the SQL Gods are smiling on you, that index would get used for the joins and your performance would improve.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

George, good morning!

I dived off last night as well, I was shattered and boy was my brain slow to get started this morning I had a few nice thoughts though, along similar lines to you I would guess.

With regards to load on this, a request will see an average of 2-10 records each time in the @MessageStaging, with a maximum potential of 500 (limited at the client end). Each client makes a request once every minute and lets for arguments sake say we have 50 clients.

I agree with you on JOINING the left() elements, this isn't particularly cool and is no doubt causing a few small problems on performance. My thoughts were that at no point do we actually use the entire @MessageStaging.DeviceAddress string, we only ever seem to strip the left() or right() characters from it on the inserts and joins, should we perhaps break that column in two in the table variable to start with, we could then do straight joins to the columns instead of these funny ones, does that make sense? is that what you were suggesting? Even if it doesn't see a massive performance boost it will at the very least clean the SQL up a little :-D ?

I mean, should the server be able to cope with these kinds of figures? or am I asking too much from it with this level of potential concurrency?

Thanks George,

Heston

 

[gmmastros]

2-10 rows for the @MessageStaging table is nothing. It's probably not worth optimizing anything there.

1 request per minute per client for 50 clients. That gives you more than 1 second per call. On average, how long does this code take to run?

I see that you are inserting in to 4 different tables. How big are these tables? Also, consider that you may have too many indexes on those tables, that can slow you down too. Think of it this way, whenever you insert, update, or delete data, the index needs to change (representing more work for SQL Server).

It appears as though you have 5 sections to this code. Insert into staging table, insert into OUI, DEVICE, LogClass, and MessageLog. I would encourage you to determine which of these 5 parts is slow. It could be that 4 parts are very fast, but one of the inserts is slow causing your performance problems.

There's also 'hidden' code to consider. Do you have any triggers on these 4 tables?

sp_helpTrigger 'OUI'
sp_helpTrigger 'DEVICE'
sp_helpTrigger 'LogClass'
sp_helpTrigger 'MessageLog'

If you have poorly performing triggers, this could account for your problems.

Basically....

1. How long (on average) does it take for this code to execute?

2. How long (on average) does each of the 5 sections take?

3. Do you have too many indexes on the tables?

4. How many rows (approximately) does each table have?

5. Do you have any triggers on any of the tables?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Hello George,

Ok that all makes sense to me. I'm glad you've picked up on the fact I'm inserting into multiple tables, this is something I've been thinking about this morning. Looking at my use cases I cant see any reason for my data to be normalized like this it was just done as a 'best practice' when the database was first built apparently, I can certainly see that denormalizing it is something to consider , do you think that would make a decent enough impact? I'd rather avoid it for now as its more work but if it's going to make a killer impact on the problem then it can be done.

To answer your questions:

1. How long (on average) does it take for this code to execute?
At the moment it appears to be around the 3 second mark on average, sometimes a little less.

2. How long (on average) does each of the 5 sections take?
Have you got an effective way for me to check this? Perhaps some function that'll return a result set I can look at?

3. Do you have too many indexes on the tables?
The tables are indexed yes, MessageLog in particular has quite a few stats and indexes on it, unfortunately I'd rather not remove any of these are they were placed in to improve reporting performance which was suffering a little without them.

4. How many rows (approximately) does each table have?
The table counts look like this, again, MessageLog has the most data in at present. As for growth, LogClass and OUI don't really grow very often, MessageLog and Device grow much more substantially.

MessageLog - 3,562,802
Devoce - 539,835
OUI - 10,718
LogClass - 10

5. Do you have any triggers on any of the tables?
We don't have any triggers in the database so we're safe there :-D

I see what you mean about the rows of data in the table variable they're not very substantial at all, all the more reason this process should be efficient.

Thanks for any more info,

Heston

 

[gmmastros]

3 seconds is an eternity (in my opinion). My gut reaction is... this should take less than 0.1 seconds. So something is definitely wrong.

For the timing....

I would suggest you create a table for this.

Create Table ExecutionTime(Description VarChar(50), Duration Int)

Then, modify your code, like this...

Declare @Start DateTime
Set @Start = GetDate()

-- Put your first section here

Insert Into ExecutionTime(Description, Duration)
Values ('Insert @MessageStaging', DateDiff(Millisecond, @Start, GetDate())

Set @Start = GetDate()

-- Put your second section here

Insert Into ExecutionTime(Description, Duration)
Values ('Insert OUI', DateDiff(Millisecond, @Start, GetDate())

Set @Start = GetDate()

-- Put your third section here

Insert Into ExecutionTime(Description, Duration)
Values ('Insert Device', DateDiff(Millisecond, @Start, GetDate())

Set @Start = GetDate()

Continue doing this for all 5 sections of your code, giving each section a different description.

Then, after this query is run a couple times, you can....

Select Description, Avg(Duration) As Duration
From   ExecutionTime
Group BY Description

I'm also curious to know what indexes you have on the device table. Can you run this...

sp_helpIndex 'Device'

Post the results here.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

George, Hi.

That's a nice little trick for monitoring the times, I can see myself using that method in future :-D

Insert @MessageStaging - 26
Insert Device - 1146
Insert Log Class - 3
Insert Message Log - 1216
Insert OUI - 110

That's the times after I ran the test script a whole bunch of times, as suspected its the device and message log inserts which take the time.

As for indexes on the Device table... "PK_DeviceAddress clustered, unique, primary key located on PRIMARY Device_ID"

Does that shed any light on the situation?

Many thanks again for your advice on this George.

Heston

 

[gmmastros]

Don't forget to remove the timing code when we're done.

I suggest this....

Create Index idx_Device_OUI_ID_Address On Device(OUI_ID, Address)

Then...

Truncate Table ExecutionTime

Finally... test the code a little and re-run the query to get the execution times.

With any luck, the Insert Message Log execution will drop from 1.2 seconds to about 30 milliseconds.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Insert @MessageStaging - 7
Insert Device - 2
Insert Log Class - 2
Insert Message Log - 23
Insert OUI - 73

Now that's done and dusted... lets make love! ;-)

 

[gmmastros]

I dunno. I thought this would take 0.1 seconds, and according to your numbers, we're still at 0.107 seconds.

Still though.... from approximately 3 seconds down to 0.1 seconds. Not a bad day's work. You have my permission to take the rest of the day off.

Actually, though. The Insert OUI is bugging me.

Run:

sp_helpIndex 'OUI'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HestonJames]

Yeah you're right, those 7 milliseconds are a real bugger aren't they ;-)

As for the index on the OUI table, looks like this:

PK_OUI nonclustered, unique, primary key located on PRIMARY OUI_ID
UK_OUI clustered, unique, unique key located on PRIMARY OUI

What do you think to that?

Heston