View performance issue - 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

General SQL Server Forums

New to SQL Server Programming

View performance issue

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-08-06 : 04:22:09

Hi all,

I am getting a performance issue that is slowing my server to a crawl, I have a view on the frontpage of my site that seems to be causing some performance issues, my site has recently started getting between 10 - 20k hits a day. My friend suggested I put the view in a stored proc then insert the data into a table - this could be ran when the view was updated. What do you think?

view:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER view [dbo].[frontpageSpecials] as
select  top 7 * from (select departuredate = convert(varchar, departuredate, 3),departureairportname,duration,country,resort,boardshort,price,pricebasedon,id,rating,'/holiday/special-offer/?id=' as tablename from vholiday where frontpage = 1
union all
select departuredate = convert(varchar, departuredate, 3),departureairportname,duration,country,resort=city,boardshort,price,pricebasedon=pricefor,id,rating,'/city/special-offer/?id=' as tablename from vcitybreaks where frontpage = 1
union all
select departuredate = convert(varchar, availablefrom, 3),departureairportname,duration,country,resort=city,boardshort,price,pricebasedon=pricefor,id,rating,'/travel/special-offer/?id=' as tablename from vluxury where frontpage = 1 ) as u1 order by 1 desc

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-08-06 : 05:20:40

1. can you post an expected/actual execution plan? (text or graphical)

2. why are you converting dates to varchar? sorting character values is different from sorting dates values.

3. Also how much data is in each of the tables? can you post DDL as well?

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-08-06 : 05:43:34

the count for the tables ( they are actually views ) is 143,379 and 112

here is the exec plan [url]http://www.dell-e.co.uk/execplan.zip[/url]

I'm using SQL 2005

I had to convert the dates to varchar, I think it was because I couldn't join them otherwise

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-08-06 : 06:44:42

Well I just deleted 32000 records from one of the underlying tables and it reduced the cost in the execution plan by about 20% with relevant processes. What do you think about the suggestion about putting in place a stored procedure?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-08-06 : 09:55:55

At a very quick glance....your SQL contains loads (o.k. several) table + clustered index scans.
Scans are bad news....unless the tables involved are VERY small. (I'm not good at reading the XML form of your execution plan...the interactive graphical form in QA is far easier for my level of expertise)

I suspect either the tables have no indices or have no indices of use to the SQL code.

Making this code into a SP probably won't make much of a difference.

Can you break down/examine the "view" code(s) and see if you can tune any one element by getting the query to switch over from "scans" to "index seeks"?....you may have to add/amend indices (including order of columns) to get improvements. And then repeat the exercise across all the views.

How slow is slow?.....did you benchmark/time the performance?

Optimisation is a mixture of an art & a science...involving a fair bit of experimentation. Have a go and revert with details of how you are getting on. Posting sample SQL query (for one/the views) + DDL will help

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-08-06 : 11:07:22

The idea was to put the guts of the view in a statement that would create a separate flat table with no joins that could be queried by the frontend. The query itself didn't seem to be running to slow the problem was that it seemed to be hogging the server time so other parts of the site were timing out. What version of SQL server do you use? I only seem to have one choice when outputting execution plans.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-08-07 : 09:45:23

SQL 2K

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-08-07 : 12:24:40

I think text execution plans have been removed on 2005

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources