SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to optimize this SQL statement ?

    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 !

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    Belgium - Antwerp
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you !

    I tried it out and it worked, after some messing around with the CONVERT function.

  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Quin
    I tried it out and it worked, after some messing around with the CONVERT function.
    That's why I put that warning in there.... Good job on getting it to work though!

    Quote Originally Posted by Quin
    Thank you !
    You're welcome
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •