May 7, 2009 at 10:39 am
Hi All,
I was just wondering where SQL 2005 could help here in terms of improvements and new features, I do notice that a lot of my stored procedures, pull data into a temp table from production tables, does some operations on it (joins, updates) etc and then the data is then pushed out to the client.
Is there a better way to do this, as I’m just considering I/O contention issues here on the tempdb and also performance considerations as well.
Another this is, is it good development practice to always select distinct from a table, as my feeling is that, if the table is properly normalized, structured and free of duplicates, then there is no real need to have select distinct all the time.
Any ideas here.
May 7, 2009 at 11:04 am
Dean Jones (5/7/2009)
I was just wondering where SQL 2005 could help here in terms of improvements and new features, I do notice that a lot of my stored procedures, pull data into a temp table from production tables, does some operations on it (joins, updates) etc and then the data is then pushed out to the client.
Are the temp tables really necessary, or can the operations all be done in one step?
Sometimes using temp tables for intermediate results in complex queries is faster, sometimes doing it all in one query is faster. Only way to tell is test.
Another this is, is it good development practice to always select distinct from a table
It is very bad development practice to always use DISTINCT. It's an expensive operation. It shouldn't be needed unless there's a problem with the query, the data or the data design
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
May 7, 2009 at 11:15 am
Gail is absolutely right: there are many ways to pull data and testing is the only way to see what is better for you. Also, never ever use SELECT DISTINCT if you want good performance.
In my practice I’ve seen that memory table variable sometimes works better than tempdb. Even permanent table is better sometimes. It all depends on amount of data. Here is a few rules:
-Memory table variable creates table in tempdb if it can’t hold all data in memory;
-Usage of tempdb is slow because new table is created every time you are using tempdb;
-Be very careful with permanent table because several users/queries/sessions can use the same permanent table.
Alex Prusakov
May 7, 2009 at 11:53 am
Since table variables are also created in tempdb, and follow the same rules on using memory, their only speed advantage is that they don't write to the transaction log. This is more often than not defeated by the speed advantages of temp tables (like indexes and stats).
I would definitely take a look at getting rid of as many Distinct operations as possible. Test the heck out of it in a dev environment, because they may have been put in there to compensate for really bad database design, in which case you can't just take them out.
- 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
May 7, 2009 at 11:54 am
Alex Prusakov (5/7/2009)
Also, never ever use SELECT DISTINCT if you want good performance.
I wouldn't say never. There are times when it's needed, when you do really need distinct data from a column/columns that contain duplicates. They are the exception, not the norm.
In my practice I’ve seen that memory table variable sometimes works better than tempdb.
Sometimes. Very seldom though. There are a number of downsides with table variables. One is that they can't have indexes other than pk and unique constraints. Other is that they don't have statistics. Net result of the two is likely to be very poor performance if they contain lots of rows.
-Memory table variable creates table in tempdb if it can’t hold all data in memory;
That's incorrect.
Table variables are treated exactly the same as Temp tables. They are created in tempDB, they have entries in the system tables, they are assigned space in the database. Both temp tables and table variables are kept in memory as much as possible because SQL knows that they will be used shortly. In cases of memory pressure, they will be spilled to disk in tempDB.
-Usage of tempdb is slow because new table is created every time you are using tempdb;
SQL 2005 introduced the concept of temp table caching where, when a temp table is dropped, it's entries in the system tables are not discarded and a single IAM and data page is kept allocated. Net result is that repeated creation and dropping of temp tables has much less of an effect on TempDB than it did in SQL 2000
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
May 7, 2009 at 3:35 pm
Thanks Guys
Here is the situation, a temp table is created
data is inserted into the temp table
Sp's are called which use this temp table (sometimes)
On other occasions, data is simply selcted from the temp table and wrapped around XML using some other programming language
xML is then displayed on the web
Any areas for improvements here, I'm thinking would Common table expressions do a good job, not too sure on they way they work and performance as well.
May 7, 2009 at 4:31 pm
Another this is, is it good development practice to always select distinct from a table, as my feeling is that, if the table is properly normalized, structured and free of duplicates, then there is no real need to have select distinct all the time.
You are correct. It isn't good development practice to ALWAYS code distinct. Do so only as needed, and don't do it without a very specific objective in mind.
The use of temporary tables is another matter. The answer here is "it depends". Sometimes use of a temporary table can actually speed up a complex query. However, this does not mean using a temp table to avoid doing a JOIN.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 7, 2009 at 4:39 pm
So what are the alternatives to usinga temp tables, or is it just to always writing the possible complex query off the tables on the flay all the time.
May 7, 2009 at 5:04 pm
Typically I try to make things happen in a single query, trying for a single pass through the table(s). If it doesn't seem to be running very fast, then I consider whether or not a temp table would improve it.
Instead of temp tables you can use subqueries, table variables (for small numbers of rows), common table expressions (more commonly known as CTEs), and inline table valued functions. However tempdb gets used a lot. Table variables go there just like temp tables. A good practice is to put tempdb on separate, and very fast, disk resources apart from your own databases.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 7, 2009 at 5:30 pm
true talk here
but sometimes, the Query can look long winded and kind of long, and hence the reason why its been put into a temp table.
May 7, 2009 at 5:48 pm
Dean Jones (5/7/2009)
true talk herebut sometimes, the Query can look long winded and kind of long, and hence the reason why its been put into a temp table.
That's not a good reason to use a temp table. True, it's a natural tendency of developers, but's not a good reason to do it in SQL.
CTE's can alleviate this tendency considerably because they make it easier to break up the code into understandable pieces, without necessarily logically or physically separating those pieces.
[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]
May 8, 2009 at 1:54 am
Dean Jones (5/7/2009)
true talk herebut sometimes, the Query can look long winded and kind of long, and hence the reason why its been put into a temp table.
Long winded and kind of long do not automatically mean slow. Sometimes when such a query is slow, it's because of poor indexing, or odd query constructs, and temp tables don't fix that.
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
May 8, 2009 at 6:50 am
Dean, I recently saw a query where the developer used a temp table to break something down into steps. The result was to run at least three different queries against the source table, with each query updating a single column in the temp table. This could have been accomplished with one pass and some medium-weight CASE statements, and avoided the overhead of creating and updating the temp table multiple times. If a single query seems overwhelming to look at, CTEs are a wonderful way to help SQL programmers break down their thinking to where it appears step by step. Building a temp table makes sense if you want data indexed in a particular fashion before a join, or if you need the results to persist to some later query.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 8, 2009 at 8:44 am
Dean,
If you can solve the problem possibly in one table pass with appropriate indexes temp tables are likely not needed.
If your tables are heavily modified temp tables aleviate locking/blocking/deadlocking issues.
The above rules should help you in reaching a decision.
Note: There is nothing written in stone when it comes to development, you have to "TEST".
Good luck
* Noel
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply