SQL Server View in Access

[dan08]

I have created a SQL Server View that limits the data a person can see based on information about them stored in another table. I am using this view as a record source for a form in Access. This form takes something like 15 seconds to load because the view is kind of complex. The view is dynamic in that it is different for each user, but does not change once a user has opened the Access Database. I want to know if there is a way to set the linked table as static so that it just loads when the database opens and then is treated as a regular table.

 

[gmmastros]

Complex does not necessarily mean slow. If you post the definition for the view, there may be some suggestions we can make to improve the performance.

You can add schema binding to the view. Once you do this, you can also index the view. Since you say the data is different for each user, I assume you are filtering on userid (or something similar). You could add an index to the view on the column which would likely speed up the view.

One thing to be aware of... If you add schema binding to the view, you will no longer be able to modify the table structure of the tables involved in the view. Specifically... if you have 10 columns in table A, but the view is only using 4 of those columns, you will not be able to change those 4 columns (changing the data type, length, removing the columns, etc...) If you ever find yourself in a situation where you NEED to change them, you'll need to drop the view first or change it to not use schema binding.

Adding indexes to views is a very good way to improve performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I suppose I should have mentioned how to create a schema bound view. It's simple, really.

In a normal view, you have....

Create View YourViewName
As
-- The query for your view

Simply add 'With schemabinding', and specify the schema that the view belongs to, like this.

[code]
Create View [!]dbo.[/!]YourViewName
[!]WITH SCHEMABINDING[/!]
As
-- The query for your view

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dan08]

I would like to avoid anything that might affect the underlying tables because this is part of a group of tables containing lots of financial data.

The code for the view is something like this

SELECT Top 100 * FROM Finance
Where Department = 'dbo.fn_gerUserDept(dbo.fn_getUser())'
ORDER BY [Invoice Date] DESC

getUser uses some code to grab the username currently logged in to windows
getUserDept look through a table of user information to find the department associated with that username.

 

[gmmastros]

Please answer the following questions:

1. If you connect to SQL Server using SQL Server Management Studio, and run the view, how long does it take to return data?

2. is dbo.fn_getUser a scalar valued function?

Select name, type_desc 
From   sys.objects 
where  name = 'dbo.fn_getUser'

3. is dbo.fn_gerUserDept as scalar valued function?

Select name, type_desc 
From   sys.objects 
where  name = 'dbo.fn_getUserDept'

4. What indexes to you have on the Finanace table?

You don't need to post all of the results from the query shown below. I really want to know if there is an index where the index_key has Department as the first column, and 'Invoice Date' as the second column.

sp_helpindex 'Finance'

More than anything, I suspect that adding an index to the finance table will make a big difference.

If you don't see an index with index_key = "Department, Invoice Date", then I would suggest you try adding the index to see if it makes a difference in performance.

Create index idx_Finance_Department_Invoice_Date On Finance(Department, [Invoice Date])

So.... create the index and then check performance. If the performance isn't any better, I would suggest that you drop the index. There's nothing worse in a database than an index that never gets used. To drop the index....

Drop Index Finance.idx_Finance_Department_Invoice_Date

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dan08]

The query takes about 15 seconds, in SQL Server and Access.

Both functions are scalar-valued

The Finance table has an autonumber index as the first field

I want to only return 100 most recent entries, so I have to sort the table DESC and get the top 100. When I added this I noticed the performance really went down. Is there a way to pull the last 100 so I don't have to order the whole table?

 

[gmmastros]

Let's clarify...

When I added this

Are you referring to the index?

the performance really went down

Do you mean the performance got a lot better because the execution time went down? If so, how long does it take now? (just curious)

Is there a way to pull the last 100 so I don't have to order the whole table?

Adding an index does not sort the whole table. It only sorts the data contained within the index and a hidden "pointer" to the row in the real table. In this case, the hidden data is probably an int (4 bytes), Department (probably a string), and Invoice Date is probably a DateTime (8 bytes).

Basically, what I'm saying is that the index size is relatively small. You cannot really pull the last 100 because the last 100 depends on the department (which is why it is included in the index).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dan08]

I meant the performance went down when I added the ORDER BY statement. The table already has an index. I tried ordering by the index and it didn't change the performance. I don't think there is a way to get this view to execute much quicker because it has to check with different unrelated tables. Instead I would like a way to only execute the query once and then have the view in Access as a table that doesn't need to run the query for the view each time. Right now it seems like its running the whole query each time I request the table.

 

[gmmastros]

The table already has an index.

Tables can (and often do) have multiple indexes. You don't want to go index crazy because modification statements, like Insert, update, and delete are slower when there are multiple indexes. In most cases, you'll never notice the difference in performance with the inserts, updates, and deletes. That is... until you continue adding more and more indexes.

In my database, I have approximately 300 tables. I have approximately 680 indexes. About 200 of my tables only have one index (for the primary key). My most heavily indexed table has 22 indexes (which is probably double the number that there should be). Usually, the larger the table, the more indexes you have because there are more queries run against it, and larger tables benefit more from indexes than smaller tables.

Regardless, I do encourage you to add the index that I suggested. I think you will be pleasantly surprised. It's not likely to add much to the size of your database. It's not likely to affect the performance of your updates, inserts, and deletes (to the point where you would notice it). It'll only make your query faster, at the expense of a little extra database size.

Other than making the query faster (which is my strong recommendation), you may want to think about persisting the data on the Access side. You could make a global recordset object that stores the results of the query. When your app starts, load the data. Whenever you want to use it, just use it. I am no Access guru (like I am with SQL Server), but I suspect it wouldn't be that difficult to persist the data in ram within your access app.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TamarGranor]

If dbo.fn_gerUserDept(dbo.fn_getUser()) returns a single value, why not run a query once to return that value and then pass the value as a parameter to the view?

Or are you saying you need to keep the whole list of the 100 most recent records from the Finance table for the specified department? Are you using that list then in joins with other tables? If not, store it on the Access side for the duration of the session. (Not sure how you do that. In Visual FoxPro, you'd just put it in a cursor and keep it around.)

Tamar

 

[dan08]

Hey guys, I finally got back around to working on this problem. In addition to filtering the Finance table I also create a small view of a user info using the getUser function (and only holds the info for the current user). So instead of using WHERE finance.username = getUser, I created an INNER JOIN on Finance.User = UserTable.User. This is lightning fast. Thanks for your help guys.