| Author |
Topic |
|
hspatil31
Posting Yak Master
182 Posts |
 Posted - 2011-09-05 : 04:53:02
|
| Dear All,I am having following query, in following query each table having more that milions records. For executing this query it's taking to much time. On tables i don't want to set any index.Can anybody please tell me how to optimize this query. Is there anyother way to optimize ?SELECT CTR.Code AS Country_Code,CTR.Name AS Country_Name,INRS.Dialed_Digits AS Dialed_Digits,CAST(INRS.Rate as numeric(8,4)) As Rate,C.Abbreviation AS Provider,CAST(RDCR.Raw_Rate as numeric(8,4)) As Raw_Rate,RDF.Overhead AS Overhead ,CAST(RDCR.Gross_Rate as numeric(8,4)) As Gross_Rate,RDF.Margin AS Margin ,CAST(RDCR.Net_Rate as numeric(8,4)) As Net_Rate,RDLCC.Rank AS RankFROM aaa INRS (nolock)INNER JOIN bbb RDLCC (nolock) ON INRS.ID = RDLCC.Normalized_Rate_Sheet_IDINNER JOIN ccc RDCR (nolock) ON RDLCC.Rate_Deck_Created_Rate_ID = RDCR.IDINNER JOIN ddd WS (nolock) ON WS.ID= INRS.Work_Sheet_IDINNER JOIN eee RS (nolock) ON WS.Rate_Sheet_ID= RS.IDINNER JOIN fff C (nolock) ON C.ID= RS.Carrier_IDINNER JOIN ggg CTR (nolock) ON INRS.Country_ID = CTR.IDINNER JOIN hhh RDF (nolock) ON RDF.Rate_Deck_Details_ID = RDCR.Rate_Deck_Details_ID AND RDF.Call_type_ID = @CallType_IDWHERE RDCR.Rate_Deck_Details_ID=@RateDeck_Detail_IDAND RDCR.Call_type_ID = @CallType_IDAND RDCR.Country_Code=@Country_Code ORDER BY Country_Code,Dialed_Digits,Rank Thanks and Ragard'sHarish Patil |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-05 : 05:08:38
|
| we need some more infodid you analyse execution plan? what are costly steps? are there some table scans? are they some large tables involved?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
hspatil31
Posting Yak Master
182 Posts |
Posted - 2011-09-05 : 05:16:14
|
| Hello Friend,Following are the tableshaing more data. These are the costly steps.And one more thing if any other operation is going on this tables then my above query result taking more time.aaa INRS (nolock)INNER JOIN bbb RDLCC (nolock) ON INRS.ID = RDLCC.Normalized_Rate_Sheet_IDINNER JOIN ccc RDCR (nolock) ON RDLCC.Rate_Deck_Created_Rate_ID = RDCR.IDPlease tell me how to solve this issue ?Thnks,Harish |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-05 : 05:30:26
|
| did you see execution plan? are there any table scans?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
gwilson67
Starting Member
42 Posts |
Posted - 2011-09-05 : 11:34:32
|
| Turn on the Actual Estimated Plan not the Estimated Plan. Based on the results, you can look to see if you can have any improvements like adding indexes.Greghttp://www.freewebstore.org/tsqlcoderepositoryPowerful tool for SQL Server development |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-05 : 13:04:46
|
| "On tables i don't want to set any index."Well pretty much all bets are off at improving it then.Do you know the consequences of using NOLOCK as a hint? Are they accommodated in your application? Or is this report NOT used for any decision making process by managers etc? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2011-09-05 : 16:56:24
|
And if you can't be bothered to read the link.Let me summarize it for you:DON"T USE NOLOCK.You can end up reading the same row twice (or not at all).quote:
On tables i don't want to set any index.
Why not? There will be no way to get adequate performance if you don't want to index anything.Do the join conditions even have keys and constraints?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
Optimized SQL Query.