how can I optimize more - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

how can I optimize more

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 04:00:56

select distinct site,DATEADD(dd, 0, DATEDIFF(dd, 0, date)) d
from vwimagesall where date>getdate()-365

this returns 70,000 rows but takes 5 minutes to run (it's a very large table)

is there anyway I an optimize it more

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 04:11:38

whats the purpose of distinct? what all are the indexes available on the table?

Have you had a look at execution plan?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 04:19:41

how do i check execution plan

this is actually a view joining 2 tables

I'm doing this query as I need to get

1.distinct number of days
2. minimum date
3. maximum date

the problem is together these are adding 12 minutes to the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 04:28:31

execution plan can be viewed by clicking on display actual execution plan in ssms and then running your query

Is view complicated one with lots of aggregation etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 04:39:15

view is just a union between 2 tables

but the trick is there is a lot of info in the tables - it's dealing with a large amount the data but I don't think it should take so long

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 04:47:14

quote:
Originally posted by esthera

view is just a union between 2 tables

but the trick is there is a lot of info in the tables - it's dealing with a large amount the data but I don't think it should take so long

union? that will also be a big performance hit..especially when the tables are large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 04:53:11

not sure how to read it but this part of the sp is 24 % query cost

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 05:12:19

how can i get around a union

I have 2 ways images are inserted to 2 different tables

and i need to combine the 2 together in order to get these numbers that I need

would I be better taking a count from both and adding together?
also a min and max from both and taking what i need

would that be more efficient?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 05:12:26

sorry which part? i think you missed posting it!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 05:17:27

i meant this part that queries this union

when commenting the rest goes quickly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 05:20:06

quote:
Originally posted by esthera

how can i get around a union

I have 2 ways images are inserted to 2 different tables

and i need to combine the 2 together in order to get these numbers that I need

would I be better taking a count from both and adding together?
also a min and max from both and taking what i need

would that be more efficient?

images? so you want count of image datatype field?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 05:36:08

it's a table of images and there information

I am trying now instead of the union to make a temp table and insert the data and then pull it from there

running now to see if it's quicker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 05:37:47

hmm...that might prove to be better now that source contains image field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 05:46:48

it's a bit to better but still too long

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-04 : 06:31:57

May you share the view query?

Cheers
MIK

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 06:47:48

i got rid of the view and did this instead

create table #picdays(
site nvarchar(10)
,mydate datetime
)

insert into #picdays(site,mydate)
select distinct site,DATEADD(dd, 0, DATEDIFF(dd, 0, date)) d
from images.dbo.imagesfrompv where date>getdate()-365

insert into #picdays(site,mydate)
select distinct site,DATEADD(dd, 0, DATEDIFF(dd, 0, date)) d
from images.dbo.images where date>getdate()-365;

with pictdays as (

select distinct site,mydate from #picdays

group by site,mydate

)

update #sites
set online = t2.d
from (select COUNT(*) d, site
from pictdays
group by site) t2
where sitecode = t2.site

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 07:02:22

you dont need distinct if you're already grouping on same columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-06-04 : 07:33:58

thanks for your help - I got it much quicker

the biggest issue was the union

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-04 : 08:01:00

Not sure if your view query was like this or different, but you may like to compare the performance of below query with your one(s)

--There might be syntax issues with the query
SELECT [Site],Counts
--UPDATE X SET [Online]=Counts
FROM #sites X
INNER JOIN
(
SELECT [Site],COUNT(1) as Counts
FROM
(select [site],CONVERT(date,[date]) d
from images.dbo.imagesfrompv where [date]>getdate()-365
UNION
select [site],CONVERT(date,[date]) d
from images.dbo.images where date>getdate()-365
)T
)Y
ON X.sitecode=Y.Site

Reason: I believe adding data to temporary table is an extra overhead and have found this kind of approach usefull only if I need to reuse the same dataset multiple times in a SP. temp table appears to be used just once so I think the overhead can be reduced by not using it.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources