0

I am new to a particular job and using Scalar Functions for the first time.

The more records I try to process however the S L O W E R .. it gets..

Am I doing something wrong? I am reading from a view and have 2 criteria in the WHERE clause.

40 records or even 400 returns fairly fast.. (this is an old spaghetti app btw running on SQL Server 2008) but start trying to crunch 800+ records and it gets exponentially slower..

Any suggestions?

ALTER FUNCTION [dbo].[GetAccount_Total_By_Domain]
    (@domain nvarchar(128), @status nvarchar(128))
RETURNS decimal(10, 2)
AS
BEGIN
    DECLARE @allAccountsTotal int;
    DECLARE @statusTotal int;
    DECLARE @returnPercent decimal(10,2);

    SELECT 
        @allAccountsTotal = COUNT(*) 
    FROM [NDCSTATS].[dbo].[SEC_ADMIN_ACCOUNT_COMPLIANCE_VW]  
    WHERE Domain = @domain;

    SELECT 
        @statusTotal = COUNT(*) 
    FROM [NDCSTATS].[dbo].[SEC_ADMIN_ACCOUNT_COMPLIANCE_VW] 
    WHERE Domain = @domain AND StatusVal = @status;

    SET @returnPercent = CAST(100 * SUM(@statusTotal) / SUM(@allAccountsTotal) OVER() AS DECIMAL(10, 2))
        --100 * @allAccountsTotal / @statusTotal;

    RETURN @returnPercent;
END

UPDATE:

I have put a nonclustered index on the Core table which in this case is DOMAIN an nvarchar(128) type value since we are grouping by Domain - but also StatusVal - only StatusVal is not available until the View where we do a UNION.

I also removed columns not needed in the Core table and the queries have sped a bit until up around 2000 records.. but still having trouble with the big ones like 10k records.

I also added some criteria columns to the INDEX that are BIT columns so they are fast I hope!!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JoJo
  • 4,643
  • 9
  • 42
  • 65
  • 1
    There's nothing wrong with the code (I'm not sure what the `OVER()` is doing on the `SET/CAST`). The problem is going to be in the views - and whether they are coverable with indexes. How experienced optimizing queries are you? – dav1dsm1th Nov 18 '13 at 22:22
  • @dav1dsm1th Optimizing queries in views? No sir. The view btw is a UNION table (with 4 selct statements different WHERE criteria) – JoJo Nov 18 '13 at 22:26
  • 1
    As long as there is an index on each of the partitions of the view on the `domain` and the `statusval` columns (either one, with the columns in that order, or two separate) (and the criteria in the `where` conditions in the view) you should be getting reasonable performance on the row counts you are describing. Can you post an image of the actual execution plan of one call to the function with representative parameters passed? – dav1dsm1th Nov 18 '13 at 22:33
  • @dav1dsm1th Having trouble putting an index on the view: Create failed for Index.. An exception occured while executing a T-SQL statement.. Cannot create index on view 'xx_VE' because the view is not schema bound. – JoJo Nov 19 '13 at 17:36
  • No - the indexes (you'd need one (or more) on each) are on the underlying tables of the view. You can create an indexed view - which is a different beast - but that has other implications (but they can be extremely high performance). – dav1dsm1th Nov 19 '13 at 17:46
  • You need to provide a schema for the view and the tables it is built over - so that advice on indexes can be formulated. An execution plan of a call to the function would also be extremely useful. – dav1dsm1th Nov 26 '13 at 10:23

0 Answers0