how to optimize this SQL statement ?

**Quin** · May 24, 2004 01:21

Hi,

I've been helped a number of times here regarding to SQL statements, my thx again :-)

This time it is no different.
I have a SQL statement that works but I want it to run faster (now takes about 3 sec.) an design it better. The DB is SQL Server.

Alright one table is used here : Participations.
It contains phone numbers bound to a specific session (broadcast on tv) and some info about when they called. The nr. of rows has a max. of about 10000, because each session is being backupped and then deleted.
Table SQL :

Code:

CREATE TABLE [dbo].[Participations] (
 [Id] [int] IDENTITY (1, 1) NOT NULL ,
 [SessionId] [int] NOT NULL ,
 [DateVal] [char] (10) NOT NULL ,
 [TimeVal] [char] (10) NOT NULL ,
 [szTelephone] [char] (10) NOT NULL ,
 [Post_FLAG] [int] NOT NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Participations] WITH NOCHECK ADD 
 CONSTRAINT [PK_Participations] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[Participations] ADD 
 CONSTRAINT [DF_Participations_Post_FLAG] DEFAULT (1) FOR [Post_FLAG]
GO
 CREATE  INDEX [Participations_tel] ON [dbo].[Participations]([szTelephone]) ON [PRIMARY]
GO
 CREATE  INDEX [Participations_time] ON [dbo].[Participations]([TimeVal]) ON [PRIMARY]
GO

To be clear, I didn't design the table, otherwise I would have used DateTime, but it's irrelevant for the example.

Now the SQL statement itself.
Purpose : to give me all the phone numbers for a specific session, the number of times they occurred for that session, their unique ID (pk) and the date and time they entered the system. If any phone number occurs more than once, return only the one that entered for the first time along with it's corresponding date, time and Id (pk) values.
So some of you already will notice the combining of aggregate functions and normal fields.

Now the way I did it :

Code:

SELECT C.*
FROM
(
SELECT b.szTelephone, b.ANumber, b.DateVal, b.TimeVal, a.ID, a.Session
FROM Participations A,
 (
 SELECT szTelephone, COUNT(szTelephone) as ANumber, MAX(DateVal) as DateVal, MAX(TimeVal) as TimeVal
 FROM Participations
 WHERE Session = 143 AND Post_FLAG = 0
 GROUP BY szTelephone
 ) B
WHERE A.szTelephone = B.szTelephone
AND   A.Session = 143 AND POST_Flag = 0
) C
WHERE C.ID = (SELECT MIN(ID) FROM Participations WHERE C.szTelephone = szTelephone AND Session = 143 AND Post_Flag = 0)
ORDER BY C.ID

This thing runs at 3 sec. average.
I used a subselect as table B, because I have to fetch the unique ID (and Session) for each phone number, but because I use aggregate functions in that subselect, I can't just select the Id in there, either use it in aggregate function or put it in group by, but neither of those options is what I want, hence table B.
Table C is for selecting the first ID occurred in that table.

Any improvements ? :-)

Thx in advance !

**DaveMaxwell** · May 24, 2004 04:31

What you're doing should be able to be done in one select. You don't need to select the sessionID since you obviously already know what it is. If you want to loop through the sessions, add the session to the select and to the group by portions...

** WARNING ** Code hasn't been tested, so there may be some syntax errors (especially with the convert portion), so test thoroughly....

Code:

SELECT szTelephone
, min(ID) AS minID
, min(convert(datetime, DateVal + ' ' + TimeVal)) AS DateTimeVal
, COUNT(*) AS ANumber
FROM Participations
WHERE Session = 143
AND Post_FLAG = 0
GROUP BY szTelephone