April 30, 2007 at 5:54 am
We had data in tables for multiple users (Logins) .Each user data is identified by a one column named “USER”. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.
Each table has a column USER and we are queering data based on login user .this is the foreign key of USER table. Each view contains user column in where clause .So for every query we are searching all records .instead of that is there any way to get data with out searching all records.
I heard about table Partitioning, index Partitioning, view Partitioning. Are they helpful to boost my query performance?
April 30, 2007 at 10:47 am
table partitioning is used to partition data, which in turn may help performance, sounds to me more likely you need better indexing. btw views are generally the quickest way to slow down queries.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 9, 2007 at 2:33 pm
If your use the WITH SCHEMABINDING option you can index the view as well. I would suggest you look at things like DTA to see if your views & queries could benefit with additional indexes & statistics as Colin pointed out.
Kurt Zimmerman
DBA
RHWI
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply