asked
Need SQL Query tuning solution
Hi all, Can anyone provide me simple solution for query written below:- I have written the following query to find All data fromt able dbo.RecommendationTypesTranslation where DefaultLanguage is fectched from RecommendationTypes & RecommendationProvider if there is no DefaultLanguage then fetch data for Default language "en" for RecommendationTypesID. SELECT DL.RecommendationTypesID, RTT.ShortName, RTT.LongName, DL.DefaultLanguage FROM dbo.RecommendationTypesTranslation RTT INNER JOIN ( SELECT R1.RecommendationTypesID, IsNull(DefaultLanguage, 'en') AS DefaultLanguage FROM RecommendationTypes R1 LEFT OUTER JOIN ( SELECT RT.RecommendationTypesID, DefaultLanguage AS DefaultLanguage FROM RecommendationTypes RT INNER JOIN RecommendationProvider RP on RT.Source = RP.ProviderSource INNER JOIN RecommendationTypesTranslation RTT ON RTT.RecommendationTypesID = RT.RecommendationTypesID AND RTT.LanguageCode = RP.DefaultLanguage) T ON R1.RecommendationTypesID = T.RecommendationTypesID) DL ON DL.RecommendationTypesID = RTT.RecommendationTypesID AND DL.DefaultLanguage = RTT.LanguageCode
can you post the query plan please? What indexes are there? Do all tables have PK-FK in place? Why do you think it is slow? How much faster do you want it to be - 10%, 100%, 1000%?
how big are the tables? How up-to-date are the index stats?
I might be completely wrong here, but it almost appears that the query in question was generated by some sort of tool. The downside of using those is that extra clicking and dragging tends to produce overly complicated queries. Without knowing the details about the data, it is not really possible to figure out how to best restate the query, but please try either this: select RTT.RecommendationTypesID, RTT.ShortName, RTT.LongName, isnull(RP.DefaultLanguage, 'en') DefaultLanguage from RecommendationTypesTranslation RTT left join RecommendationTypes RT on RTT.RecommendationTypesID = RT.RecommendationTypesID left join RecommendationProvider RP on RT.[Source] = RP.ProviderSource and RTT.LanguageCode = RP.DefaultLanguage; -- or even this because it is difficult to figure out the need to -- include the RecommendationProvider RP in the mix if the RTT -- already has the LanguageCode column which could be used select RTT.RecommendationTypesID, RTT.ShortName, RTT.LongName, RTT.LanguageCode DefaultLanguage from RecommendationTypesTranslation RTT left join RecommendationTypes RT on RTT.RecommendationTypesID = RT.RecommendationTypesID Oleg
