Hot to Optimize the query

[inspi]

I have this Query. I need a way to optimize this.


SELECT DISTINCT /*First SQL*/
SK.key AS SECURE,
sl.col1 AS Name,
sl.col2 AS Value,
SK.log AS KEYLOG,
SK.time,
(
SELECT max(L.log)
FROM dbo.LogTable AS L
WHERE L.key = SK.key AND L.time < SK.time
) AS PREVIOUS
INTO #temp_prev_both
FROM dbo.LogkeyTbl AS SK, dbo.LogTable AS sl
WHERE
SK.log = sl.log AND
SK.time >= ’11-14-2007 12:00:00 AM’ AND
SK.time <= ’11-14-2007 11:59:59 PM’ AND
SK.time =
(
SELECT max(S.time)
FROM dbo. LogkeyTbl AS S, dbo. LogTable AS sl
WHERE
S.log = sl.log AND
S.Key = SK.Key AND
S.time >= ’11-14-2007 12:00:00 AM’ AND
S.time <= ’11-14-2007 11:59:59 PM’
GROUP BY S.Key
)
ORDER BY 1

SELECT * /*Second SQL*/
FROM #temp_prev_both t left outer join LogTable sl ON t.Previous = sl.log and t.Name = sl.Name

First SQL command will dump all the records to a temp table. There are approx 400K records.

Second SQL is taking 7 mins to run.

Is there any way to reduce the time of running?

 

[gmmastros]

If you are using a Microsoft SQL Server database, then I suggest you post your question here:
Forum183



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

skip the temp table, use a single query

SELECT * /*Second SQL*/
  FROM (
       SELECT DISTINCT  /*First SQL*/
              SK.key AS SECURE
            , sl.col1 AS Name
            , sl.col2 AS Value
            , SK.log AS KEYLOG
            , SK.time
            , ( SELECT max(L.log) 
                  FROM dbo.LogTable AS L 
                 WHERE L.key = SK.key 
                   AND L.time < SK.time
                ) AS PREVIOUS        
         FROM dbo.LogkeyTbl  AS SK
       INNER
         JOIN dbo.LogTable  AS sl
           ON SK.log = sl.log 
          AND SK.time >= '11-14-2007' 
          AND SK.time  < '11-15-2007'
          AND SK.time =  
                ( SELECT max(S.time) 
                    FROM dbo.LogkeyTbl AS S
                  INNER
                    JOIN dbo.LogTable AS sl 
                      ON S.log = sl.log 
                     AND S.Key = SK.Key 
                     AND S.time >= '11-14-2007'
                     AND S.time  < '11-15-2007' )
       ) AS temp_prev_both t 
left outer 
  join LogTable sl 
    ON t.Previous = sl.log 
   and t.Name = sl.Name

r937.com | rudy.ca

 

[SQLSister]

Rudy, just so you know, inthe SQL Server forum, it turns out his biggest issue is that the tables are not indexed.

"NOTHING is more important in a database than integrity." ESquared