Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Optimizer Expand / Collapse
Author
Message
Posted Saturday, June 13, 2009 7:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:53 PM
Points: 27, Visits: 202
You have an SQL query that is referencing multiple tables and views. Using a query optimizer you discover your current views are causing the performance drain. You don’t want to replicate the data your view is using in a Table. Of the following, which solution should you choose?
Post #734502
Posted Saturday, June 13, 2009 9:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
Which choices?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #734516
Posted Saturday, June 13, 2009 10:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 10:53 PM
Points: 27, Visits: 202
Hi Steve - Here are the choices

Create a new View
Use a cursor and store the data in a temporary table
Create an Indexed View
Create a User Defined Function to return the needed data
Create a new Table
Post #734527
Posted Saturday, June 13, 2009 11:49 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
dreamslogic (6/13/2009)
Hi Steve - Here are the choices

Create a new View
Use a cursor and store the data in a temporary table
Create an Indexed View
Create a User Defined Function to return the needed data
Create a new Table

That would have an awful lot to do with what the supposed problem with the old views was. Views do not usually cause performance problems, but using them inefficiently can.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #734538
Posted Sunday, June 14, 2009 3:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
dreamslogic (6/13/2009)
Hi Steve - Here are the choices

Create a new View
Use a cursor and store the data in a temporary table
Create an Indexed View
Create a User Defined Function to return the needed data
Create a new Table


None of the above. Rewrite the query and/or view so that they are written optimally and add any necessary indexes.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #734571
Posted Sunday, June 14, 2009 9:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:49 PM
Points: 32,768, Visits: 14,929
Or provide more information. A view isn't anything more than a query that is written over a table(s). The query optimizer looks at that query and includes it in the query you are writing on a view, looking to optimize things.

There is no general advice outside of don't use a cursor.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #734609
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse