logical views

Question

logical views

Anamika

SSCertifiable

Points: 6202
More actions
January 6, 2012 at 3:52 am

#249146

Hi,
i'm not able to index a view as it is built on several tables from different servers. does the logical view gets generated on the fly every time?
what could be the solution to fix performance issue on this?
Thanks,
Regards,
Ami

Viewing 13 posts - 1 through 12 (of 12 total)

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

You will need to tune the indexes on the individual tables. I would get the statement out of the view and look at tuning that as you would normally.

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

Views are nothing more than saved select statements. They're inlined into the query during parsing and executed as part of the full query.

You'd tune it the way you tune any other query (tune the queries that use the view, not the view by itself)

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

Anamika SSCertifiable Points: 6202 More actions · Answer 3

Hi,

Thanks for your inputs. the data resides in datawarehouse. and resides in different servers. merging the databases or duplicating the same data through some ETL process or is there any other approach do you suggest?

Thanks,

Regards,

Ami

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 4

The answer can’t be a straight yes/no; it depends on many things. For Example, if you introduce ETL it needs to be in SYNC with rest of the databases (frequency unknown). You might introduce ETL bugs in data. Also it requires double the storage (duplicated data). Yes, the reports based on Logical View *might* perform well.

As others pointed out, you should focus on query for optimization for view. Please post its query execution plan (actual), view definition & sample data. We will analyze it further.

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

That's a huge question, and it's more a data design and architecture than a technical one. Consolidation may be a good idea, but then you have to consider how often the data changes, how you're going to sync the changes, how stale the data can be for the queries you're running, etc, etc. It's not a quick fix and it's generally a huge project (with planning and design) of its own.

Or it may be as 'simple' as moving databases on separate servers onto a single server, though that requires its own planning and analysis and design.

As for other solutions, it really depends what you're doing and we don't have enough information on that. If you can give more details, maybe we can offer other suggestions.

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

Anamika SSCertifiable Points: 6202 More actions · Answer 6

hi,

One huge thanks, 🙂

sorry, i can't post the execution plan. (company standards)

I'm just analysing in the same way. how best i can tune or rewrite the query to get the best out of it. started tuning the underlying tables.

Before taking a decision on merging / ETL i just thought of getting an overview from you people.

If the rewriting / tuning is not solving the problem, I have to check the feasability of other options.

Thanks for your inputs,

Ami

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

Pity about the exec plan, I don't understand why companies prohibit posting those, it's not as if there's top-secret information included in a plan.

Try starting with these for tuning

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Also Grant Frichey's book on execution plans (http://www.sqlservercentral.com/articles/books/65831) and his book (for purchase) on tuning SQL (http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=sr_1_1?s=books&ie=UTF8&qid=1325945395&sr=1-1).

Merging is a huge undertaking, not something you would do on a whim based on the advice of random people who have no idea of your systems. To give you an idea, it is something I proposed in two cases to fix cross-server performance. One client declined due to the huge amount of work it would have involved (and that was just moving databases from three servers onto one) and the other is still deciding whether it's worth it or not.

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

Anamika SSCertifiable Points: 6202 More actions · Answer 8

the execution plan will reveal the database names and the column names. I don't want to take any risk on that.

thanks for the inputs.

going through the articles.

thnx

regards,

ami

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

Anamika (1/7/2012)
the execution plan will reveal the database names and the column names. I don't want to take any risk on that.

And? If I were to tell you that a database name was CardPortal and there was a table called Transactions, what secrets would it give away? There's only so many ways to design a database and probably the worst thing that someone could tell from a column list is how badly (or not) the database is designed.

I know, management rules. Still, it hinders us and you and protects little.

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

Anamika SSCertifiable Points: 6202 More actions · Answer 10

true,

i have newly joined to this company. I see a lot of design changes.

the dimension tables are not having clustered indexes 🙁

and fact tables are having datetime as one of the composite keys.

to make it into clustered and to change the datetime to integers (by adding time dimension with date values) i have to get permissions. 🙁

but the query should run fast by doing only tuning....... 🙂

little struggling between management processes and addressing people. Will find a way soon..... 🙂

ami

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

Anamika (1/7/2012)
little struggling between management processes and addressing people. Will find a way soon..... 🙂

Good luck.

If you need any motivations for any of the changes you want, post here. I'm sure many of us here have been in that kind of situation a few times.

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

Anamika SSCertifiable Points: 6202 More actions · Answer 12

Thanks a lot....

I should really tell this...

you people are really helping and motivating me a lot. Many a times I found best solutions with you people.

Thanks a lot.

Regards,

Ami