April 8, 2009 at 10:18 am
bob.willsie (4/8/2009)
Perhaps people fell into that trap because some programmers don't clean up after themselves. I've come across code that continually created random named temp tables rather than creating and flushing or deleting the same tables.
Which is questionable anyway, since temp tables are automatically dropped when the connection closes or when the procedure that creates them ends, and anyway SQL hashes the names to ensure that they are unique.
Or are you talking about permanent 'temporary' tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2009 at 10:21 am
Jack Corbett (4/8/2009)
Just put everyone in db_datareader role. That's good enough security.
Don't you mean sysadmin?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2009 at 10:37 am
Oh, oh - can't believe this one's been missed so far? Or did I just bang my head on the desk 'till it went away?
"Well - it's obvious that all developers need access to the base tables. Stored Procs just slow things down .... "
Do I get a prize for bringing that one up?
April 8, 2009 at 11:04 am
GilaMonster (4/8/2009)
Jack Corbett (4/8/2009)
Just put everyone in db_datareader role. That's good enough security.Don't you mean sysadmin?
I'm actually seeing more recognition that everyone in sysadmin is the wrong way to go, but more of I'll just put everyone in the db_datareader and db_datawriter roles. You don't see system administrations granting read/write persmissions to everyone on network shares, why would we do it in SQL Server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2009 at 11:29 am
DBA's have the assumption that all developers are bad for a database or bad SQL programmers. Well, I am a develepor cum DBA but before I had to operate as a DBA I am sure I wrote some bad queries.So, the advise that I want to give DBA's is that you should rather try and teach the developers about performance than to try and keep them out of a database. When I started as DBA I was amazed to see all the wonderful tools there is to tune your query. Yes, my training was maybe not concentrated on performance tuning and what you can do to tune your query.
The other day I sat with a query that I did not write from the start myself but I had changed some of it through the years and it kept giving me a time-out in Access. There were about four subqueries in it and when I did the execution plan it showed me that the subqueries performing badly. What I am trying to say is, sometimes it is not bad developers but insufficient training.
My other assumption is "don't pass the buck".... oh no! that is not an assumption it's a fact!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
April 8, 2009 at 11:36 am
GSquared (4/8/2009)
On the "temp tables are bad" thing, the coding requirements for the place I work state that table variables should be used instead of temp tables, and that temp tables need to be created first, then populated, if they absolutely must be used. The first is because table variables are in RAM and temp tables are on disk, the second is because of locks on system tables. Both are wrong (it's all SQL 2005, since that does make a difference).Temp tables did have problems in SQL 7.5. Not really since 2000.
Which leads to one of the really bad assumptions: "If something was ever true, it must be true forever." And a corollary: "I already studied that subject, so I don't need to re-study it, ever."
Actually, if memory serves, in SQL Server 2005, table variables are stored in TempDB as well. In SQL Server 2000 they were stored in RAM. There's another incorrect generalization.
"Beliefs" get in the way of learning.
April 8, 2009 at 11:48 am
Manie Verster (4/8/2009)
DBA's have the assumption that all developers are bad for a database or bad SQL programmers. Well, I am a develepor cum DBA but before I had to operate as a DBA I am sure I wrote some bad queries.So, the advise that I want to give DBA's is that you should rather try and teach the developers about performance than to try and keep them out of a database. When I started as DBA I was amazed to see all the wonderful tools there is to tune your query. Yes, my training was maybe not concentrated on performance tuning and what you can do to tune your query.
But with Developer Edition, there just no good reason why every developer cannot have their own sandbox/playpen that they can be fully privileged on. Then at the same time the DBA's can restrict them to DBA in the shared DEV enviroments, Read-Only in QA and no access at all to Production.
This gives everyone what they need or want with very little cause for objection.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2009 at 11:55 am
Robert Frasca (4/8/2009)
GSquared (4/8/2009)
On the "temp tables are bad" thing, the coding requirements for the place I work state that table variables should be used instead of temp tables, and that temp tables need to be created first, then populated, if they absolutely must be used. The first is because table variables are in RAM and temp tables are on disk, the second is because of locks on system tables. Both are wrong (it's all SQL 2005, since that does make a difference).Temp tables did have problems in SQL 7.5. Not really since 2000.
Which leads to one of the really bad assumptions: "If something was ever true, it must be true forever." And a corollary: "I already studied that subject, so I don't need to re-study it, ever."
Actually, if memory serves, in SQL Server 2005, table variables are stored in TempDB as well. In SQL Server 2000 they were stored in RAM. There's another incorrect generalization.
That was the whole point of what I wrote. They are handled the same way as far as RAM vs disk.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 12:30 pm
My favorite is "assuming programmers will build what I ask them to"
Falls down almost every single time.
Great job, Phil!
April 8, 2009 at 1:35 pm
Robert Frasca (4/8/2009)
Actually, if memory serves, in SQL Server 2005, table variables are stored in TempDB as well. In SQL Server 2000 they were stored in RAM. There's another incorrect generalization.
Not at all. Even back in SQL 2000, table variables could spill to TempDB if needed and both temp tables and table variables were kept in memory as much as possible
http://support.microsoft.com/kb/305977
From above kb article:
Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2009 at 1:53 pm
Manie Verster (4/8/2009)
DBA's have the assumption that all developers are bad for a database or bad SQL programmers. Well, I am a develepor cum DBA but before I had to operate as a DBA I am sure I wrote some bad queries.. What I am trying to say is, sometimes it is not bad developers but insufficient training.
My other assumption is "don't pass the buck".... oh no! that is not an assumption it's a fact!
From what I've seen, all the top bods on this site seem to have come from the same type of background. Oh, and me. BUT, quite frankly, there does seem to be a significant issue with a certain type of developer who because they can bung in "SELECT * FROM SomeTable" into a bit of c# code, think they know the lot about the database side, irrespective of actual clue level.
Even worse, when you attempt to explain to them - well, they're not interested. I have no idea why someone not interested in learning wants to go into coding - but it does seem to be becoming worryingly common.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 8, 2009 at 1:57 pm
GSquared (4/8/2009)
Robert Frasca (4/8/2009)
GSquared (4/8/2009)
On the "temp tables are bad" thing, the coding requirements for the place I work state that table variables should be used instead of temp tables, and that temp tables need to be created first, then populated, if they absolutely must be used. The first is because table variables are in RAM and temp tables are on disk, the second is because of locks on system tables. Both are wrong (it's all SQL 2005, since that does make a difference).Temp tables did have problems in SQL 7.5. Not really since 2000.
Which leads to one of the really bad assumptions: "If something was ever true, it must be true forever." And a corollary: "I already studied that subject, so I don't need to re-study it, ever."
Actually, if memory serves, in SQL Server 2005, table variables are stored in TempDB as well. In SQL Server 2000 they were stored in RAM. There's another incorrect generalization.
That was the whole point of what I wrote. They are handled the same way as far as RAM vs disk.
Aye - but a KEY point that Gila (thanks mate) pointed out to me is that you have no stats on a tablevar's index (pk only IIRC) wheras stats are created for all indexes created on a temp table. Mkes a lot of difference if you're dealing with non-trivial datasets.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 8, 2009 at 2:06 pm
andrew gothard (4/8/2009)
Manie Verster (4/8/2009)
DBA's have the assumption that all developers are bad for a database or bad SQL programmers. Well, I am a develepor cum DBA but before I had to operate as a DBA I am sure I wrote some bad queries.. What I am trying to say is, sometimes it is not bad developers but insufficient training.
My other assumption is "don't pass the buck".... oh no! that is not an assumption it's a fact!
From what I've seen, all the top bods on this site seem to have come from the same type of background. Oh, and me. BUT, quite frankly, there does seem to be a significant issue with a certain type of developer who because they can bung in "SELECT * FROM SomeTable" into a bit of c# code, think they know the lot about the database side, irrespective of actual clue level.
Even worse, when you attempt to explain to them - well, they're not interested. I have no idea why someone not interested in learning wants to go into coding - but it does seem to be becoming worryingly common.
My pre-DBA background includes management/executive, sales, marketing, proofreading/editing, typesetting, and I was a file clerk for a while. I'm not sure that comes under the heading of "same type of background"... 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 2:08 pm
andrew gothard (4/8/2009)
GSquared (4/8/2009)
Robert Frasca (4/8/2009)
GSquared (4/8/2009)
On the "temp tables are bad" thing, the coding requirements for the place I work state that table variables should be used instead of temp tables, and that temp tables need to be created first, then populated, if they absolutely must be used. The first is because table variables are in RAM and temp tables are on disk, the second is because of locks on system tables. Both are wrong (it's all SQL 2005, since that does make a difference).Temp tables did have problems in SQL 7.5. Not really since 2000.
Which leads to one of the really bad assumptions: "If something was ever true, it must be true forever." And a corollary: "I already studied that subject, so I don't need to re-study it, ever."
Actually, if memory serves, in SQL Server 2005, table variables are stored in TempDB as well. In SQL Server 2000 they were stored in RAM. There's another incorrect generalization.
That was the whole point of what I wrote. They are handled the same way as far as RAM vs disk.
Aye - but a KEY point that Gila (thanks mate) pointed out to me is that you have no stats on a tablevar's index (pk only IIRC) wheras stats are created for all indexes created on a temp table. Mkes a lot of difference if you're dealing with non-trivial datasets.
The assumption I was talking about is a false one that table variables are better than temp tables. I've run into that one quite a few times. Indexes, stats, transactional control, the ability to pass access to them to procs called from the proc/script that created them, these are all valid differences between temp tables and table variables. The old "table variables are faster because they're in RAM" is a very, very common false datum.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 2:10 pm
Manie Verster (4/8/2009)
DBA's have the assumption that all developers are bad for a database or bad SQL programmers.
that's a common assumption about DBA's in itself Manie 🙂
---------------------------------------------------------------------
Viewing 15 posts - 46 through 60 (of 81 total)
You must be logged in to reply to this topic. Login to reply