04-15-2016 12:36 AM
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
04-15-2016 02:10 AM
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;
04-15-2016 01:16 PM
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;
04-18-2016 01:17 AM
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
04-18-2016 08:36 AM
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.