Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Tuning queries using tempdb

Posted on 2009-02-20
8
Medium Priority
?
709 Views
Last Modified: 2012-05-06
My customer is experiencing performance problems.  I noticed via SQL Profiler that a lot ot the SQL is using temp tables.  I had them modify one stored procedure to not use temp tables as a test. But now, they are still using select distinct.  I am not sure but it appears that the majority of the cost in the procedure is spent on the sort - see showplan for .  I definitely need to tune tempdb some - any suggestions there?  Will adding another temp data and/or log file help?  
Any other suggestions.

Thanks
SELECT DISTINCT 
c.cust_num as RETAILER_NUM, 
c.cust_1_name as RETAILER_NAME, 
c.cust_street_addr_text as RETAILER_ADDRESS, 
c.cust_city_name as RETAILER_CITY,
c.cust_state_us_postal_code as RETAILER_STATE, 
c.cust_zip_code as RETAILER_ZIPCODE, 
isnull(c.cust_phone_num,'          ') as CUST_PHONE_NUM,
'                         ' as CONTACT_NAME, 
ct.SHIP_TO_TERR_NUM as SHIP_TO_TERR_NUM, 
E.EMPL_NAME as SALES_REP_NAME
 
FROM 
	cp_nafta_dws.CUSTOMER c
JOIN 
	cp_nafta_dws.CUSTOMER_TERRITORY ct 
ON 
	ct.cust_num = c.cust_num
JOIN
	cp_nafta_dws.TERRITORY T 
ON
	ct.SHIP_TO_TERR_NUM = T.TERRITORY_NUM		
	AND T.YEAR_NUM = YEAR(GETDATE())
	AND T.TERRITORY_NAME is not null
JOIN
	cp_nafta_dws.EMPLOYEE E
ON
 	T.EMPLID = E.EMPLID
 
WHERE 
	ct.SALES_ORG_NAME IN ('CROP','POST')
	AND ct.SHIP_TO_TERR_NUM LIKE '<@some number' + '%'
	AND ISNULL(c.CUST_DELETE_FLAG, 'N') != 'Y'
go

Open in new window

showplan.xls
0
Comment
Question by:msaka
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23694766
Temp tables usage has the following advantages:

1. Stores records in temp database and hence faster access.

Disadvantages:

1. It takes more time to search or join records in temp tables when records are more as it wont use index at all. During this conditions, you can have subqueries itself which will improve your performance.

FYI, you can also created indexes in temp tables to improve your performance drastically. Try that out..
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 23694779
The sort is due to the distinct being use.  Distinct guarantees a distinct resutl set, so it uses a sort to reach it.

Temp tables are not necessarily bad if they are used in the right way.  Do you have indexes on the fields you're joining and searching on?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 23695066
Hi,

Distinct has a performance penelty, as you found from the query plan. Even if the results set is distinct anyway, SQL will still do the sort to check. Using distinct isn't wrong, but its often a flag that the developers don't understand the data, and just throw distinct at the query to make the problem go away.

That is, if on checking the distinct isn't needed, then remove it. Distinct should only be used when needed, compared to all procedures should use set no count on ... for instance

Some good hints about performance here
http://www.sql-server-performance.com/articles/per/performance_audit_part7_p1.aspx

HTH
  David
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:msaka
ID: 23697545
Thanks for the responses.  I asked the developer to ensure that the query requires distinct.  I will not find out until Monday if we can remove the distinct.  We can also try going back to using temp tables and creating indexes based on the queries that are performed - but creating indexes within the stored procedures - I would think that this would also be a performance killer.  Any thoughts about adding another data or log file to tempdb?

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23697924
David,

>>but its often a flag that the developers don't understand the data, and just throw distinct at the query to make the problem go away. <<
Absolutely.  Or if they do understand the data do not understand the concept of normalization.

>>Distinct should only be used when needed<<
There are times when I wish DISTINCT was deprecated.  There is simply no use for it and is usually a sign of an incompetent developer.  You can do the same with GROUP BY and this last is far more powerful and in some sense more structured.

Anthony
0
 
LVL 35

Expert Comment

by:David Todd
ID: 23701046
Hi,

If you have a related bunch of queries in a long procedure, often the overhead of creating the index is then recovered by the performance increase in those queries. But usually the effort of creating a useful index isn't recovered by only one or two queries. Like most things, it depends.

Anthony:
I know that group by can do a distinct, but have preferred my developers to use distinct when needed, rather than mis-using a group by to create a distinct. Just as I've gotten a cross join used when the join condition of an inner join was mangled, and actually creating a cross join.

Cheers
  David
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23705166
David,

>>rather than mis-using a group by to create a distinct.<<
I guess my point was that it is easier to misuse a DISTINCT than a GROUP BY clause.

And then of course you get the ones that use both DISTINCT and GROUP BY in the same query and the expression "unencumbered by the thought process" comes to mind. :)

Anthony
0
 

Accepted Solution

by:
msaka earned 0 total points
ID: 24031786
I added additional temp data files and added Trace Flag -T1118 to spread tempdb usage across all tempdb data files. (I was seeing 2:1:1 and 2:1:3 wait resources)  Getting the developers to modify code is another issue - one I have been unsuccessful at. so far  So, this remains an issue.  However, I am closing this question.

Thanks for your assistance.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question