PratikDagaFinal-TuningReport

PratikDagaFinal-TuningReport - G22.2434.01 fall 2009...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
G22.2434.01 fall 2009 Advanced Database Systems Report of Tuning Project By: Pratik Daga Student ID: N18669576 DEDICATED TO MY FAMILY, SYLPHY AND JACKEY Index Server Information ............................................................................................................................................... 2 Case 1: Remove cursor from procedure Procedure which calculate & store date for last trading date (30 times Improvement)…………. 4 Procedure which calculate and store data for all the trading dates (10 times) ……………………. 7 Case 2: Logical Tuning Avoiding Dead Index during Peak Hours (Performance varies) ................................................... 13 Debugging and other modifications ………………………………………………………………… 16 Case 3: Correlated Sub Queries (logical reads) .............................................................................................. 18 Case 4: Reducing Complicated Sub Queries into simpler (from 6 min to 3 sec) ...................................... 21 Appendix: DDL statement of tables and views ………………………………………………………………. . 25
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Server Information Common Network name (alias) Investarsd6800 IP External XXX.XXX.XXX.XXX CONFIDENTIAL IP Internal XXX.XXX.X.X CONFIDENTIAL CPU Xeon MP, 3Ghz X 4, 2CORE, 8MB Memory 32 Gb Slot 8X PCIE PERC 5/E Adapter Connected to MD1000 Disks C: 36Gb X 2, RAID 1 Internal Drives Disk Size: 33GB Read Policy: Adaptive Read Ahead Write Policy: Write Back Stripe Size: 16 KB E: 72Gb X 10, RAID 10 Internal Drives Disk Size: 272GB Read Policy: Adaptive Read Ahead Write Policy: Write Back Stripe Size: 64 KB J: 136Gb X 15, RAID 10 MD1000 Disk Size: 952.88GB Read Policy: Adaptive Read Ahead Write Policy: Write Back Stripe Size: 64 KB Replication
Background image of page 2
Distributor for INVESTARSD2800 Snapshot folder: E:\ReplData Subscriber of INVESTARSD2800, STRAWBERRY Quotes, InvestarsData The database Server used is SQL SERVER 2005. The front end for the Website is ASP.net. Since it’s a financial Database, it has lot of procedures and most of the them run every day or as per client requirements. CASE 1: a) Removing Cursor from procedure which calculates and store data for latest trading date:‐‐ There is no need to store calculation about all the trading dates. Simply, a last trade date calculation at the end of the cursor is needed. Before:‐‐ DECLARE cur_dates_1 CURSOR FOR SELECT DISTINCT date = tradedate FROM @dates WHERE tradedate is not null ORDER BY date OPEN cur_dates_1 FETCH FROM cur_dates_1 INTO @date WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'rebalance date: ' + CONVERT ( VARCHAR ( 10 ), @date , 1 ) IF @TotalReturn = 'Y' BEGIN UPDATE @positions SET Dividend = ISNULL ( Dividend , 0 ) + ( SELECT ISNULL ( SUM ( Dividend ), 0 ) FROM @dividends as d WHERE d . StockID = p . StockID AND d . Date > @PrevDate AND d . Date <= @Date ) FROM @positions as p END IF @RatingType = 'U' BEGIN
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 10/06/2011 for the course CS 2434 taught by Professor Shasha during the Spring '11 term at NYU.

Page1 / 29

PratikDagaFinal-TuningReport - G22.2434.01 fall 2009...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online