Hello,
I have configured replication in a test lab using three identical two processor based servers. One server is being used as the distributor. I am running an application on a fourth machine(laptop) that is directly connected to the Source Server. This appli
cation inserts records into the source database at a designated rate. when I query against the source and target tables simultaneaously, there is huge latency in the number of records that is being replicated (Target is minutes behind the source). I have
modified the the log agent and distribution agent properties (polling interval, the maxbcpthreads, etc.). After doing that, I did notice some improvement but it is not enough. I need to have the two database synchronized within seconds of each other. Is t
here any other modifications that I can make to speed up the synchronization of data? By the time I have inserted 10K records into the source, the target has only half 7K.
it is possible that you are loading your server too much. Latency is a
function of throughput, the more through put you put on your system the
greater your overall latency.
Can you run this command in your distribution database so we can get an idea
of undelivered commands?
select * from MSdistribution_status
Also can you check to see if you can use the replication of stored
procedure's execution. This can radically improve performance.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:7B6AC640-D49F-4EB7-8EBF-4C394D6D0F5F@.microsoft.com...
> Hello,
> I have configured replication in a test lab using three identical two
processor based servers. One server is being used as the distributor. I am
running an application on a fourth machine(laptop) that is directly
connected to the Source Server. This application inserts records into the
source database at a designated rate. when I query against the source and
target tables simultaneaously, there is huge latency in the number of
records that is being replicated (Target is minutes behind the source). I
have modified the the log agent and distribution agent properties (polling
interval, the maxbcpthreads, etc.). After doing that, I did notice some
improvement but it is not enough. I need to have the two database
synchronized within seconds of each other. Is there any other modifications
that I can make to speed up the synchronization of data? By the time I have
inserted 10K records into the source, the target has only half 7K.
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
performance question
I've got 3 functions. one that calculates a value, and two other ones that
return a value based on the value returned by the first.
I use these in a view
example:
select
dbo.calcvalue(t.[id]) as value,
dbo.calc1(calcvalue(t.[id]) )) as val1,
dbo.calc2(calcvalue(t.[id]) )) as val2
from
tabletest t
will calcvalue() get called 3 times? or would it be more efficient to use
two views.
example:
view 1:
select
dbo.calcvalue(t.[id]) as value
from
tabletest t
view 2:
select
dbo.calc1(value) as val1,
dbo.calc2(value) as val2
from
view1user defined functions must be deterministic - give the same output for the
same input. I think one reason this restriction is there is so that sql can
eliminate performing second and third calls to your function. That said, I
don't know if it will call it more than once. You can insure it won't by
using a derived table.
select t.id, value, dbo.calc1(value) as val1, dbo.calc2(value) as val2
from
(
select id, calcvalue(id) as value
from tabletest
) as t
Jeremy wrote:
>I've got 3 functions. one that calculates a value, and two other ones that
>return a value based on the value returned by the first.
>I use these in a view
>example:
>select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
>from
> tabletest t
>will calcvalue() get called 3 times? or would it be more efficient to use
>two views.
>example:
>view 1:
>select
> dbo.calcvalue(t.[id]) as value
>from
> tabletest t
>view 2:
>select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
>from
> view1
--
Message posted via http://www.sqlmonster.com|||Jeremy,
The rules for scalar UDFs allow for many types of optimization,
including substitution. However, a simple test will show that both SQL
Server 2000 and SQL Server 2005 will not reuse the result of the UDF for
the same row.
So if you have an expensive dbo.calcvalue, and a very cheap dbo.calc1,
then the query
select dbo.calcvalue(id), dbo.calc1(dbo.calcvalue(id)) from t
will need almost twice as long to finish when compared to
select dbo.calcvalue(id), dbo.calc1(id) from t
--
Gert-Jan
Jeremy wrote:
> I've got 3 functions. one that calculates a value, and two other ones that
> return a value based on the value returned by the first.
> I use these in a view
> example:
> select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
> from
> tabletest t
> will calcvalue() get called 3 times? or would it be more efficient to use
> two views.
> example:
> view 1:
> select
> dbo.calcvalue(t.[id]) as value
> from
> tabletest t
> view 2:
> select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
> from
> view1
return a value based on the value returned by the first.
I use these in a view
example:
select
dbo.calcvalue(t.[id]) as value,
dbo.calc1(calcvalue(t.[id]) )) as val1,
dbo.calc2(calcvalue(t.[id]) )) as val2
from
tabletest t
will calcvalue() get called 3 times? or would it be more efficient to use
two views.
example:
view 1:
select
dbo.calcvalue(t.[id]) as value
from
tabletest t
view 2:
select
dbo.calc1(value) as val1,
dbo.calc2(value) as val2
from
view1user defined functions must be deterministic - give the same output for the
same input. I think one reason this restriction is there is so that sql can
eliminate performing second and third calls to your function. That said, I
don't know if it will call it more than once. You can insure it won't by
using a derived table.
select t.id, value, dbo.calc1(value) as val1, dbo.calc2(value) as val2
from
(
select id, calcvalue(id) as value
from tabletest
) as t
Jeremy wrote:
>I've got 3 functions. one that calculates a value, and two other ones that
>return a value based on the value returned by the first.
>I use these in a view
>example:
>select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
>from
> tabletest t
>will calcvalue() get called 3 times? or would it be more efficient to use
>two views.
>example:
>view 1:
>select
> dbo.calcvalue(t.[id]) as value
>from
> tabletest t
>view 2:
>select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
>from
> view1
--
Message posted via http://www.sqlmonster.com|||Jeremy,
The rules for scalar UDFs allow for many types of optimization,
including substitution. However, a simple test will show that both SQL
Server 2000 and SQL Server 2005 will not reuse the result of the UDF for
the same row.
So if you have an expensive dbo.calcvalue, and a very cheap dbo.calc1,
then the query
select dbo.calcvalue(id), dbo.calc1(dbo.calcvalue(id)) from t
will need almost twice as long to finish when compared to
select dbo.calcvalue(id), dbo.calc1(id) from t
--
Gert-Jan
Jeremy wrote:
> I've got 3 functions. one that calculates a value, and two other ones that
> return a value based on the value returned by the first.
> I use these in a view
> example:
> select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
> from
> tabletest t
> will calcvalue() get called 3 times? or would it be more efficient to use
> two views.
> example:
> view 1:
> select
> dbo.calcvalue(t.[id]) as value
> from
> tabletest t
> view 2:
> select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
> from
> view1
Performance Question
Hi there,
In a SP, I want to update table A based on values in table
B after data manipulation.
Which of the following option is better in the performance
point of view.
(1) Using Cursor
(2) Using 'table' datatype to hold one table
(3) Using temporary table instead of cursor.
Is there any other better approach exist?
Can 'table' datatype be used in 'Execute SQLTask' in DTS?
TIA,
HariHi
I would go with (2) but If you post DDL+ sample data + expected result it
will be more easily to olve the problem
Also consider
UPDATE tableA SET col=b.col1 FROM
tableB b JOIN tableA a on b.pk=a.pk
"sqlprogrammer" <anonymous@.discussions.microsoft.com> wrote in message
news:097901c397bb$25543520$a401280a@.phx.gbl...
> Hi there,
> In a SP, I want to update table A based on values in table
> B after data manipulation.
> Which of the following option is better in the performance
> point of view.
> (1) Using Cursor
> (2) Using 'table' datatype to hold one table
> (3) Using temporary table instead of cursor.
> Is there any other better approach exist?
> Can 'table' datatype be used in 'Execute SQLTask' in DTS?
> TIA,
> Hari|||I would also go for table datatype. But if you can share your code with us
then we can try to give you a better solution ... Did you look at using the
following syntax:
Update <TableA>
Set Col1 = <TableB>.Col1
Where <tableA>.id = <TableB>.id
something on these lines if you want to update a table comparing the values
from another table ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"sqlprogrammer" <anonymous@.discussions.microsoft.com> wrote in message
news:097901c397bb$25543520$a401280a@.phx.gbl...
> Hi there,
> In a SP, I want to update table A based on values in table
> B after data manipulation.
> Which of the following option is better in the performance
> point of view.
> (1) Using Cursor
> (2) Using 'table' datatype to hold one table
> (3) Using temporary table instead of cursor.
> Is there any other better approach exist?
> Can 'table' datatype be used in 'Execute SQLTask' in DTS?
> TIA,
> Hari
In a SP, I want to update table A based on values in table
B after data manipulation.
Which of the following option is better in the performance
point of view.
(1) Using Cursor
(2) Using 'table' datatype to hold one table
(3) Using temporary table instead of cursor.
Is there any other better approach exist?
Can 'table' datatype be used in 'Execute SQLTask' in DTS?
TIA,
HariHi
I would go with (2) but If you post DDL+ sample data + expected result it
will be more easily to olve the problem
Also consider
UPDATE tableA SET col=b.col1 FROM
tableB b JOIN tableA a on b.pk=a.pk
"sqlprogrammer" <anonymous@.discussions.microsoft.com> wrote in message
news:097901c397bb$25543520$a401280a@.phx.gbl...
> Hi there,
> In a SP, I want to update table A based on values in table
> B after data manipulation.
> Which of the following option is better in the performance
> point of view.
> (1) Using Cursor
> (2) Using 'table' datatype to hold one table
> (3) Using temporary table instead of cursor.
> Is there any other better approach exist?
> Can 'table' datatype be used in 'Execute SQLTask' in DTS?
> TIA,
> Hari|||I would also go for table datatype. But if you can share your code with us
then we can try to give you a better solution ... Did you look at using the
following syntax:
Update <TableA>
Set Col1 = <TableB>.Col1
Where <tableA>.id = <TableB>.id
something on these lines if you want to update a table comparing the values
from another table ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"sqlprogrammer" <anonymous@.discussions.microsoft.com> wrote in message
news:097901c397bb$25543520$a401280a@.phx.gbl...
> Hi there,
> In a SP, I want to update table A based on values in table
> B after data manipulation.
> Which of the following option is better in the performance
> point of view.
> (1) Using Cursor
> (2) Using 'table' datatype to hold one table
> (3) Using temporary table instead of cursor.
> Is there any other better approach exist?
> Can 'table' datatype be used in 'Execute SQLTask' in DTS?
> TIA,
> Hari
Monday, March 12, 2012
Performance penalty for LIKE when I really mean Equal?
Hi,
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:
> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>
I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.
I was thinking of having one parameterized SQL statement like this...
SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.
Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is th
e
equivalent of = 'OT'?
Thanks.
BBMFor prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.
ML|||If @.Type is NULL, then the following will return all rows without evaluating
the like comparison:
SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
"BBM" <bbm@.bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
> Hi,
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
> I was thinking of having one parameterized SQL statement like this...
> SELECT * FROM T1 WHERE T1.TYPE LIKE @.Type
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
> Thanks.
> BBM|||Thanks for the tip. I'll try this.
"JT" wrote:
> If @.Type is NULL, then the following will return all rows without evaluati
ng
> the like comparison:
> SELECT * FROM T1 WHERE (@.Type Is Null) or (T1.TYPE LIKE @.Type)
>
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@.microsoft.com...
>
>
performance optimization of search query
I am facing some performance issues in a Stored Procedure. The procedure
needs to return a resultset based on some search criteria. There are around
20 possible search criteria. Below is the SQL query used in my Stored
procedure. Any help to optimize the search will be great:
needs to return a resultset based on some search criteria. There are around
20 possible search criteria. Below is the SQL query used in my Stored
procedure. Any help to optimize the search will be great:
--get LOV details in table variables
INSERT INTO @.tblLov (LovCode, LovDesc, ParamCode)
SELECT LovCode, LovDesc, ParamCode FROM tp_Lov WITH (NOLOCK)
WHERE ParamCode IN('FileSrc', 'CommTrailInd', 'CommTxnStatus',
'AgencyPrincipalInd','ProdSubType','AuditTransStatus')
--get commission transaction according to the search criteria
INSERT INTO @.tblSearchResults
SELECT l1.LovDesc AS TransSource,
l2.LOVDesc AS CommTrailInd,
r.RemitCode as RemitNumber,
t.IntTransId as TransNumber,
CONVERT(VARCHAR, t.TrdDt, 110) AS TradeDate,
CONVERT(VARCHAR, t.SettlementDt, 110) AS SettlementDate,
rp.RepCode,
(ISNULL(rp.LstNm,'') + ', ' + ISNULL(rp.FstNm,'')) AS RepName,
(CASE WHEN ISNULL(t.IntClntId,0)=0
THEN ISNULL(t.ClntShortNM, '') +
(CASE WHEN (t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA')) AND ISNULL(t.FundAcctNum,'')<>'' THEN ' - ' +
ISNULL(t.FundAcctNum,'')
WHEN (t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'')
= 'VA')) AND ISNULL(t.PolicyNum,'')<>'' THEN ' - ' + ISNULL(t.PolicyNum,'')
WHEN t.TransSrc IN('PSH','MSR') AND ISNULL(t.ClrHouseAcctNum,'')<>'' THEN
' - ' + ISNULL(t.ClrHouseAcctNum,'')
ELSE '' END)
ELSE dev.udf_COMM_PCD_GetClientName(t.IntClntId, t.IntTransId)
END) AS Client,
(CASE WHEN ISNULL(t.CUSIP,'')='' THEN t.ProdName ELSE p.ProdNm END) AS
[Product],
t.InvAmt AS InvestmentAmt,
t.GDC AS GDC,
t.ClrChrg AS ClearingCharge,
t.NetComm AS NetCommission,
(CASE WHEN t.Status IN(@.strLov_TxnStatus_Tobepaid, @.strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutRateString(t.IntTransId) ELSE '' END) AS
PayoutRate,
(CASE WHEN t.Status IN(@.strLov_TxnStatus_Tobepaid, @.strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutAmountString(t.IntTransId) ELSE '' END) AS
Payout,
l3.LOVDesc AS TransStatus,
t.Comments,
t.OrderMarkup AS BDMarkup,
t.IntTransId,
rp.IntRepId,
sch.SchCode,
t.IntClntId,
t.CUSIP,
t.RepIdValue AS RepAlias,
t.RepIdType,
t.SplitInd,
l4.LOVDesc AS AgencyPrincipalInd,
t.AgencyPrincipalFee,
t.EmployeeTradeInd,
t.ShareMarkup,
t.UnitsTraded,
s.SponsorNm,
CASE WHEN t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA') THEN ISNULL(t.FundAcctNum,'') --Production
Defect #873 & 877
WHEN t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') =
'VA') THEN ISNULL(t.PolicyNum,'')
ELSE t.ClrHouseAcctNum END,
CASE WHEN ISNULL(t.ProdSubType,'') IN ('', 'Z') THEN 'Not Defined'
ELSE l6.LovDesc END AS ProdSubType, --t.ProdSubType,
l5.LOVDesc AS TransAuditStatus, --t.TransAuditStatus,
t.TransAuditStatus AS TransAuditStatusCode,
t.OriginalTransId,
t.RowId,
t.Status,
t.intParentTransId,
t.CancelTrdInd,
t.ClrChrgOverrideInd,
9999 AS AuditKey
FROM tr_CommTrans t WITH (NOLOCK)
INNER JOIN @.tblLov l1 ON t.TransSrc = l1.LOVCode and l1.ParamCode = 'FileSrc'
INNER JOIN @.tblLov l2 ON t.CommTrailInd = l2.LOVCode and l2.ParamCode =
'CommTrailInd'
INNER JOIN @.tblLov l3 ON t.Status = l3.LOVCode and l3.ParamCode =
'CommTxnStatus'
INNER JOIN td_Remit r WITH (NOLOCK) ON t.IntRemitId = r.IntRemitId
LEFT OUTER JOIN @.tblLov l4 ON t.AgencyPrincipalInd = l4.LOVCode and
l4.ParamCode = 'AgencyPrincipalInd'
LEFT OUTER JOIN @.tblLov l5 ON t.TransAuditStatus = l5.LOVCode AND
l5.ParamCode = 'AuditTransStatus'
LEFT OUTER JOIN @.tblLov l6 ON t.ProdSubType = l6.LOVCode AND l6.ParamCode =
'ProdSubType'
LEFT OUTER JOIN tm_BDProd p WITH (NOLOCK) ON t.CUSIP = p.CUSIP
LEFT OUTER JOIN tm_BDSponsors s WITH (NOLOCK) ON t.IntBDSponsorId =
s.IntBDSponsorId
LEFT OUTER JOIN tm_Reps rp WITH (NOLOCK) ON t.IntRepId = rp.IntRepId
LEFT OUTER JOIN tm_PayoutSch sch WITH (NOLOCK) ON t.IntSchId = sch.IntSchId
WHERE t.IntTransId = (CASE WHEN @.intTransId IS NULL THEN t.intTransId ELSE
@.intTransId END) AND
t.TransSrc = @.strTransSrc AND
r.RemitCode = (CASE WHEN ISNULL(@.strRemitCode,'')='' THEN r.RemitCode ELSE
@.strRemitCode END) AND
ISNULL(t.SettlementDt,'01-01-1900') BETWEEN @.dtmFromSettlementDt AND
@.dtmToSettlementDt AND
ISNULL(t.TrdDt,'01-01-1900') BETWEEN @.dtmFromTradeDt AND @.dtmToTradeDt AND
t.CommTrailInd = (CASE WHEN @.chrShowTrails='Y' THEN t.CommTrailInd ELSE 'C'
END) AND
t.Status = (CASE WHEN ISNULL(@.strStatus,'')='' THEN t.Status ELSE
@.strStatus END) AND
ISNULL(t.ClrHouseAcctNum,'') LIKE (CASE WHEN ISNULL(@.strAccountId,'')=''
THEN ISNULL(t.ClrHouseAcctNum,'')
WHEN (@.strTransSrc = 'PSH' OR @.strTransSrc = 'MSR' OR @.strTransSrc
= 'MSA') THEN @.strAccountId
ELSE ISNULL(t.ClrHouseAcctNum,'') END) AND
ISNULL(t.FundAcctNum,'') LIKE (CASE WHEN ISNULL(@.strAccountId,'')='' THEN
ISNULL(t.FundAcctNum,'')
WHEN @.strTransSrc = 'NSM' THEN @.strAccountId
WHEN @.strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')<>'VA' THEN
@.strAccountId
ELSE ISNULL(t.FundAcctNum,'') END) AND
ISNULL(t.PolicyNum,'') LIKE (CASE WHEN ISNULL(@.strAccountId,'')='' THEN
ISNULL(t.PolicyNum,'')
WHEN @.strTransSrc = 'NSV' THEN @.strAccountId
WHEN @.strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')='VA' THEN
@.strAccountId
ELSE ISNULL(t.PolicyNum,'') END) AND
ISNULL(t.IntBDSponsorId,-1) = (CASE WHEN @.intSponsorId IS NULL THEN
ISNULL(t.IntBDSponsorId,-1) ELSE @.intSponsorId END) AND
ISNULL(t.ProdType,'') = (CASE WHEN ISNULL(@.strProdType,'')='' THEN
ISNULL(t.ProdType,'') ELSE @.strProdType END) AND
ISNULL(t.ProdSubType,'') = (CASE WHEN ISNULL(@.strProdSubType,'') ='' THEN
ISNULL(t.ProdSubType,'') ELSE @.strProdSubType END) AND
ISNULL(t.CUSIP,'') = (CASE WHEN ISNULL(@.strCUSIP,'')='' THEN
ISNULL(t.CUSIP,'') ELSE @.strCUSIP END) AND
ISNULL(rp.SSN, 0) = (CASE WHEN @.numRepSSN IS NULL THEN ISNULL(rp.SSN, 0)
ELSE @.numRepSSN END) AND
ISNULL(rp.RepCode,'') = (CASE WHEN ISNULL(@.strRepCode,'')='' THEN
ISNULL(rp.RepCode,'') ELSE @.strRepCode END) AND
ISNULL(rp.LstNm, '') = (CASE WHEN ISNULL(@.strRepLstNm,'')='' THEN
ISNULL(rp.LstNm,'') ELSE @.strRepLstNm END) AND
ISNULL(rp.FstNm, '') = (CASE WHEN ISNULL(@.strRepFstNm,'')='' THEN
ISNULL(rp.FstNm,'') ELSE @.strRepFstNm END) AND
ISNULL(rp.RepStatus,'') <> (CASE WHEN @.chrIncludeTerminated='Y' THEN 'Z'
ELSE 'T' END) AND
ISNULL(t.IntClntId,-1) = (CASE WHEN @.intClientId IS NULL THEN
ISNULL(t.IntClntId,-1) ELSE @.intClientId END) AND
( (@.chrAuditReportFlag = 'N' AND
t.Status NOT IN(@.strLov_TxnStatus_Loaded, @.strLov_TxnStatus_Cancelled) AND
ISNULL(TransAuditStatus,@.strLov_TransAuditStatus_Active) =
@.strLov_TransAuditStatus_Active
)
OR
(@.chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@.strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@.strLov_TransAuditStatus_Active, @.strLov_TransAuditStatus_Cancelled)
)
)
The 12 table join will without a doubt, cause slow downs with any significant amount of data. A few items to consider would be:
dynamic sql based on the requested search criteria. This would be good if you are familiar with dynanmic SQL and would be able to elliminate many joins based on only having specific criteria. For example, if you don't have fields pertaining to the tm_PayoutSch table, then could you remove that join? This would be be done in dynamic sql since you would not be able to determine that ahead of time. Also, elliminating the CASE statements in the JOIN clause will significally help you out if this is an option.|||
First, at the end of this statement, a few lines looks irragular to me:
(@.chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@.strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@.strLov_TransAuditStatus_Active, @.strLov_TransAuditStatus_Cancelled)
I am not sure how SQL will interprate it. Beside all Chris said, you may consider to use temprary table to limit the size of intermediate dataset. SQL optimization can do some thing, but we better to help it with our own effort. Get the output sub set from the core table or the lasrgest table(tr_CommTrans) first. In that way, we can avoid generate a even larger intermediate data set when left join other tables. Also, it apperantly you a dealing with a lot of garbage data. When you creates the temperary table, you can get a data clean table and without change the original data.
|||Continuing on the previous two posts, here are a couple of things I see:
Joining to a table variable can be an expensive process. The query optimizer assumes that table variables will have just one row in them (check out your execution plan). Since table variables contain no statistics, there is nothing to make it assume otherwise. Consider a temporary table or a subquery in these places.
Using ISNULL in your WHERE clause is not preferrable on either side of the "Equals". ISNULL on the column could cause SQL Server to perform an INDEX SCAN on the column in question (assuming it's indexed) or TABLE SCAN (clustered index scan), as it has to perform this calculation on each row.
Using ISNULL on the parameter side (I.E. ISNULL(@.strAccountId,'') ) results in a non-SARGable query. SQL Server will be unable to determine the cardinality of your request and could also result in an INDEX SCAN or TABLE SCAN.
Subscribe to:
Posts (Atom)