question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

Global Hash Table

I am reading a functional spec for some work that I will be picking up and the author has suggested the use of a global hash table. All the info I've read has suggested that these aren't used very often and wondered if someone could explain the pros and cons of using it. I've used temporary tables many times but not global ones. Thanks
tablehashglobal
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

server version? likely row count?
0 Likes 0 ·
SQL 2000, no idea - sorry. probably 10s of thousands
0 Likes 0 ·
What does the author suggest you use the global hash table for? I can't see why/when it can be a good idea to use a global, temporary table instead of a "normal" database table.
0 Likes 0 ·
That's what I thought but wondered if anyone could enlighten me. It doesn't appear that there is any particular reason for using a hash table rather than a standard table.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
My 2 pence: If its going to hold as many rows as you suggest then it should be a fixed table in a database where it can be optimised. A Global temp table (denoted by ##tablename rather than #tablename) is accessible by all processes on a server so has some advantages of a local one - that can only be accessed by the process that created it. There arent many reason to have to do this and I have, indeed, never used one or seen one used. As this will cause your tempdb to grow when the rows are inserted it could cause you problem with space issues and database,table fragmentation. My preference: to have a table in the database you are working in that is dedicated to this function.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's great, I will put this in place.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Answered already, but I'll toss in a bit more. The other problem with a global temp table is that it has to be reloaded each time the server is restarted. Granted, that shouldn't be much of an issue under most circumstances, but it sure can be. So now you're adding to the overhead of system startup or to the code that access this global temp table where a real table would just be persisted.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image
Mark answered
I've used a global temp table once when I was using SQL 2000. I wanted to nest stored procedures, but you can nest only one procedure deep in the 2000 version (if memory serves). (Actually, I need to go back and refactor that code since we're on 2005 now, so the global table is no longer needed.)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
Maybe I'm really unique here - but I use global temp tables quite often, when I am trying to monitor the progress of a long running query. I'll create the global temp table at the start of the long running proc, and insert / update rows in it to reflect the progress of the query. For example, if i'm doing a long bit of analysis over 50 million+ rows, and i'm batching it into 100,000 row batches (just for arguments sake). It's also worth noting that global temp tables persist until all connections that have referenced them are closed. So it won't persist unless a connection that has used it is kept open... Edit -> also probably advisable not to call it a hash table - as they are something quite different :)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Absolutely. Just everyone was saying 'oh no I never use those' - so I thought I'd stick my neck out. 100% agree that it's not the tool for the job right here.
1 Like 1 ·
There are certainly uses but for tens of thousands of rows I dont think its a viable solution. We dont know the column count but it would likely bloat tempdb unnecessarily and if it drops out of memory then it will likely cause growth and frag of tempdb files when it is next created. Everything in its place and a place for everything.
0 Likes 0 ·
I guess that makes sense, but when I want to monitor a batch like that I tend to either use simple print statements or log it out to a log table which I create as a standard table until I know I no longer need it for debugging, which could be a long time later after the analysts have gone over the final product.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I have given this a little thought, and I think there's som confusion about the use of the word "global hashtable". I don't think the author means "global #table", but rather a global table for key/value pairs. Unless the author specifically states that it should be a temporary SQL Server table, I think all the author means is that the system might need a globally stored key/value-pair-repository. That's something that could be stored in SQL Server, if it needs to persist between application restarts. But I'll have to add that I'm more or less guessing here. What kind of system/application is it?
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ok. Then I can't make any sense out of it, under any other circumstances than those that @Matt Whitfield talks about.
2 Likes 2 ·
No I did mean a global temporary table.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.