Ingenuous best performing queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Ingenuous best performing queries

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-13 : 09:23:02

Hi Team,

I think, there are lots of smart queries that have improved performance. Following is one of the kind, that I created (Am I the inventor of the approach?

Not sure..).

Could you please list similar ingenuous queries or point an article that deals with this topic ?

[I agree that readability of the following better performing query is less.]

-- Batch 1 Execution
CREATE TABLE #Funding
(
[FundingID] [int] IDENTITY(1,1) NOT NULL,
[FundingNo] [int] NOT NULL,
[LKFundingTypeCode] [varchar](50) NOT NULL,
CONSTRAINT [PK_Funding_FundingID] PRIMARY KEY CLUSTERED ([FundingID] ASC)
) ON [PRIMARY]

-- Batch 2 Execution
DECLARE @Counter INT
SET @Counter = 1

WHILE @Counter <= 50000
BEGIN
INSERT INTO #Funding (FundingNo,LKFundingTypeCode) VALUES (@Counter,'XXX')
SET @Counter = @Counter+1

END

-- Batch 3 Execution
UPDATE STATISTICS #Funding
GO

-- Batch 4 Execution – SELECT Queries

--Query Group 1

DECLARE @MaxFundingID INT
SET @MaxFundingID = (SELECT MAX(FundingID) FROM Funding)

SELECT FundingID,FundingNo
FROM #Funding
WHERE FundingID<= @MaxFundingID
AND LKFundingTypeCode = 'XXX'

--Total Cost for Group 1 = 2+23 =25%

--Query Group 2
SELECT FundingID,FundingNo
FROM #Funding
WHERE LKFundingTypeCode = 'XXX'

--Total Cost for Group 2 = 75%

Thanks & Regards
Lijo Cheeran Joseph

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-13 : 11:56:21

I'm not sure what your point is here. Both of those queries return the entire table, there's no point in the filters in either case.

As for performance, on SQL 2008 I got the following:

Query 1
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#Funding'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(50000 row(s) affected)
Table '#Funding'. Scan count 1, logical reads 151, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 771 ms.

Query 2:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(50000 row(s) affected)
Table '#Funding'. Scan count 1, logical reads 150, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 730 ms.

The % costings cannot always be trusted. This is a clear case where they're plain wrong. The reads are slightly higher for the first query and the durations are almost equal.

I replaced your insert with one where the searched for value is not the same for every single row and added an appropriate index, and the cost % of the first group went to 57% and of the second query 44%, both showing appropriate index seeks.

WHILE @Counter <= 50000
BEGIN
IF @Counter BETWEEN 10000 AND 11000
INSERT INTO #Funding (FundingNo,LKFundingTypeCode) VALUES (@Counter,'XXX')
ELSE
INSERT INTO #Funding (FundingNo,LKFundingTypeCode) VALUES (@Counter,'ABC')

SET @Counter = @Counter+1

END

-- Batch 3 Execution
CREATE INDEX idx_Testing ON #Funding (LKFundingTypeCode) INCLUDE (FundingNo)
UPDATE STATISTICS #Funding WITH fullscan
GO

Durations were again almost equal

--
Gail Shaw
SQL Server MVP

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-13 : 12:24:18

Thank you, Gila. I am just learning optimization. (Started with graphical execution plan cost %. )

quote:
Originally posted by GilaMonster

The % costings cannot always be trusted. This is a clear case where they're plain wrong.

Can you please give any other examples where % cost is misleading ?

Thanks
Lijo Cheeran Joseph

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-03-14 : 04:11:49

Any time the statistics are wrong for a start.
You can use the cost, but remember that it is an estimated cost. Nothing more. It should be used in combination with logical IO, CPU time and duration.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources