Re: SQL tuning

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Uri Dimant (urid_at_iscar.co.il)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 14:10:06 +0200

Nesaar
It is hard to suggest something , so have you looked at execution plan of
the query? Does SQL Server optimizer use indexes to perfom the query?
I see you are using a variable in the query.Is it stored procedure that
accept a parameter? It might be that otpimizer uses a 'bad' execution plan
which based on preveious variable.

"Nesaar" <nbanderker@fairbairncapital.com> wrote in message
news:uvKHo4fUEHA.3988@tk2msftngp13.phx.gbl...
> Hi. Does anyone perhaps know of a way to re-write this query to perhaps
make
> it a bit faster. It used to be a single select statement but then i had to
> break it up to cater for specific exclusions. I have added indexes and so
> on. Any ideas?
>
> Thanks
> N
>
> SELECT qrt.QuarterlyClientID, qrt.ClientID, qrt.zPolicyTypeID,
> SUM(qrt.TxnAmount),
> CAST( (DATEDIFF(DAY, qrt.TxnDate, @QuarterCloseDate)) as float)/365.25
> FROM dbo.QuarterlyRunPortfolio qrp (NOLOCK) INNER JOIN
> QuarterlyRunPolicyTransaction qrt (NOLOCK)
> ON (qrp.QuarterlyClientID = qrt.QuarterlyClientID
> AND qrp.ClientID = qrt.ClientID
> AND qrp.zPolicyTypeID = qrt.zPolicyTypeID)
> INNER JOIN dbo.zFund f (NOLOCK)
> ON (qrt.zFundID = f.zFundID
> AND ISNULL(f.LoanFund, 'N') = 'N')
> WHERE qrt.SortOrder NOT IN (5,6,7,8,9)
> AND UPPER(LEFT(qrp.ClientId,1)) = 'A'
> GROUP BY qrt.QuarterlyClientID, qrt.ClientID, qrt.zPolicyTypeID, TxnDate
> HAVING SUM(qrt.TxnAmount) <> 0
> --
> UNION
> ---
> -- Process all the IF clients and exclude the dividend and the initial ut
> fee associated with that dividend distribution
> SELECT qrt.QuarterlyClientID, qrt.ClientID, qrt.zPolicyTypeID,
> SUM(qrt.TxnAmount),
> CAST( (DATEDIFF(DAY, qrt.TxnDate, @QuarterCloseDate)) as float)/365.25
> FROM dbo.QuarterlyRunPortfolio qrp (NOLOCK) INNER JOIN
> QuarterlyRunPolicyTransaction qrt (NOLOCK)
> ON (qrp.QuarterlyClientID = qrt.QuarterlyClientID
> AND qrp.ClientID = qrt.ClientID
> AND qrp.zPolicyTypeID = qrt.zPolicyTypeID)
> INNER JOIN dbo.zFund f (NOLOCK)
> ON (qrt.zFundID = f.zFundID
> AND ISNULL(f.LoanFund, 'N') = 'N')
> WHERE NOT EXISTS (SELECT * FROM
> FairbairnReporting.dbo.IFDividendDistribution ifd
> WHERE qrt.TxnDate = ifd.InvestmentDate
> AND qrt.zProductId = ifd.zProductId
> AND qrt.zFundId = ifd.zFundId
> AND qrt.PolicyId = ifd.PolicyId
> AND qrt.TxnAmount = ifd.Amount)
> AND NOT EXISTS (SELECT *
> FROM FairbairnReporting.dbo.IFInitialUnitTrustFee ifu
> INNER JOIN FairbairnReporting.dbo.IFDividendDistribution ifd1
> ON ifu.InvestmentDate = ifd1.InvestmentDate
> AND ifu.zProductId = ifd1.zProductId
> AND ifu.zFundId = ifd1.zFundId
> AND ifu.PolicyId = ifd1.PolicyId
> WHERE qrt.TxnDate = ifu.InvestmentDate
> AND qrt.zProductId = ifu.zProductId
> AND qrt.zFundId = ifu.zFundId
> AND qrt.PolicyId = ifu.PolicyId
> AND qrt.TxnAmount = ifu.Amount)
> AND UPPER(LEFT(qrp.ClientId,1)) != 'A'
> GROUP BY qrt.QuarterlyClientID, qrt.ClientID, qrt.zPolicyTypeID, TxnDate
> HAVING SUM(qrt.TxnAmount) <> 0
>
>

Relevant Pages

  • Re: DTE recommends an index that already exists but with a different .
    ... Unit - Your first column in the existing index is only give a "not equal" compare, which means that everything except that 1 value will be included. ... then another index would be more useful to your query. ... FROM MS2.dbo.Transactions AS T WITH (NOLOCK) ... INNER JOIN MS2.dbo.TransactionItems AS TI WITH ON T.Unit = TI.Unit ...
    (microsoft.public.sqlserver.tools)
  • Performance Problem
    ... FROM dbo.Task T (NOLOCK) ... INNER JOIN dbo.StateMaster ON StateMaster.Id = WO.StatusId ... WorkOrder table is master table which consists of 155986 rows. ... For executing the above query it is taking 1.7 sec. ...
    (comp.databases.ms-sqlserver)
  • Re: NOLOCK
    ... INNER JOIN Event E ON A.EventID = E.EventID WITH (NOLOCK) ... Pro SQL Server 2000 Database Design - ... >>> From the query, it looked like he had NOLOCK on one table, but not all. ...
    (microsoft.public.sqlserver.programming)
  • Re: Query execution plan different between production/test - same
    ... Tweaked Query and execution plan on Prod server that runs fast: ... INNER JOIN ShopFloor.dbo.vShifts Shifts ON Operations.vchOperation = Shifts.vchOperation AND Shifts.vchOperation'DEVELOPMENT' ...
    (microsoft.public.sqlserver.server)
  • Re: Strange Query Behavior
    ... When you save a query, Access saves an execution plan with it. ... I have a query based on 5 tables with one inner join and 3 outer joins in ... so I imported the original query from a backup copy of the FE. ...
    (microsoft.public.access.queries)