November 10, 2010 at 11:41 am
The below query takes some wheer 6 to 9 seconds each time..
declare @GUID char(36)
set @GUID ='ec5a3c21-071c-48f5-80e9-d2445987dc37'
SELECT
(SELECT Count(*) FROM Temp WHERE GUID=@GUID) AS TotalNumberOfUnits,
(SELECT Count(*) FROM Temp WHERE (Results = 0) AND (GUID=@GUID)) AS T1,
(SELECT Count(*) FROM Temp WHERE (Results = 1) AND (GUID=@GUID)) AS T2,
(SELECT Count(*) FROM Temp WHERE (Results = 2) AND (GUID=@GUID)) AS T3,
(SELECT Count(*) FROM Temp WHERE (Results = 3) AND (GUID=@GUID)) AS T4
FROM Temp
This temp table has half a millinon records..
Guid is the PK having clustered index..no other indexes exist in the table..
is there any way that this query can be rewritten to bring up the performance..
TIA
November 10, 2010 at 11:44 am
We'd need to know the DDL and index builds on TEMP to be able to help further.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 11:47 am
here goes the DDL..Only CI on PK exists..
CREATE TABLE [dbo].[Temp](
[GUID] [char](36) NOT NULL,
[fk_InventoryID] [int] NOT NULL,
[TranType] [int] NOT NULL,
[Amount] [money] NOT NULL CONSTRAINT [DF_TempAmount] DEFAULT (0),
[TimeStamp] [datetime] NOT NULL,
[Results] [smallint] NULL,
CONSTRAINT [PK_Temp] PRIMARY KEY CLUSTERED
(
[GUID] ASC,
[fk_InventoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
November 10, 2010 at 12:01 pm
This should help:
CREATE NONCLUSTERED INDEX idx_temp_forGuidCounts ON TEMP ( GUID, Results)
This should let your count statements avoid a scan on the clustered and go directly for a seek on the non-clustered.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 12:23 pm
Thanks..lets see how it goes...it would take some time to go thru processes to have this NCI...
November 10, 2010 at 12:27 pm
For the query itself you might want to try something like
DECLARE @TotalNumberOfUnits INT
SELECT @TotalNumberOfUnits = COUNT(*) FROM Temp WHERE GUID=@GUID
DECLARE @Res TABLE
(
Results SMALLINT PRIMARY KEY CLUSTERED,
NumOcc INT
)
INSERT @Res
(
Results,
NumOcc
)
SELECT
Results,
COUNT(*)
FROM Temp
WHERE [GUID] = @GUID
AND Results BETWEEN 0 AND 3
GROUP BY Results
SELECT
@TotalNumberOfUnits AS TotalNumberOfUnits,
(SELECT NumOcc FROM @Res WHERE Results = 0) AS T1,
(SELECT NumOcc FROM @Res WHERE Results = 1) AS T2,
(SELECT NumOcc FROM @Res WHERE Results = 2) AS T3,
(SELECT NumOcc FROM @Res WHERE Results = 3) AS T4
Andreas Goldman
November 10, 2010 at 12:31 pm
Instead of the additional index I'd replace the multiple SELECT with a single SELECT. Something like
SELECT
COUNT(*) AS TotalNumberOfUnits,
SUM(CASE WHEN Results = 0 THEN 1 ELSE 0 END) AS T1,
SUM(CASE WHEN Results = 1 THEN 1 ELSE 0 END) AS T2,
SUM(CASE WHEN Results = 2 THEN 1 ELSE 0 END) AS T3,
SUM(CASE WHEN Results = 3 THEN 1 ELSE 0 END) AS T4
FROM Temp
WHERE GUID=@GUID
This query should perform a clustered index seek instead of a scan.
As an alternative I'd create a narrow supporting index:
CREATE NONCLUSTERED INDEX idx_temp_forGuidCounts ON TEMP ( GUID) INCLUDE (Results)
November 10, 2010 at 4:40 pm
You might add one further refinement to Lutz's excellent query, depending on whether Results column could ever have other values:
SELECT
COUNT(*) AS TotalNumberOfUnits,
SUM(CASE WHEN Results = 0 THEN 1 ELSE 0 END) AS T1,
SUM(CASE WHEN Results = 1 THEN 1 ELSE 0 END) AS T2,
SUM(CASE WHEN Results = 2 THEN 1 ELSE 0 END) AS T3,
SUM(CASE WHEN Results = 3 THEN 1 ELSE 0 END) AS T4
FROM Temp
WHERE GUID=@GUID
AND Results IN (0, 1, 2, 3)
Scott Pletcher, SQL Server MVP 2008-2010
November 11, 2010 at 11:56 am
scott.pletcher (11/10/2010)
You might add one further refinement to Lutz's excellent query, depending on whether Results could ever have other values:
SELECT
COUNT(*) AS TotalNumberOfUnits,
SUM(CASE WHEN Results = 0 THEN 1 ELSE 0 END) AS T1,
SUM(CASE WHEN Results = 1 THEN 1 ELSE 0 END) AS T2,
SUM(CASE WHEN Results = 2 THEN 1 ELSE 0 END) AS T3,
SUM(CASE WHEN Results = 3 THEN 1 ELSE 0 END) AS T4
FROM Temp
WHERE GUID=@GUID
AND Results IN (0, 1, 2, 3)
If Results holds other values than 0 to 3, the COUNT(*) in your query would show a different result than what the OP currently will get. And, if there are no other values, the IN clause won't be required.
Therefore, I decided not to include the Results column in the WHERE section.
November 11, 2010 at 12:00 pm
@Scott:
If Results holds other values than 0 to 3, the COUNT(*) in your query would show a different result than what the OP currently will get.
Quite. I figured the first line should be a total of the other four -- maybe I'm wrong there.
In particular I was thinking about NULL values.
That might not be at all applicable in this case. But I thought it was worth mentioning just in case.
Scott Pletcher, SQL Server MVP 2008-2010
November 11, 2010 at 12:17 pm
scott.pletcher (11/11/2010)
@Scott:
If Results holds other values than 0 to 3, the COUNT(*) in your query would show a different result than what the OP currently will get.
Quite. I figured the first line should be a total of the other four -- maybe I'm wrong there.
In particular I was thinking about NULL values.
That might not be at applicable in this case. But I thought it was worth mentioning just in case.
Good point. So, now the OP has two options depending on his final requirements. What else to ask for? 😀
November 12, 2010 at 6:09 am
Thanks a lot Lutz...I just modified query and it executes like a champ...
November 12, 2010 at 9:37 am
Glad I could help 😀
And thank you for the feedback.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply