Tuning queries using tempdb
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
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
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?
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?
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
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
ASKER
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
Thanks
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
>>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
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
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
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
>>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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..