sql server performance issue
-
Hello ,
we are facing some issues with performance with SQL server, we have more than 100000 sales deal records in one table and almost 2500 actual sales persons in another table.
We have created one Table valued UDF which takes three paramters and returns the sales deals information.
This udf returns approx. 89,000 rows for top level sales persons. But it takes almost 48-49 secs to return 89,000 rows with more than 20 columns for each row.
We believe that indexes are set properly. This was working much faster with less data. But with growing data, performance has degraded. Could you please help us to resolve this performance issue ?
Here is sample SP code :
SELECT
field1,
field2,
field3,
field4,
s.*,
field5,
field6,
field7,
field8,
field9,
field10,
field11,
field12,
field13,
field14,
field15,
field16,
field17,
field18,
field19,
field20,
field21,
field22,
FROM Level, fnGetWithMonthWINs(@loginid, @teamId,@IsNodeMultiteam) main
JOIN fnGetAlldataByProduct(@loginid,@teamId,@IsNodeMultiteam) S ON S.id = main.id
LEFT OUTER JOIN Locate ON s.LocationId=Locations.LocationId
WHERE Opps. Levelno = Level. Levelno AND Level.Letter <> 'L'
ORDER BY
field1,
field4,
field6,
field7,
field8,
field10,
field15,
field16,
field19
-
Returning 89000 rows out of 100000 pretty much means that you're going to get scans of the table and/or indexes involved.
However, there might be things that can be done. Can you post the execution plan for the query?
Also, I note that you're joining two UDF's. Are these single statement UDF's or multi-statement? If they're multi-statement, that's your problem, right there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I have uploded the execution plan below
http://www.screencast.com/t/edsAREIt
click on "Download this media." and save the file as txt , the default type is xml,
please change it to txt.
-
Please post the code for the UDFs, the schema of the tables involved, and the indexes on them.
Oh, and read these two posts as well.
http://sqlinthewild.co.za/index.php/2008/08/12/views-or-functions/
http://scarydba.wordpress.com/2008/08/15/more-on-table-valued-functions/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
bhimrajg (12/31/2008)
I have uploded the execution plan belowYou can zip it and attach it to your post here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok....Here is simple query :
SELECT
o.OId,
o.ExternalGUID,
o.StId,
o.ActId,
o.RepId,
o.OppoName,
o.BPotential,
o.RPotential,
o.ForeToClose,
o.CloseDate,
o.OTypeId,
o.CurrId,
o.PctGrMagin,
o.CreateDate,
o.TargtStDate,
o.ActualStDate,
o.OppoIsLost,
o.LocId,
s.RepTypeId,
s.TerriId,
a.AcctName,
a.AcctTypeId,
OfficeId,
0 AS CrossTerritoryAccount,
o.note,
o.LastModifiedDate
FROM Opportunities o
JOIN SalesReps s ON s.RepId= o.RepId
JOIN Accounts a ON a.ActId= o.ActId
WHERE o.OppoIsLost= 'N'
And Please check the execution plan
Still this query takes appox 19 secs to return 89,000 rows.
Opportunities table has over 143564 records
Accounts table has 114003 records
SalesReps table has 2843 records
Could you please help me to improve the performance. ? This is root level user defined function code block.
-
Actually I can not add full schema information here 🙁
-
bhimrajg (12/31/2008)
Ok....Here is simple query :Is that the select inside one of the udfs?
What about the other udf?
And Please check the execution plan
Right click the exec plan, select save as, save the plan as a .sqlplan file, zip it and attach it to your post.
till this query takes appox 19 secs to return 89,000 rows
I'm not surprised. You have three table scans there. If you have any nonclustered indexes, SQL's not using them.
Opportunities table has over 143564 records
Accounts table has 114003 records
SalesReps table has 2843 records
Please post schema of the tables and the index definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi GilaMonster,
Please find the table-valued UDF code, Table schema and indexes in the attached zip file.
I am trying to run simple select query on the UDF function and its taking approx 36-38 seconds .
For ex. Select * from dbo.[fnGetOppos_Test](@loginToken,@TerrId,@IsNodeMultiVAR)
Note : fnGetOppos_Test internally use another UDF fnGetTerrTree. I havent included its defination as its very simple udf returning only few columns and 2000 rows. If I run it separately it takes only 0-1 sec.
We have created UDFs which returns the data depending on the user access and then we use these UDFs instead of physical tables in all stored procedures. But now we are running into performance issues with growing data.
Could you please have a look at attached files and execution plan ? I certainly feel that 2 million data should not be a problem.
Thanks in advance.
-
I'll check the exec plan when I get SQL reinstalled. In the meantime, what's the definition of fnGetTerrTree, and can you post the schemas of the tables (as create table statements please)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Hi Rajg,
I can't find the definition of the IX_VarOffice index in code you sent. Also as Gail said it would be helpful to see create table statements and the definition of the fnGetTerrTree function.
At aside, in my opinion the table is a bit overindexed, although it is probably not important for your problem. Is the IX_AllOppoty used at all?
I'd rephrase the function to the following - most of the columns are returned from Opportunities table, so why the SalesReps are first in the FROM? But this may be my personal opinion of course.
ALTER FUNCTION [dbo].[fnGetOppos_Test](
@loginToken UNIQUEIDENTIFIER,
@terrId INT = 0,
@IsNodeMultiVAR BIT = 0
)
RETURNS TABLE
RETURN
(
SELECT
o.OId,
o.ExternalGUID,
o.StId,
o.ActId,
o.RepId,
o.OppoName,
o.BPotential,
o.RPotential,
o.ForeToClose,
o.CloseDate,
o.OTypeId,
o.CurrId,
o.PctGrMagin,
o.CreateDate,
o.TargtStDate,
o.ActualStDate,
o.OppoIsLost,
o.LocId,
s.RepTypeId,
s.TerriId,
a.AcctName,
a.AcctTypeId,
OfficeId,
0 AS CrossTerrAcct,
o.note,
o.LastModifiedDate
FROM Opportunities o INNER JOIN
(SalesReps s
INNER JOIN fnGetTerrTree(@loginToken, @terrId,@IsNodeMultiVAR) t
ON t.TerriId = s.TerriId)
ON o.RepId = s.RepId
INNER JOIN Accounts a ON o.ActId = a.ActId
WHERE
o.OfficeId = t.ParTerriId
AND o.OppoIsLost = 'N'
)
Regards
Piotr
...and your only reply is slàinte mhath
-
hello
Please refer IX_VarOffice index as IX_Office.
please find the attachments tables.txt for create table statements.
also definition of the functions fnGetTerrTree.txt and fnGetTerrTree_2.txt
-
First things first, do you have automatic statistics enabled on the database? If not, why not?
The index IX_VarOffice is showing as 1 estimated row and 84,000 actual rows. That's usually (not always) indicative of statistics being out of date. If you don't have automatic statistics on, I'd turn them on. If you already have them on, I'd strongly suggest updating the statistics with a FULL scan on that table... Actually, I take it back, run a full scan on all your tables. I'm seeing a wide disparity on each of the operators between estimated and actual rows.
I'd do that first and then regenerate the plan. Right now, what you have is representative of bad statistics, not necessarily bad tsql or indexes.
While you're at it, you might want to see if you need to defrag the indexes as well.
You've got a missing join predicate against TerritoryPath.
Gail will probably come up with all kinds of issues, but I'd suggest addressing the statistics and reposting the new execution plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi,
I updated statistics on database by using 'sp_updatestats' but it didnt help. Still simple select query to user-defined function is taking same time (apprx. 35 secs).
I noticed one change in the execution plan i.e all index scans are now replaced by seeks. I have attached new execution plan here.
This is our root level udf and all stored procedures are using this function creating performance issues everywhere in application.
I dont know much about index defragmentation..I'll check it.
P.S In this post, you will find different column names for same column at some places for ex. TerrId / TerritoryId. Please note that they are same.
Thanks you.
Rajg
-
There is less disparity between the estimated & the actual, so that's good. You've still got a missing JOIN predicate. That should get fixed.
I still suggest you run UPDATE STATISTICS WITH FULL SCAN against the tables. It still looks to me like your statistics are off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply