How to speed up a view?

[icemel]

Hi,

We have a view on a local server that accesses a remote server's tables to create the view. The view is several UNIONS across some pretty big tables.

The queries that access this view take a really long time to return.

Does anyone have any suggestions to make this process faster?? (aside from creating the view from local tables... don't know if this can be done)

Thanks

 

[vongrunt]

One of problems with that concept: WHERE clause used on view cannot be optimized well.

Get rid of view. Seriously.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[icemel]

Actually, it's uglier than that... the view is comprised of unions across "select *" statements!

So it's doing a buncha full table scans to create the view.

It's really more a data warehousing type of query... in fact, that's exactly what it is... we're storing a bunch of email history... and need a fast way of selecting it.

There's gotta be a better way.

 

[mrdenny]

Stored proc will be faster than a view.

Change the select * to list the columns out. Might help a little.

Change from UNION to UNION ALL (this was SQL doesn't need to check for uniqueness on all the rows). (Unless you need that uniqueness).

Do you have the option of creating an index on the view (not sure if you can create an index on a view that crosses servers)?

Can you filter the rows before they come into the view?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[druer]

If it is for a data warehouse why not just have all of the data from those tables inserted into 1 table within the data warehouse? Turn off indexes before the inserts from each of the tables, do the load with inserts or bulk copy or something, then create the indexes on the 1 master table and then do selects against it, instead of having this view that has to get hit against multiple times. Then in the future you only need to query against the real underlying tables for data since a particular date, instead of for all of the data each and every time.

 

[NoCoolHandle]

Denny..

Stored proc will be faster than a view.

are you sure? The only help a proc gets is longer use of cached query plans and those can bite you in the A&^)e if you change parameters.

But it cant run any faster than a view. or for that matter an adhoc sqlstatement (if there is no cached plan) and .....


Now getting rid of select * ! that is a winner.

Personally, I think there are many reasons to never use views, but speed just isn't one.

My 1c

Rob

 

[SQLDenis]

views are excellent if you use horizontal partitioning and or federated databases
For everything else they are just tools for lazy programmers (like me) who don't want to write the same joins over and over again

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

Procs can be faster than a view depending on how the data is joined and how you are filtering it. Granted this is more true in 7 that 2000 as the 2000 optimizer is much better than the 7 optimizer.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[NoCoolHandle]

Denny,

Procs can be faster than a view depending on how the data is joined and how you are filtering it.

That sounds very hollow.
Based on the fact that both are predifined sql statements and they can both contain basicly the same syntax..

so.. if the syntax is the same.. How can a proc run faster?
Especially if you can add a where clause to the view and views can be INDEXED~!

Rob

 

[druer]

NoCool,

I'm not speaking for Denny as I'm sure he has his reasons for mentioning speed difference. But what I've seen in maintenance of others is:

1. Views don't have where conditions because nobody knows when they build them what they will be used for. They simply join a ton of data and the expectation is that then when you join to the view you add the criteria. Meaning an execution plan then has to be put together. Given the stored proc would have the where criteria it has the execution plan pre-compiled. (Which may or may not be a good thing if the number of results may drastically change based on parameter values. But more often than not is a good thing.)

2. Problem with #1 in cases I've found is that occasionally other programmers (never myself of course) end up making the mistake of joinging to the view to pull data, but have nothing in the where clause that can be used in the execution of the view that would limit what it pulls. Whereas, if they were typing the command out in the stored proc, they wouldn't miss the fact that the view is joined for all patients, and they aren't declaring a patient id or something silly.

These are just some observations from my background to partially answer the point of how a proc could be faster. Yes I realize that your specific question was if the two are identical meaning that parameters couldn't be used. But that is a very rare occurance in the environments I've worked in.

 

[vongrunt]

With all that said, my favorite "feature" used in views is TOP 100 PERCENT with explicit ORDER BY.

And personally I'm not much pleased with indexed views (aka: Yet Another Lazy Corporate Solution (tm)) - it's list of requirements and limitations is longer than giraffe's neck.

Partitioned views are somewhat another story IMHO - again, if you keep WHERE clause on a leash.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

And I saw a partitioned view spread over 6 servers with a reasonably even distributeion of 40,000,000 records SUCK in comparison to a single table on a single server.....


I am not saying they are good. PERIOD.

But I refuse to believe in a blanket statemtent of
"Views are slower than Stored Procs"

Yes you could bring back a view with no where clause, but you could just as easliy bring back more rows or columns than you want or need with a stored proc..

Now don't get me wrong. I don't use views.. (unless there might be a performance improvement - and then I test, test, test) and 99.99999% of all my DB activity is via StoredProcs..

But saying that views are slow is like saying all women drive like morons! (it might be correct in some instances but....)

Ok ladies shoot me...

Rob