Query Optimizer

Question

Post reply

Query Optimizer

dreamslogic

Ten Centuries

Points: 1131
More actions
June 13, 2009 at 7:38 pm

#131120

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736478

June 13, 2009 at 9:42 pm

#1009225

Which choices?

dreamslogic Ten Centuries Points: 1131 More actions · Answer 2

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

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.