Switch off all SQL statistics and run Performance Monitor from Windows "Administrative Tools"

And then try to catch your 15 times difference in CPU load.

Do you actually believe that???

Average CPU!1.347!1.904!31.265!0.56!29.92

Analysis:

The use of a UDF instead of in-line SQL resulted in a 21 % increase in duration and a 15 fold increase in CPU...

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'RefineString')

 DROP FUNCTION RefineString

GO

CREATE FUNCTION dbo.RefineString

 (@String nvarchar (4000))

RETURNS nvarchar

AS

BEGIN

 SELECT @String = REPLACE(@String, ' LTD.', ' limited')

 SELECT @String = REPLACE(@String, ' LTD',...

They told you "to set a smaller FILEGROWTH "

And what did you do?

Check the disk where you .ldf file placed. Probably there...

Copy - Paste.

Nothing special. Only reduces number of rows to 500k or 200k depending on capability of server I used.

You can see it in results. I did not post 500k...

REALLY bad.

Hopefully your "OrgChart" table is not being updated dozen times per minute.

So the best way to improve performance is to create separate table ReportsToBoss(BossId, ReporterId) and fill it up with...

CREATE ....

Declare @IDS TABLE (Id int)

INSERT INTO @IDS (ID)

SELECT @ID

WHILE EXISTS (select 1 from tblEmployee E

                        inner join @IDS I on E.ParentId = I.Id --E.ParentId in @IDS

                        left join @IDS I1 on...

No, it is NOT a relational database.

Your calldata field contains at least 5 fields:

name;address;city;state;Zipcode;Phone.

So it must be separate table "CallData", one column for name, one for address, etc.

And it...

What you have is not relational database actually.

This forum is not about working with "flat file" kind of data.

Change your table to relational type - one entity=one table, one value...

Another option.

I replace real values with NULLs.

---------------

DECLARE @MyStart DATETIME

DECLARE @MyEnd   DATETIME

Declare @DummyDate datetime

  PRINT 'Formula in SELECT, returns NULLS'

    SET @MyStart = GETDATE()

 SELECT CONVERT(DATETIME, CONVERT(INT, Date2 - 0.5)) + @DummyDate...

 There are some options (as always)

I tried to minimise network traffic influence. Feel the difference:

 PRINT 'Formula in WHERE'

    SET @MyStart = GETDATE()

 SELECT *

   FROM...

= getdate() - 1

DateVal - ((DATEPART(dw, DateVal) + @@DATEFIRST + 5) %7)

gives you last monday before DateVal. If DateVal is Monday it will return DateVal.

2 hickymanz

Have you heard something about UDF?

All those terrible REPLACE and SUBSTRING = SUBSTRING must be encapsulated in 2 scalar UDF.

Within UDF...