SQL Server Performance Forum – Threads Archive

Index on Temp Table

I tried adding an index on a TempTable and it’s not using it. It still does a Table Scan of all 42,000 rows. Here it is: —————————————-
create table #MainTable(
RowID INT IDENTITY,
UserID int,
UserName varchar(100),
Phone varchar(20),
ClientID INT,
AccountID int,
AccountName varchar(100),
CustodianAccountNo varchar(15),
CustodianAccountID int,
MoneyManagerName varchar(80),
InceptionDate datetime,
PortfolioTotal numeric(18,2),
MarketValue numeric(18,2),
Equity numeric(18,2),
Fixed numeric(18,2),
CashEqv numeric(18,2),
AdvisorTotal numeric(18,2),
GrandTotal numeric(18,2),
NoOfRecords int
)
CREATE INDEX IdxUserID ON #MainTable (UserID)
—————————————– Any thoughts would be appreciated! Thanks.

---

Post the query that you think should use that index.

---

Here’s the query: UPDATE B
SET AdvisorTotal=A.MarketValue,
NoOfRecords=A.NoOfRecords,
@[email protected]+B.MarketValue
FROM ( SELECT UserID,
Sum(MarketValue) AS MarketValue,
Count(UserID) AS NoOfRecords
FROM #MainTable GROUP BY UserID )A ,#MainTable B WHERE A.UserID=B.UserID OPTION (KEEP PLAN)
Thanks!

---

How many rows are usualy in the table?
Which query part you would like index to be used on derived table with group by, join between derived table and #mainTable instance aliased with B or both? I think better performance offers the code bellow:
create #userTotals(
userID int primary key clustered,
advisorTotal numeric(18,2) not null,
noOfRecords int not null
) insert into #userTotals(userID, advisorTotal, noOfRecords)
SELECT UserID, Sum(MarketValue) AS MarketValue, Count(UserID) AS NoOfRecords
FROM #MainTable
GROUP BY UserID select @numGT=sum(advisorTotal)
from #userTotals
Later you can join #userTotals and #mainTable if you need both details and totals. Columns advisorTotal and noOfRecords can be removed from #mainTable in this case.

---

Thank you. I’m still struggling with this. The entire sproc takes 22 seconds to run. The Execution Plan shows that this query: ———————————– UPDATE B
SET AdvisorTotal=A.MarketValue,
NoOfRecords=A.NoOfRecords,
@[email protected]+B.MarketValue
FROM ( SELECT UserID,
Sum(MarketValue) AS MarketValue,
Count(UserID) AS NoOfRecords
FROM #MainTable GROUP BY UserID )A ,#MainTable B WHERE A.UserID=B.UserID OPTION (KEEP PLAN) ——————————— is 78% of the cost. One thing I noticed is that it’s doing a table scan on #MainTable which has 42,000 rows. I added a PK on UserId and ROWID because UserID has duplicates and it actually takes 3 seconds longer to run. Next in line is this query that is after the query above: ———————————————– ————————————————————————————————
— Pagination
————————————————————————————————
if @intShowAllRecords = 0
begin
set @intStartPosition = @intStartRecNo
set @intEndPosition = @intStartRecNo
if @intNoofRecords <> 0
begin
set @intEndPosition = @intStartPosition + @intPageSize -1
end
set @intNoofPages = @intNoofRecords / @intPageSize if (@intNoofRecords % @intPageSize) >= 1
begin
set @intNoofPages = @intNoofPages + 1
end
SELECT
UserID,
UserName,
Phone,
ClientID,
AccountID,
AccountName,
CustodianAccountNo,
CustodianAccountID,
MoneyManagerName,
Case WHEN InceptionDate is null THEN
‘N/A’
Else
convert(varchar (10),InceptionDate,101)
END as InceptionDate,
MarketValue as MarketValue,
EquityPer = CAST(CASE WHEN CashEqv < 0 THEN
CASE WHEN (Equity+Fixed) <> 0 THEN (Equity/(Equity+Fixed))*100
ELSE 0 END
ELSE
CASE WHEN PortfolioTotal <> 0 THEN (Equity/PortfolioTotal)*100
ELSE 0 END
END AS NUMERIC(18,2)) , FixedPer = CAST(CASE WHEN CashEqv < 0 THEN
CASE WHEN (Equity + Fixed) <>0 THEN Fixed/ (Equity + Fixed) * 100
ELSE 0 END
ELSE
CASE WHEN PortfolioTotal <>0 THEN (Fixed/PortfolioTotal) *100
ELSE 0 END
END AS NUMERIC(18,2)) ,
CashPer = CAST(CASE WHEN (CashEqv<0) THEN 0.00
ELSE
CASE WHEN PortfolioTotal <> 0 THEN (CashEqv/PortfolioTotal)*100
ELSE 0 END
END AS NUMERIC(18,2)) ,
AdvisorTotal as AdvisorTotal,
@numGT as GrandTotal,
NoOfRecords
FROM
#MainTable
WHERE RowID >= @intStartPosition AND RowID <= @intEndPosition
ORDER BY RowID OPTION (KEEP PLAN)
end
else
begin SELECT
UserID,
UserName,
Phone,
ClientID,
AccountID,
AccountName,
CustodianAccountNo,
CustodianAccountID,
MoneyManagerName,
Case WHEN InceptionDate is null THEN
‘N/A’
Else
convert(varchar (10),InceptionDate,101)
END as InceptionDate, MarketValue as MarketValue,
EquityPer = CAST(CASE WHEN CashEqv < 0 THEN
CASE WHEN (Equity+Fixed) <> 0 THEN (Equity/(Equity+Fixed))*100
ELSE 0 END
ELSE
CASE WHEN PortfolioTotal <> 0 THEN (Equity/PortfolioTotal)*100
ELSE 0 END
END AS NUMERIC(18,2)) , FixedPer = CAST(CASE WHEN CashEqv < 0 THEN
CASE WHEN (Equity + Fixed) <>0 THEN Fixed/ (Equity + Fixed) * 100
ELSE 0 END
ELSE
CASE WHEN PortfolioTotal <>0 THEN (Fixed/PortfolioTotal) *100
ELSE 0 END
END AS NUMERIC(18,2)) ,
CashPer = CAST(CASE WHEN (CashEqv<0) THEN 0.00
ELSE
CASE WHEN PortfolioTotal <> 0 THEN (CashEqv/PortfolioTotal)*100
ELSE 0 END
END AS NUMERIC(18,2)) ,
AdvisorTotal as AdvisorTotal,
@numGT as GrandTotal,
NoOfRecords
FROM
#MainTable
ORDER BY RowID OPTION (KEEP PLAN)
end DROP TABLE #MainTable ——————————————————— I am stumped on ways to get this stored procedure to run quicker. Any help is greatly appreciated. Thanks.

---

Have you tried the solution I offered for the code from your previous post?

---

Yes and unfortunately it was exactly the same so I suspect that there is something else.<br /><br />Any other thoughts? I’m getting a headache. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br />Have you tried the solution I offered for the code from your previous post?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

---

… and how many rows do you usually have in the temp table?

---

42,690 in #MainTable

---

Then try:
create #userTotals(
userID int primary key clustered,
advisorTotal numeric(18,2) not null,
noOfRecords int not null
) create index idx1 on #mainTable(userID, MarketValue) insert into #userTotals(userID, advisorTotal, noOfRecords)
SELECT UserID, Sum(MarketValue) AS MarketValue, Count(userID) AS NoOfRecords
FROM #MainTable
GROUP BY UserID select @numGT=sum(advisorTotal)
from #userTotals

---

]]>