MSSQL says I can't, but I want to.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MSSQL says I can't, but I want to.

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-04-28 : 19:22:41

I've got a table used for sesisons in a web farm. Obviously, this table gets a *lot* of selects (one for each pageview or app-served object). Simplified DDL:

create table sessions
(session_guid uniqueidentifier primary key clustered,
i_users int,last_accessed smalldatetime,session_data image)

The session_data is an image because it's a serialized .net dictionary (yeah, maybe not ideal, but I'm stuck with that for now).

Here's the problem: if i_users is not null when retrieving a session, I need to update another table to do some stuff for that user. Right now, the logic in the SP is something like:

update sessions WITH(FASTFIRSTROW,ROWLOCK) set 
last_accessed=getdate() where session_guid=@gSession

select @iUsers=i_users from sessions WITH(NOLOCK) where session_guid=@gSession

select i_users,session_data from sessions WITH(NOLOCK) where session_guid=@gSession

if @iUsers is not null
    exec p_userstuff @iUsers

Those two selects in a row seem like a waste, but SQL server won't let me do something like "select @iUsers=i_users,i_users,session_data from sessions" since it doesn't like assigning a variable in the same statement that returns rows. And I can't stuff the session_data into a variable in the SP since it's an image.

Is there any more elegant solution here for both having logic in the SP depending on the value in the row and also returning that value to the calling program?

Thanks
-b

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-28 : 19:58:03

[code]

declare @x table ( i_users int, session_data image )

insert into @x
select
i_users,
session_data
from
sessions
where
session_guid = @gSession

select @iUsers = i_users from @x

select i_users, session_data from @x

[/code]

CODO ERGO SUM

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-04-28 : 20:06:51

I must need a brain tuneup or something.

Thanks!
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-04-28 : 20:08:14

I must need a brain tuneup or something.

Thanks!
-b

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 02:21:56

I think you could probably get the User ID with:


update sessions WITH(FASTFIRSTROW,ROWLOCK) 
set last_accessed=getdate(),
    @iUsers=i_users
where session_guid=@gSession

Couple of observations:

I guess you are stuck with your GUID now, but for a high-insert table its not an idea clustered PK - lots of table splits as they insert randomly. If you are making (or can make) your own GUID then then are algorithms that will generate sequentially increasing GUIDs - so that the new ones will all insert at the end of the table, and keep it 100% filled (The magic word "COMB" springs to mind for use in Google!). Alternative IDENTITY column could be considered. (We use an IDENTITY together with a "Random Code" we generate - from a GUID actually! - when the session is created, so although the ID can be guessed the random-bit can't, and both are needed for the WHERE clause, although the PK is just on the ID column).

Lots of philosophical arguments to be had around that - will it cause page locks on inserts? Will the skewed index structure need more Rebuilds etc. I presume you are deleting this data after a short while, if so the way the table behaves during the delete process is also relevant!

You should be able to get some speed gain by explicitly naming the Owner of your tables and Sprocs:


update dbo.sessions WITH(FASTFIRSTROW,ROWLOCK) set 
last_accessed=getdate() where session_guid=@gSession

select @iUsers=i_users from dbo.sessions WITH(NOLOCK) where session_guid=@gSession

select i_users,session_data from dbo.sessions WITH(NOLOCK) where session_guid=@gSession

if @iUsers is not null
    exec dbo.p_userstuff @iUsers

Kristen

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-04-29 : 03:53:13

quote:
Originally posted by KristenYou should be able to get some speed gain by explicitly naming the Owner of your tables and Sprocs:

How come ?

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-04-29 : 05:53:22

Ah .. found the answer (i think) in 'The Curse and Blessings of Dynamic SQL' (http://www.sommarskog.se/dynamic_sql.html)

quote:
On SQL 2000, this is an absolute must for effecient use of the query-plan cache. If you leave out dbo from a single table, each user will get his own copy of the plan in the cache. This is because on SQL 2000, each user has a default schema which is equal to the username.

or are there other benefits?

--
This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 07:03:17

"are there other benefits?"

Yes, when you say to SQL "SELECT ... FROM MyTable" if first looks for "MyLoginName.MyTable", which it (presumably) doesn't find, and then it looks for "dbo.MyTable". I have no idea how long that takes, but I think its reasonable to assume that on a couple of million queries a day it adds up to something saved!

One other one to watch out for is if you name your SProcs starting with "SP_..." because then SQL Server will first look in the MASTER database, and then in the local one (and probably for each of those it will look for "MyLogin" owned objects before then defaulting back to "dbo." owner.

Also, it is important that you correctly capitalise all object names - i.e. so that they match the case you created them with; obviously on a Case Sensitive installation that's critical! but on a case insensitive database there is something about cache-misses (can't remember exactly the reason) if there is a case mismatch.

If you are doing dynamic SQL from your application don't forget the "dbo" stuff there too:


Dim strSQL
strSQL = "EXEC dbo.MySproc @foo='bar'"
...

Can't think of anything else major off-hand, but I expect its all detailed at www.sql-server-performance.com

Kristen

Kristen
Test

22859 Posts

Posted - 2006-04-29 : 07:15:46

Actually, on reflection, I think the CASE issue is one of the Optimizer having to hold compile locks on system objects until it can determine if the objects are the same as the ones in the cached plan.

Kristen

aiken
Aged Yak Warrior

525 Posts

Posted - 2006-04-29 : 19:10:26

Thanks, Kristen. Great ideas as always.

You're quite right (and clever) about assigning @iUsers in the update statement. That makes perfect sense and is working great.

The actual SP calls were indeed using dbo. (even though I didn't type it in my examples), but I have to admit that I didn't know that same advice applied to tables as well. I've updated the code accordingly.

The issue with case sensitivity in SP names is that SQL server uses the CHECKSUM function to identify compiled SP's in its SP cache. Checksum is case sensitive, since it's operating on binary data, so if you call a SP with the wrong CaSe, SQL server will throw a cache miss. I don't think it actually recompiles the SP; I think it looks up the SP based on the case-insensitive name, then gets the checksum of the proper case'd SP, then uses the cached version. At least, that's my understanding. I seem to recall it generates SP:CacheMiss but not SP:recompile.

Interestingly, Michael's table variable suggestion (well, answer to my question, not suggestion that I use one) was quite a bit slower than just doing two selects. My guess is that it's because table variables get created in tempdb, and the extra overhead there was worse than just doing two selects.

Finally, you're right that a GUID isn't ideal, but we are kind of stuck with it. Your approach of using ID's internally but GUID's externally makes a lot of sense. I don't think the page splits are a huge issue for us, since the session table is on a filegroup that's on a ramdisk (if the DB server has to restart, in theory the ramdisk files get persisted to disk and then back. If not, logged-out users are the least of our concerns). Still, there's a lot to be said for elegance, and that kind of change could be done gradually and run side by side for some time.

Thanks
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources