How can I improve my SQL query

Hi,

I have this SQL query that can take too long time, up to 1 minute if table contains over 1 million rows. And if the system is very active while executing this query it can cause more delays I guess.

select
distinct 'CONV 1' as Conveyour,
info as Error,
(select top 1 substring(timecreated, 0, 7) from log b where a.info = b.info order by timecreated asc) as Date,
(select count(*) from log b where b.info = a.info) as 'Times occured'
from log a where loggroup = 'CSCNV' and logtype = 4

The table name is LOG, and I retrieve 4 columns: Conveyour, Error, Date and Times occured. The point of the subqueries is to count all distinct post and to retrieve the date of the first time the pst was logged. Also, a first and last date could be specified but is left out here.

Does anyone knows how I can improve this SQL query?

Best /M

Try to avoid the sub-query, The following query may tune your query performance for some extent,

Code Snippet

select distinct

'conv 1' as conveyour,

info as error,

data.date,

data.[times occured]

from log a

join (select info, substring(min(timecreated),0,7) as date, count(*) as [times occured] from log b)

as data on a.info = data.info

where

loggroup = 'cscnv'

and logtype = 4

|||

Just wrote this quickly, may or may not work.

Code Snippet

SELECT 'CONV 1' AS [Conveyour]
,info AS [Error]
,MAX(substring(timecreated, 0, 7)) AS [Date]
,COUNT(*) AS [Times occured]
FROM log
WHERE loggroup = 'CSCNV'
AND logtype = 4
GROUP
BY info

Chris|||

I am not sure, as per the Moorstream query the where conditions is not controling the timecreated (date) & count.

Its upto Moorstream to choose..

|||

Ah, I see what you mean, forgot about that bit

Would be interesteing to hear the requirement behind that one.

|||

Thank you for your answers, now I have to test these queries and measure execution times

Best,

/M