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

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...
> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
> >--Original Message--
> >Basically, you are submitting the queries to an Oracle
> database, via
> >SQL-Server. So I would look at the Oracle side. If you
> submit the
> >queries directly on the Oracle database, do they behave
> the same?
> >
> >Gert-Jan
> >
> >
> >Dobromir Rizov wrote:
> >>
> >> Hello Everybody,
> >>
> >> There is an unexplained yet situation with
> >> performance/time to return query results on 3 queries.
> >>
> >> Queries must be identical and are executed from SQL S
> 2000
> >> via Linked server to an Oracle database.
> >> End table is the same (events).
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'FLORIDA'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'POWER2'
> >>
> >> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
> SERVERNAME
> >> = 'APCEPKS'
> >>
> >> Last query always takes ~5 sec to return results (
> >> to 'APCEPKS'); there are much more records than others.
> >>
> >> First and second queries take more than a minute!!!
> >>
> >> The order of execution doesn't matter.
> >>
> >> Execution plan shows no differences.
> >>
> >> My question is:
> >>
> >> What could cause performance difference like this?
> >>
> >> Any suggestions are highly appreciated!
> >>
> >> Thx,
> >>
> >> Dobby
> >.
> >|||The execution plan shows:
Row count 1 on the shortest query and full table scans on
others.
My understanding is that indexes are used only (but
always) with the query on 'APCEPKS', but never with any
other queries.
What makes queries to differ?
Is there a way to force a query to use a particular index?
Thx,
Dobby
>--Original Message--
>Hello Everybody,
>There is an unexplained yet situation with
>performance/time to return query results on 3 queries.
>Queries must be identical and are executed from SQL S
2000
>via Linked server to an Oracle database.
>End table is the same (events).
>
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'FLORIDA'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'POWER2'
>SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
>= 'APCEPKS'
>Last query always takes ~5 sec to return results (
>to 'APCEPKS'); there are much more records than others.
>First and second queries take more than a minute!!!
>The order of execution doesn't matter.
>Execution plan shows no differences.
>My question is:
>What could cause performance difference like this?
>Any suggestions are highly appreciated!
>Thx,
>Dobby
>
>
>.
>|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby
>--Original Message--
>Basically, you are submitting the queries to an Oracle
database, via
>SQL-Server. So I would look at the Oracle side. If you
submit the
>queries directly on the Oracle database, do they behave
the same?
>Gert-Jan
>
>Dobromir Rizov wrote:
>> Hello Everybody,
>> There is an unexplained yet situation with
>> performance/time to return query results on 3 queries.
>> Queries must be identical and are executed from SQL S
2000
>> via Linked server to an Oracle database.
>> End table is the same (events).
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'FLORIDA'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'POWER2'
>> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE
SERVERNAME
>> = 'APCEPKS'
>> Last query always takes ~5 sec to return results (
>> to 'APCEPKS'); there are much more records than others.
>> First and second queries take more than a minute!!!
>> The order of execution doesn't matter.
>> Execution plan shows no differences.
>> My question is:
>> What could cause performance difference like this?
>> Any suggestions are highly appreciated!
>> Thx,
>> Dobby
>.
>sql

Performance question

Hello Everybody,
There is an unexplained yet situation with
performance/time to return query results on 3 queries.
Queries must be identical and are executed from SQL S 2000
via Linked server to an Oracle database.
End table is the same (events).
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'FLORIDA'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'POWER2'
SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
= 'APCEPKS'
Last query always takes ~5 sec to return results (
to 'APCEPKS'); there are much more records than others.
First and second queries take more than a minute!!!
The order of execution doesn't matter.
Execution plan shows no differences.
My question is:
What could cause performance difference like this?
Any suggestions are highly appreciated!
Thx,
DobbyAdd these commands after each query and see how does it affect ?
DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Basically, you are submitting the queries to an Oracle database, via
SQL-Server. So I would look at the Oracle side. If you submit the
queries directly on the Oracle database, do they behave the same?
Gert-Jan
Dobromir Rizov wrote:

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby

---

|||One thing I can think of -- not familiar with Oracle data/index structure --
is that Servername is non-cluster indexed, and most of the records for
Servername = 'APCEPKS' are located in a small range of data pages, whereas
the records for the other two servername values are spread wide.
"Dobromir Rizov" <rizov_d@.shaw.ca> wrote in message
news:154a01c3e036$5522b6a0$a101280a@.phx.gbl...

quote:

---

> Hello Everybody,
> There is an unexplained yet situation with
> performance/time to return query results on 3 queries.
> Queries must be identical and are executed from SQL S 2000
> via Linked server to an Oracle database.
> End table is the same (events).
>
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'FLORIDA'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'POWER2'
> SELECT MAX(ID) FROM PHDT..PS_USER.EVENTS WHERE SERVERNAME
> = 'APCEPKS'
> Last query always takes ~5 sec to return results (
> to 'APCEPKS'); there are much more records than others.
> First and second queries take more than a minute!!!
> The order of execution doesn't matter.
> Execution plan shows no differences.
> My question is:
> What could cause performance difference like this?
> Any suggestions are highly appreciated!
> Thx,
> Dobby
>
>

---

|||Same all queries executed directly in Oracle take less
than a second to return result.
Dobromir

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

|||In that case, I would consider moving to an OPENQUERY syntax:
SET QUOTED_IDENTIFIER OFF
SELECT MaxID FROM OPENQUERY(PHDT,
"SELECT MAX(ID) AS MaxID
FROM PHDT..PS_USER.EVENTS
WHERE SERVERNAME='FLORIDA'
")
I realize however, that this may not meet your requirement...
Gert-Jan
Dobromir Rizov wrote:[QUOTE]
> Same all queries executed directly in Oracle take less
> than a second to return result.
> Dobromir
>
> database, via
> submit the
> the same?
> 2000
> SERVERNAME
> SERVERNAME
> SERVERNAME|||Directly in SQL*Plus queries retutn instant results.
There are indexes in place.
Dobby

quote:

---

>--Original Message--
>Basically, you are submitting the queries to an Oracle

---

database, via

quote:

---

>SQL-Server. So I would look at the Oracle side. If you

---

submit the

quote:

---

>queries directly on the Oracle database, do they behave

---

the same?

quote:

---

>Gert-Jan
>
>Dobromir Rizov wrote:
2000[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
SERVERNAME[QUOTE]
>.
>

---

sql

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
Dan D.
Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:

> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.
|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
Dan D.
"Francis" wrote:
[vbcol=seagreen]
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:
> Is there much of a performance penalty for using views? When I use the view
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better at
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
> Views require some extra time to look up info in system tables and although
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
> > Is there much of a performance penalty for using views? When I use the view
> > it takes 42 seconds to return results but when I run the query inside the
> > view by itself it returns records in 5 to 7 seconds.
> >
> > Would a stored procedure be better? Would the stored procedure be better at
> > keeping the execution plan?
> >
> > Thanks,
> > --
> > Dan D.

performance penalty using views

Is there much of a performance penalty for using views? When I use the view
it takes 42 seconds to return results but when I run the query inside the
view by itself it returns records in 5 to 7 seconds.
Would a stored procedure be better? Would the stored procedure be better at
keeping the execution plan?
Thanks,
--
Dan D.Views require some extra time to look up info in system tables and although
they should offer the same execution time as the statement itself they may
have a slower execution time as a result. Although this may improve if the
execution plan is cached. A stored procedure would likely offer better
execution time over a view.
"Dan D." wrote:

> Is there much of a performance penalty for using views? When I use the vie
w
> it takes 42 seconds to return results but when I run the query inside the
> view by itself it returns records in 5 to 7 seconds.
> Would a stored procedure be better? Would the stored procedure be better a
t
> keeping the execution plan?
> Thanks,
> --
> Dan D.|||After some more testing, I've discovered that if I delete the view and then
recreate it, I get the same performance from both the view and the raw query
.
I guess something got out of sync somewhere.
Thanks,
--
Dan D.
"Francis" wrote:
[vbcol=seagreen]
> Views require some extra time to look up info in system tables and althoug
h
> they should offer the same execution time as the statement itself they may
> have a slower execution time as a result. Although this may improve if the
> execution plan is cached. A stored procedure would likely offer better
> execution time over a view.
>
> "Dan D." wrote:
>

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:

--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.

Performance on RS

Hi All,
Everytime when I run the report (by URL access), it need a very long time to
wait. After such init step, then it return normal speed when I run another
report!
Anyone can help ! Thanks!
TonyI noticed this when I first started with RS and what I did to get around it
is to have a report open on my desktop that auto executes every 5 minutes,
this keeps this going. I have been told that the below will work. If you
want to do my way then get a simple report, in report->properties set it to
refresh every 5 minutes (or 10 or 15, whatever).
Here is what Chris suggests:
>>>>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> Hi All,
> Everytime when I run the report (by URL access), it need a very long time
> to
> wait. After such init step, then it return normal speed when I run
> another
> report!
> Anyone can help ! Thanks!
> Tony
>|||Thank you Bruce! I try it!
"Bruce L-C [MVP]" wrote:
> I noticed this when I first started with RS and what I did to get around it
> is to have a report open on my desktop that auto executes every 5 minutes,
> this keeps this going. I have been told that the below will work. If you
> want to do my way then get a simple report, in report->properties set it to
> refresh every 5 minutes (or 10 or 15, whatever).
> Here is what Chris suggests:
> >>>>>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>>>>>>>>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:35CB04BD-BB0B-4D38-A4FF-03A52557E4A3@.microsoft.com...
> > Hi All,
> >
> > Everytime when I run the report (by URL access), it need a very long time
> > to
> > wait. After such init step, then it return normal speed when I run
> > another
> > report!
> >
> > Anyone can help ! Thanks!
> >
> > Tony
> >
>
>