July 28, 2010 at 2:44 am
Can Anyone let me know how to tune the below query?
I have also attach the sqlplan. Check all the indexes. They all are fine.
I need logical read <=5000.
Your quick response will be greatly appreicate
/*SET STATISTICS TIME ON
SET STATISTICS IO ON
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
*/
SELECT
x0_0.KeyOffering,
x0_0.KeyInstn,
x2_0.LongName,
x0_0.KeyFndg,
x0_0.IPO,
x0_0.OfferingUnderwritten,
x0_0.MutualConversion,
x0_0.ShelfOffering,
x0_0.PrivatePlacement,
x0_0.OfferingMergerRelated,
x0_0.SharesOfferedInclOverallotment,
x0_0.CompletionDate,
x0_0.TerminationDate,
x0_0.OfferingSettlementDate,
x0_0.AnnouncementDate,
x0_0.AmountOfferedInclOverallotment,
x0_0.PricingDate,
x0_0.OfferPrice,
x0_0.AmountOffered,
x0_0.AmountOfferedShareholders,
x0_0.SharesOffered,
x0_0.YieldToMaturity,
x0_0.Restricted144a,
x0_0.KeyOfferingSellerRelation,
x0_0.SubscriptionOffering,
x0_0.CurrencyEOPDate,
x6_0.StockPurchaseContract,
x1_0.Name,
x1_0.DivRate,
x1_0.RedemptionDate,
x1_0.KeyFndgType,
x0_0.KeyCurrency,
x7_0.CurrencyLongPlural,
x7_0.CurrencySymbol,
x1_0.FndgConvertible,
x3_0.Name,
x3_0.SortOrder,
x4_0.KeyConversionType,
x2_0.GAAPDomainClass,
x0_0.DeNovoBankOffering
FROM
SNL_New.dbo.Offerings x0_0
INNER JOIN SNL_New.dbo.Fndg x1_0
ON x0_0.KeyFndg = x1_0.KeyFndg
INNER JOIN SNL_New.dbo.Instn x2_0
ON x0_0.KeyInstn = x2_0.KeyInstn
LEFT JOIN Lookup.dbo.FndgType x3_0
ON x1_0.KeyFndgType = x3_0.KeyFndgType AND x3_0.updoperation < 2
LEFT JOIN SNL_new.dbo.MutualConversions x4_0
ON x0_0.KeyOffering = x4_0.KeyOffering AND x4_0.updoperation < 2
LEFT JOIN SNL_New.dbo.Instn x5_0
ON x0_0.KeyInstn = x5_0.KeyInstn AND x5_0.updoperation < 2
LEFT JOIN SNL_new.dbo.FndgConversion x6_0
ON x0_0.KeyOffering = x6_0.KeyFndg AND x6_0.updoperation < 2
LEFT JOIN Lookup.dbo.Currency x7_0
ON x0_0.KeyCurrency = x7_0.KeyCurrency AND x7_0.updoperation < 2 WHERE
((x0_0.AnnouncementDate >= '1/1/2000' OR
x0_0.CompletionDate >= '1/1/2000') AND
(x1_0.KeyFndgType NOT IN (5, 9, 8, 7, 12, 17, 18) AND
(x2_0.TreeValueRight < 654 AND
(x2_0.TreeValueLeft > 1 AND
(x1_0.FndgContinuouslyMaintained = 1 AND
x2_0.TreeID = 4041896))))) AND --100369
x0_0.updoperation < 2 AND
x1_0.updoperation < 2 AND
x2_0.updoperation < 2
ORDER BY
x2_0.LongName,
x3_0.SortOrder,
x0_0.AnnouncementDate DESC
(1129 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FndgConversion'. Scan count 1129, logical reads 2492, physical reads 3, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fndg'. Scan count 1131, logical reads 6500, physical reads 46, read-ahead reads 520, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Offerings'. Scan count 321, logical reads 2535, physical reads 4, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Instn'. Scan count 1, logical reads 8, physical reads 3, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FndgType'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MutualConversions'. Scan count 1, logical reads 11, physical reads 4, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Currency'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
July 28, 2010 at 3:40 am
Why the requirement of logical reads under 5000? That's an odd requirement for tuning. Usually one tries to get it as fast as possible, or the reads as low as possible.
How long does that take to run?
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
July 28, 2010 at 4:03 am
Query takes only 4-5 second but we have a requirment that logical read should be <=5000.
Can we use temporary table to tune the query?
I have tried creating two temporary table and use them, Logical read goes down to 4000.
I am not sure about that, whethere we should use temporary table or not?
July 28, 2010 at 4:16 am
harit79 (7/28/2010)
Query takes only 4-5 second but we have a requirment that logical read should be <=5000.
Why? Where's the logic in that?
If the query is not a performance bottleneck, you shouldn't be wasting time trying to fix it. Whether or not a query needs tuning depends on whether or not that query is currently a problem.
eg a query that runs once a day and does 45000 reads is probably far less of a problem than a query that runs once a minute and does 2000 reads.
Is 4-5 seconds an acceptable execution time for this query (it personally sounds a little high to me)? How many rows does the query return?
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
July 28, 2010 at 4:31 am
Doesn't really answer why you need one particular metric to be below an arbitrary number...
Could you post the actual execution plan, otherwise we're all guessing.
Couple of obvious things, but:
1. Could you get rid of the OR on the two dates, that will be expensive
2. Do you absolutely need Left Joins on all the lookups
3. What's with all the updoperation < 2 - do you mean updoperation = 1 or are there decimal/0/negative values that you need to take account of?
EDIT: Never mind - didn't see the execution plan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply