cancel
Showing results for 
Search instead for 
Did you mean: 

SQL query - takes long time - help with optimization

mrmadej
Level 4
Partner Accredited

Hi,

I have a problem with SQL query. I am not very familiar with SQL and this is probably the reason.
The SQL looks:

select
    (a.Id) as "Image", UTCBigIntToNomTime(a.writeEndTime) as "WriteEndTime",
    (select UTCBigIntToNomTime(c.copyDate)
        from domain_imageCopy c
            where (c.imageId=a.Id and c.Id='2')) as "CopyDate"
from domain_Image a, domain_Policy b
where a.policyType='4'
and b.Keyword='PLATINUM'
and (UTCBigIntToNomTime(a.writeEndTime) >= 'Mar 29 2016 00:00:00' and
    UTCBigIntToNomTime(a.writeEndTime) <= 'Mar 31 2016 23:59:59')
order by "WriteEndTime" asc

It is very short and not too complicated, but takes very long time.
If I run this on dbisqlc it can take about 30 minutes. From GUI I have error "An unknown exception occurred." Stop/start of OPSCenter does not helps.
OPSCenter DB has about 30GB, so is large but not too large. Defragmentation of DB is performed every 2 weeks.

I need SQL which generates report about every image (on policy type, keyword and other includes/excludes) and its first copy creation time and second copy creation  time
Something like this:

Image | 1st copy creation time | 2nd copy creation time

And if the image does not have second copy, should be NULL.

Any help will be appreciated

Regards

Madej

4 REPLIES 4

mrmadej
Level 4
Partner Accredited

I changed query with according to my colleague recommendation, but it still takes too long.

He said that query looks properly.

 

select
DISTINCT (a.Id) as "Image", UTCBigIntToNomTime(a.writeEndTime) as "WriteEndTime",
    (select UTCBigIntToNomTime(c.copyDate)
        from domain_imageCopy c
        where (c.imageId=a.Id and c.Id='2')) as "CopyDate"
from domain_Image a, domain_Policy b
where a.policyName=b.Name
and a.policyType='4'
and b.Keyword='PLATINUM'
and (UTCBigIntToNomTime(a.writeEndTime) >= 'Mar 29 2016 00:00:00' and
    UTCBigIntToNomTime(a.writeEndTime) <= 'Mar 31 2016 23:59:59')
order by "WriteEndTime" asc;

areznik
Level 5

Subqueries like the one you have in your select statement will generally make your queries significantly slower.

Try something like this: 

select
DISTINCT (a.Id) as "Image", UTCBigIntToNomTime(a.writeEndTime) as "WriteEndTime", UTCBigIntToNomTime(c.copyDate) as "copyDate"
from domain_Image a left outer join domain_imageCopy c on a.id = c.ImageId,
domain_Policy b,
where c.Id = '2'
and a.policyName=b.Name
and a.policyType='4'
and UTCBigIntToNomTime(a.writeEndTime) between convert(datetime, 'Mar 29 2016 12:00AM',100) and convert(datetime,'Mar 31 2016 11:59PM',100)
and b.Keyword='PLATINUM'
order by "WriteEndTime" asc;

 

mrmadej
Level 4
Partner Accredited

Thanks for answer.

It is much better from performance perspective. But I need something else.

I omitted in my first post that I have to have that if the second copy does not exist then the "CopyDate" should be NULL.
Something like this:

Image      | 1st copy creation time   | 2nd copy creation time
host1_<TS> | Mar 29, 2016 12:02:44 AM | Mar 31, 2016 9:48:28 PM
host2_<TS> | Mar 29, 2016 12:14:05 AM | NULL

It does mean that the 2nd copy of image "host2_<TS>" does not exist (reason does not matter).

My first query returns exactly what I need but it takes too long :)

 

Regdars

Madej

areznik
Level 5

Read up a bit on OUTER joins, thats what i was trying to show you in my query. One way to improve performance dramatically is to remove the subquery and replace it with an outer join. I might have missed something in my example, I dont have a way to test it right now, but it should give you what you're asking for.