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 optimization in SSIS

Hi,

our package have design like this,

OLEDBSource à Derived Column à Lookup

|

Matching Records Un Matched Records

| |

OLEDBCommandOLEDBDestination

(Update)(Insert)

and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package

Jegan

There are plenty of areas which could cause performance problems, just work through them logically. From a pure SSIS perspective, lookups can be slow either building the cache, or when not using caching. The OLEDB Command can also be slow because it is row by row processing. The OLEDB Destination is not great for Oracle, because they don't have fast load support in OLE-DB. There is a third party driver which is I believe faster.

Saying all that, start with the basics. What networks are there between the Source Machine -> SSIS Machine -> Destination Machine, as the data will follow that path.

Isolate the components and test them individually to identify any bottle necks.

e.g.

Source -> Trash Destination - Is the source query, extract or network hop slow

Stage the source data in a raw file in the SSIS machine, then use a Raw File Source -> Lookup -> 2 X Trash Destinations, see if the lookup performance is slow.

etc

Trash Destination is just a freebie transform on http://www.sqlis.com, which we wrote to help build test scenarios faster, and it is then obvious this is not a "real" package, but you can use other transforms just don't add an output. Row Number or Union work quite well as they don't do much on their own.

|||

In addition to what Darren said you should try and apply the OVAL concept to investigatig performance problems. OVAL was introduced by Donald Farmer in a Technet webcast and I talk about it here:

Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)

I highly recommend you watch the webcast.

Also, the Oracle driver that Darren spoke of is provided by Persistent: http://www.persistentsys.com/products/ssisoracleconn/ssisoracleconn_features.htm

Also, Scott Barrett has lots of experience of using SSIS with oracle and irs worth checking out his blog: http://microsoftdw.blogspot.com/

-Jamie

|||

Jegant wrote:

Hi,

our package have design like this,

OLEDBSource à Derived Column à Lookup

|

Matching Records Un Matched Records

| |

OLEDBCommand OLEDBDestination

(Update) (Insert)

and our source & destination table are oracle. when we execute the package the performance is very low and some times its showing like processing ( yellow color) even for 1 hrs .what could be the problem.can any one help us.is there any reason like when we use orcale database this will slow down the performance of package

Jegan

Looking only at the SSIS side, here is my bet: the OLE DB Command. How many rows are you processing? and how many of those are going to the update pipeline?

As a general, and very personal, practice I always replace the OLEDB Command by an OLE DB destination pointing to a temporary table that is empty at the beginning of every execution. Then back in the control flow I use an 'Execute SQL Task' to perform a one time update using the content of the temp table. I had similar performance issues using the approach you are describing in your post and this simple change made a huge difference. Other suggestion is to try to isolate the issue; for example replace the OLE DB command by a RowCount transformation and measure your execution time; then try the same with the OLE DB Destination. This paper has very good tips on Performance Tuning Techniques:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

Rafael Salas

Performance optimization

Hi guys.
I'm trying to solve a performance problem I'm having with SQL Server.
I narrowed the cause of the deficiency to SQL Server's dynamic memory
allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
Server Memory )
This is the stored procedure that is causing the dynamic memory
allocation problem. There is a helper stored procedure that runs this
procedure while looping through a table.
My eyes have been trained to this code and I'm probably missing
something really simple. Can anyone lend me a fresh pair of eyes?
Thanks,
Ben
CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
@.startDate char(20), @.endDate char(20)) as
SET NOCOUNT ON
DECLARE @.level int
DECLARE @.ErrorCode int
Set @.ErrorCode = 0
DECLARE @.startDateDT datetime
DECLARE @.endDateDT datetime
Set @.startDateDT = convert(datetime, @.startDate)
Set @.endDateDT = convert(datetime, @.endDate)
CREATE TABLE #stack (entityID float(20), level int, plant char(255),
names char(100), Posted int, Accurate int )
/* this creates a temp table called #stack that recursively moves
through the plant table pushing and popping */
/* until it gets the entire tree calculated */
INSERT #stack
SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
= C.Plnt
WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
< @.endDateDT) OR C.Posted Is NULL)
SELECT @.level = 1
CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
int)
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.currentEntity = entityID
FROM #stack
WHERE level = @.level
INSERT #tbl_temp_sum
SELECT S.plant, S.Posted, S.Accurate
FROM #stack S
WHERE S.entityID = @.currentEntity
DELETE FROM #stack
WHERE level = @.level
AND entityID = @.currentEntity
INSERT #stack
SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
P.Plant = C.Plnt
WHERE P.parent = @.currentEntity AND ((C.Date >=
@.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
DECLARE @.count int;
DECLARE @.plantName nvarchar(50);
SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID =
@.currentEntity;
SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
if ( @.count > 0 )
BEGIN
INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
SUM(Accurate), @.endDate FROM #tbl_temp_sum;
END
--SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
Accurate FROM #tbl_temp_sum
SET @.ErrorCode = @.@.Error
Return @.ErrorCode
GOHi
Without knowing the table structure and data it is harder to diagnose your
issue. There is a lack of error handling see
http://www.sommarskog.se/error-handling-II.html and your temporary tables
should probably be created at the start (if they are really needed!)
Try using the debug option in Query Analyser (open the object browser(F8),
select the procedure, right click and choose debug), to step through the cod
e
and see the values.
John
"benis.ong@.gmail.com" wrote:

> Hi guys.
> I'm trying to solve a performance problem I'm having with SQL Server.
> I narrowed the cause of the deficiency to SQL Server's dynamic memory
> allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
> Server Memory )
> This is the stored procedure that is causing the dynamic memory
> allocation problem. There is a helper stored procedure that runs this
> procedure while looping through a table.
> My eyes have been trained to this code and I'm probably missing
> something really simple. Can anyone lend me a fresh pair of eyes?
>
> Thanks,
> Ben
> CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
> @.startDate char(20), @.endDate char(20)) as
> SET NOCOUNT ON
> DECLARE @.level int
> DECLARE @.ErrorCode int
> Set @.ErrorCode = 0
> DECLARE @.startDateDT datetime
> DECLARE @.endDateDT datetime
> Set @.startDateDT = convert(datetime, @.startDate)
> Set @.endDateDT = convert(datetime, @.endDate)
> CREATE TABLE #stack (entityID float(20), level int, plant char(255),
> names char(100), Posted int, Accurate int )
> /* this creates a temp table called #stack that recursively moves
> through the plant table pushing and popping */
> /* until it gets the entire tree calculated */
> INSERT #stack
> SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
> = C.Plnt
> WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
> < @.endDateDT) OR C.Posted Is NULL)
> SELECT @.level = 1
> CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
> int)
> WHILE @.level > 0
> BEGIN
> IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
> BEGIN
> SELECT @.currentEntity = entityID
> FROM #stack
> WHERE level = @.level
> INSERT #tbl_temp_sum
> SELECT S.plant, S.Posted, S.Accurate
> FROM #stack S
> WHERE S.entityID = @.currentEntity
> DELETE FROM #stack
> WHERE level = @.level
> AND entityID = @.currentEntity
> INSERT #stack
> SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
> C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
> P.Plant = C.Plnt
> WHERE P.parent = @.currentEntity AND ((C.Date >=
> @.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
> IF @.@.ROWCOUNT > 0
> SELECT @.level = @.level + 1
> END
> ELSE
> SELECT @.level = @.level - 1
> END -- WHILE
> DECLARE @.count int;
> DECLARE @.plantName nvarchar(50);
> SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID =
> @.currentEntity;
> SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
> if ( @.count > 0 )
> BEGIN
> INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
> SUM(Accurate), @.endDate FROM #tbl_temp_sum;
> END
> --SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
> Accurate FROM #tbl_temp_sum
> SET @.ErrorCode = @.@.Error
> Return @.ErrorCode
> GO
>

Performance optimization

Hi guys.
I'm trying to solve a performance problem I'm having with SQL Server.
I narrowed the cause of the deficiency to SQL Server's dynamic memory
allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
Server Memory )
This is the stored procedure that is causing the dynamic memory
allocation problem. There is a helper stored procedure that runs this
procedure while looping through a table.
My eyes have been trained to this code and I'm probably missing
something really simple. Can anyone lend me a fresh pair of eyes?
Thanks,
Ben
CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
@.startDate char(20), @.endDate char(20)) as
SET NOCOUNT ON
DECLARE @.level int
DECLARE @.ErrorCode int
Set @.ErrorCode = 0
DECLARE @.startDateDT datetime
DECLARE @.endDateDT datetime
Set @.startDateDT = convert(datetime, @.startDate)
Set @.endDateDT = convert(datetime, @.endDate)
CREATE TABLE #stack (entityID float(20), level int, plant char(255),
names char(100), Posted int, Accurate int )
/* this creates a temp table called #stack that recursively moves
through the plant table pushing and popping */
/* until it gets the entire tree calculated */
INSERT #stack
SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
= C.Plnt
WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
< @.endDateDT) OR C.Posted Is NULL)
SELECT @.level = 1
CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
int)
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.currentEntity = entityID
FROM #stack
WHERE level = @.level
INSERT #tbl_temp_sum
SELECT S.plant, S.Posted, S.Accurate
FROM #stack S
WHERE S.entityID = @.currentEntity
DELETE FROM #stack
WHERE level = @.level
AND entityID = @.currentEntity
INSERT #stack
SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
P.Plant = C.Plnt
WHERE P.parent = @.currentEntity AND ((C.Date >=
@.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
DECLARE @.count int;
DECLARE @.plantName nvarchar(50);
SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID =
@.currentEntity;
SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
if ( @.count > 0 )
BEGIN
INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
SUM(Accurate), @.endDate FROM #tbl_temp_sum;
END
--SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
Accurate FROM #tbl_temp_sum
SET @.ErrorCode = @.@.Error
Return @.ErrorCode
GO
Hi
Without knowing the table structure and data it is harder to diagnose your
issue. There is a lack of error handling see
http://www.sommarskog.se/error-handling-II.html and your temporary tables
should probably be created at the start (if they are really needed!)
Try using the debug option in Query Analyser (open the object browser(F8),
select the procedure, right click and choose debug), to step through the code
and see the values.
John
"benis.ong@.gmail.com" wrote:

> Hi guys.
> I'm trying to solve a performance problem I'm having with SQL Server.
> I narrowed the cause of the deficiency to SQL Server's dynamic memory
> allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
> Server Memory )
> This is the stored procedure that is causing the dynamic memory
> allocation problem. There is a helper stored procedure that runs this
> procedure while looping through a table.
> My eyes have been trained to this code and I'm probably missing
> something really simple. Can anyone lend me a fresh pair of eyes?
>
> Thanks,
> Ben
> CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
> @.startDate char(20), @.endDate char(20)) as
> SET NOCOUNT ON
> DECLARE @.level int
> DECLARE @.ErrorCode int
> Set @.ErrorCode = 0
> DECLARE @.startDateDT datetime
> DECLARE @.endDateDT datetime
> Set @.startDateDT = convert(datetime, @.startDate)
> Set @.endDateDT = convert(datetime, @.endDate)
> CREATE TABLE #stack (entityID float(20), level int, plant char(255),
> names char(100), Posted int, Accurate int )
> /* this creates a temp table called #stack that recursively moves
> through the plant table pushing and popping */
> /* until it gets the entire tree calculated */
> INSERT #stack
> SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
> = C.Plnt
> WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
> < @.endDateDT) OR C.Posted Is NULL)
> SELECT @.level = 1
> CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
> int)
> WHILE @.level > 0
> BEGIN
> IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
> BEGIN
> SELECT @.currentEntity = entityID
> FROM #stack
> WHERE level = @.level
> INSERT #tbl_temp_sum
> SELECT S.plant, S.Posted, S.Accurate
> FROM #stack S
> WHERE S.entityID = @.currentEntity
> DELETE FROM #stack
> WHERE level = @.level
> AND entityID = @.currentEntity
> INSERT #stack
> SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
> C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
> P.Plant = C.Plnt
> WHERE P.parent = @.currentEntity AND ((C.Date >=
> @.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
> IF @.@.ROWCOUNT > 0
> SELECT @.level = @.level + 1
> END
> ELSE
> SELECT @.level = @.level - 1
> END -- WHILE
> DECLARE @.count int;
> DECLARE @.plantName nvarchar(50);
> SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID =
> @.currentEntity;
> SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
> if ( @.count > 0 )
> BEGIN
> INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
> SUM(Accurate), @.endDate FROM #tbl_temp_sum;
> END
> --SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
> Accurate FROM #tbl_temp_sum
> SET @.ErrorCode = @.@.Error
> Return @.ErrorCode
> GO
>

Performance Optimization

I have one problem while optimizing the sql query .For a few rows the
query works perfectly ,but as the number of rows increases it works but
gives wrong result.
I am using nested queries W/O aliasing . So what I assume is that Query
Optimizer is trying to flatten the query (converting it into joins) and
in the process ,because of no Alias Name takes a long time .
Something like this :--
Select * From EmpMst Where deptid in
( Select deptid from deptmst where deptname='acc')
--
into This --
--
Select * From EmpMst EM
Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
What might be the reasons for performance debacle? Following is what I
am using
1. Views (using *) --
2. Indexing (Clustered)
3. History Data (2 Billion Rows)
4. No Indexed views
5. Scalar Functions ( a bit For format Checking like All
alphabets,digits etc)
6. No Cursor
7. Updating a permanent temp table for intermediate results.
With Warm regards
Jatinder SinghIt is not clear to me what your question is? Is your question about
performance, or about getting a queries that always returns correct
results?
Which query are you currently using? The first or the second?
If you replace "*" with "EM.*" in the second query, then it is still not
(necessarily) equivalent to the first. If table DeptMst contains
duplicate deptid values, then the second query will return more rows
than the first. This effect can be cancelled out by adding the DISTINCT
keyword (and selection only from the EmpMst table).
Also, if column DeptMst.deptid contains NULLs, then the queries will
return different results.
If you are using SQL2K SP4, and the queries does not perform well, then
maybe you don't have proper indexes in place, or maybe your statistics
are not up to date. You did not post any DDL, so you indexing strategy
is unknown to us.
Gert-Jan
jsfromynr wrote:
> I have one problem while optimizing the sql query .For a few rows the
> query works perfectly ,but as the number of rows increases it works but
> gives wrong result.
> I am using nested queries W/O aliasing . So what I assume is that Query
> Optimizer is trying to flatten the query (converting it into joins) and
> in the process ,because of no Alias Name takes a long time .
> Something like this :--
> Select * From EmpMst Where deptid in
> ( Select deptid from deptmst where deptname='acc')
> --
> into This --
> --
> Select * From EmpMst EM
> Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
> What might be the reasons for performance debacle? Following is what I
> am using
> 1. Views (using *) --
> 2. Indexing (Clustered)
> 3. History Data (2 Billion Rows)
> 4. No Indexed views
> 5. Scalar Functions ( a bit For format Checking like All
> alphabets,digits etc)
> 6. No Cursor
> 7. Updating a permanent temp table for intermediate results.
> With Warm regards
> Jatinder Singh|||Hi There,
Sorry for not making myself clear . I know you can solve / suggest
some new points.
I am using First Query.
The example I had taken is where empid is PK in EmpMst and deptid is PK
in DeptMst so they cannot be null.
(if column DeptMst.deptid contains NULLs, then the queries will return
different results.
And If table DeptMst contains duplicate deptid values, then the second
query will return more rows than the first)
Select * From EmpMst Where deptid in
( Select deptid from deptmst where deptname='acc')
--
into This --
--
Select * From EmpMst EM
Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
That is the way I thought (I can be wrong) Query Optimizer will Convert
the queries with IN (Subquery...)
What might be the reasons for performance debacle (in Case I am using
Subqueries/Corelated Subqueries)?I assume here that Subqueries are
executed using tempdb.
Following is what I am using
1. Views (using *) --
2. Indexing (Clustered)
3. History Data (2 Billion Rows)
4. No Indexed views
5. Scalar Functions ( a bit For format Checking like All
alphabets,digits etc)
6. No Cursor
7. Updating a permanent temp table for intermediate results.
With Warm regards
Jatinder Singh|||You can look at the execution plan to see if the optimizer transforms
the query the way you describe. Press CTRL+G in Query Analyser and then
run the query to see the execution plan.
How the optimizer will execute your query depends on the relative table
sizes, the selectivity of the column you join on, and the 'width' of the
available indexes.
In this case the optimizer can choose between three strategies:
1a) use EmpMst as outer table and perform lookups for each relevant
deptid in DeptMst
1b) use DeptMst as outer table and perform lookups for each relevant
deptid in EmpMst (provided that you have an index on EmpMst(deptid))
(unlikely in your case)
2) scan both tables and join the result with the hashing algorithm
3) use a merge join to simulateneously scan both tables and join them
(unlikely in your case)
Under normal circumstances it is not necessary to manually rewrite the
query, because the optimizer will figure out the best plan.
However, if DeptMst and/or EmpMst are views, then no one can tell
anything about it, because you haven't posted any view definition, or
any DDL, indexes, etc. This is important information for performance
related questions. All 7 point you mention can affect performance, but
without addition information...
Maybe you can find some useful information on
http://www.sql-server-performance.com/
If not, then please provide more information (see
http://www.aspfaq.com/etiquette.asp?id=5006)
Gert-Jan
jsfromynr wrote:
> Hi There,
> Sorry for not making myself clear . I know you can solve / suggest
> some new points.
> I am using First Query.
> The example I had taken is where empid is PK in EmpMst and deptid is PK
> in DeptMst so they cannot be null.
> (if column DeptMst.deptid contains NULLs, then the queries will return
> different results.
> And If table DeptMst contains duplicate deptid values, then the second
> query will return more rows than the first)
> Select * From EmpMst Where deptid in
> ( Select deptid from deptmst where deptname='acc')
> --
> into This --
> --
> Select * From EmpMst EM
> Inner Join DeptMst DM On Dm.deptid = EM.deptid and DM.deptname='acc'
> That is the way I thought (I can be wrong) Query Optimizer will Convert
> the queries with IN (Subquery...)
> What might be the reasons for performance debacle (in Case I am using
> Subqueries/Corelated Subqueries)?I assume here that Subqueries are
> executed using tempdb.
> Following is what I am using
> 1. Views (using *) --
> 2. Indexing (Clustered)
> 3. History Data (2 Billion Rows)
> 4. No Indexed views
> 5. Scalar Functions ( a bit For format Checking like All
> alphabets,digits etc)
> 6. No Cursor
> 7. Updating a permanent temp table for intermediate results.
> With Warm regards
> Jatinder Singh|||Hi There,
Thanks for your input. The database design you are asking for is too
big to post . Your explaination is excellent. I should say with minimal
information ,you gave me geniune help. I hope someday , I will be
helping others as you do.
Yeah www.sql-server-performance.com is the site I regulary visit , not
to forget dbazine.com
I wish , I could learn how to flatten the correlated subqueries ; The
cursor based mindset push me there . Do you know some reference
articles / sites which can provide help on this topic ( Performance ).
Thanks Again.
With Warm regards
Jatinder Singh

Performance optimization

Hi guys.
I'm trying to solve a performance problem I'm having with SQL Server.
I narrowed the cause of the deficiency to SQL Server's dynamic memory
allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
Server Memory )
This is the stored procedure that is causing the dynamic memory
allocation problem. There is a helper stored procedure that runs this
procedure while looping through a table.
My eyes have been trained to this code and I'm probably missing
something really simple. Can anyone lend me a fresh pair of eyes?
Thanks,
Ben
CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
@.startDate char(20), @.endDate char(20)) as
SET NOCOUNT ON
DECLARE @.level int
DECLARE @.ErrorCode int
Set @.ErrorCode = 0
DECLARE @.startDateDT datetime
DECLARE @.endDateDT datetime
Set @.startDateDT = convert(datetime, @.startDate)
Set @.endDateDT = convert(datetime, @.endDate)
CREATE TABLE #stack (entityID float(20), level int, plant char(255),
names char(100), Posted int, Accurate int )
/* this creates a temp table called #stack that recursively moves
through the plant table pushing and popping */
/* until it gets the entire tree calculated */
INSERT #stack
SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
= C.Plnt
WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
< @.endDateDT) OR C.Posted Is NULL)
SELECT @.level = 1
CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
int)
WHILE @.level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
BEGIN
SELECT @.currentEntity = entityID
FROM #stack
WHERE level = @.level
INSERT #tbl_temp_sum
SELECT S.plant, S.Posted, S.Accurate
FROM #stack S
WHERE S.entityID = @.currentEntity
DELETE FROM #stack
WHERE level = @.level
AND entityID = @.currentEntity
INSERT #stack
SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
C.Accurate
FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
P.Plant = C.Plnt
WHERE P.parent = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
IF @.@.ROWCOUNT > 0
SELECT @.level = @.level + 1
END
ELSE
SELECT @.level = @.level - 1
END -- WHILE
DECLARE @.count int;
DECLARE @.plantName nvarchar(50);
SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID = @.currentEntity;
SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
if ( @.count > 0 )
BEGIN
INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
SUM(Accurate), @.endDate FROM #tbl_temp_sum;
END
--SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
Accurate FROM #tbl_temp_sum
SET @.ErrorCode = @.@.Error
Return @.ErrorCode
GOHi
Without knowing the table structure and data it is harder to diagnose your
issue. There is a lack of error handling see
http://www.sommarskog.se/error-handling-II.html and your temporary tables
should probably be created at the start (if they are really needed!)
Try using the debug option in Query Analyser (open the object browser(F8),
select the procedure, right click and choose debug), to step through the code
and see the values.
John
"benis.ong@.gmail.com" wrote:
> Hi guys.
> I'm trying to solve a performance problem I'm having with SQL Server.
> I narrowed the cause of the deficiency to SQL Server's dynamic memory
> allocation. ( I ran mmc.exe and used SQL Server: Memory Manager: Total
> Server Memory )
> This is the stored procedure that is causing the dynamic memory
> allocation problem. There is a helper stored procedure that runs this
> procedure while looping through a table.
> My eyes have been trained to this code and I'm probably missing
> something really simple. Can anyone lend me a fresh pair of eyes?
>
> Thanks,
> Ben
> CREATE PROCEDURE CalculateCycleCount (@.currentEntity float(20),
> @.startDate char(20), @.endDate char(20)) as
> SET NOCOUNT ON
> DECLARE @.level int
> DECLARE @.ErrorCode int
> Set @.ErrorCode = 0
> DECLARE @.startDateDT datetime
> DECLARE @.endDateDT datetime
> Set @.startDateDT = convert(datetime, @.startDate)
> Set @.endDateDT = convert(datetime, @.endDate)
> CREATE TABLE #stack (entityID float(20), level int, plant char(255),
> names char(100), Posted int, Accurate int )
> /* this creates a temp table called #stack that recursively moves
> through the plant table pushing and popping */
> /* until it gets the entire tree calculated */
> INSERT #stack
> SELECT P.ID, 1, P.Plant, P.PlantName, C.Posted, C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON P.Plant
> = C.Plnt
> WHERE P.ID = @.currentEntity AND ((C.Date >= @.startDateDT AND C.Date
> < @.endDateDT) OR C.Posted Is NULL)
> SELECT @.level = 1
> CREATE TABLE #tbl_temp_sum (Sls_Office char(255), Posted int, Accurate
> int)
> WHILE @.level > 0
> BEGIN
> IF EXISTS (SELECT * FROM #stack WHERE level = @.level)
> BEGIN
> SELECT @.currentEntity = entityID
> FROM #stack
> WHERE level = @.level
> INSERT #tbl_temp_sum
> SELECT S.plant, S.Posted, S.Accurate
> FROM #stack S
> WHERE S.entityID = @.currentEntity
> DELETE FROM #stack
> WHERE level = @.level
> AND entityID = @.currentEntity
> INSERT #stack
> SELECT P.ID, @.level + 1, P.Plant, P.PlantName, C.Posted,
> C.Accurate
> FROM tbl_Plant P Left Outer Join tbl_Cycle_Count_Temp C ON
> P.Plant = C.Plnt
> WHERE P.parent = @.currentEntity AND ((C.Date >=> @.startDateDT AND C.Date < @.endDateDT) OR C.Posted Is NULL)
> IF @.@.ROWCOUNT > 0
> SELECT @.level = @.level + 1
> END
> ELSE
> SELECT @.level = @.level - 1
> END -- WHILE
> DECLARE @.count int;
> DECLARE @.plantName nvarchar(50);
> SELECT @.plantName = P.Plant FROM tbl_Plant P WHERE P.ID => @.currentEntity;
> SELECT @.count = COUNT(*) FROM #tbl_temp_sum;
> if ( @.count > 0 )
> BEGIN
> INSERT INTO tbl_Cycle_Count SELECT @.plantName, SUM(Posted),
> SUM(Accurate), @.endDate FROM #tbl_temp_sum;
> END
> --SELECT COUNT(*) AS Cnt, SUM(Posted) AS Posted, SUM(Accurate) as
> Accurate FROM #tbl_temp_sum
> SET @.ErrorCode = @.@.Error
> Return @.ErrorCode
> GO
>