Improving SQL View performance (1 Viewer)

I started using MS SQL Server about 18 months ago. It took me a while to figure out the basics. At this point, I'm ready to go "fix" some of the methods I used with better methods, with the initial intent of just improving performance.

I'd like to tackle a series of reports. All of these reports use the same "base" view to perform general filtering. For example, a user (from Access) may select a LOCATION and a MIN and/or MAX DATE. They then select one of the accompanying reports.

I created a settings table (on SQL) that includes: USERID, SETTING, and SETTINGVALUE.

I created a simple stored procedure that will look up this value, called uSetting.

The user will select a location, min date, and max date, OR they may leave any of these blank to "disable" filtering by that criteria.

MS Access updates the SQL settings table values using a pass-thru query.

The "base" view will filter the raw source table data based on the values found in the settings table. This is accomplished using CASE statements in the FILTER column of the view. For example:

SELECT dbo.tblABC.Location
WHERE (CASE WHEN (Select dbo.uSetting('LOCATION')) = '' THEN (SELECT tblABC.Location) ELSE (SELECT dbo.uSetting('LOCATION)) END


Interpreted, this will return all values if the LOCATION is empty (user did not make a selection) and return only matching values when a selection is made.

This has been working fine. Now that the data volume is growing, I'm seeing longer and longer processing times. The effect is compounded as some reports have over a dozen filtering fields, and may link quite a few tables in the process. I need to figure out a better way before this becomes crippling.

Open to suggestions! Thanks!

Suggest the first thing to confirm is that your tables are efficiently designed and properly indexed. Also for sql server maintenance it is important to refresh the tables on a regular basis (equivalent to part of an access compact and repair) to tidy up the indexes - a recent application I was involved with saw a 90%+ improvement in response times when this was undertaken.

It also sounds like your 'base view' may be a problem - if this is a massive 'spreadsheet' type view which you are effectively filtering through your settings table, it will not be particular efficient - ideally criteria needs to be applied before creating the view. You may also want to consider indexing the view -see these two links

https://msdn.microsoft.com/en-us/library/ms191432.aspx
http://stackoverflow.com/questions/1923802/how-do-table-indexes-come-into-play-when-using-a-view

The other thing to take into account is the amount of other work that sql server is doing - if someone in another db on the same server is making heavy use of resources, this will have an impact on what you are doing.

Don't be fooled into thinking that SQL Server is faster than Access - generally speaking, it isn't. OK it has some additional functions that often improve performance and of course it has server side processing (equivalent of running access on terminal server) and four cores for processing against access's (or express's) one, but it is often doing a lot more than just the one db you are running so these advantages from a performance perspective are often diminished.

The other aspect is network performance - if you are bringing a lot more data across the network, it will be slower - so look at that as well - only bring across the fields and rows you actually need

A reliable way to kill performance is to apply a function to every record. For example extracting the Month from a date field, then filtering or ordering by month.

Worse still is applying an Access user defined function before filtering.

Thanks for the feedback. I've been hitting a stone wall on what to do for quite some time. I still need more direction on improved methods. Basically I need information on how to:

-Refresh tables (like access compact)
-Properly index tables. A good article would be helpful.
-How to apply criteria before creating the view? I thought this was the purpose of a view
-How can I filter records without applying a function to each record? Some reports may have functions on 10 or more fields

Reading through the links above are over my head. Too many terms that I don't understand. I find most articles from Microsoft expect a level of proficiency higher than what I am at, making it very difficult to understand.

I find most articles from Microsoft expect a level of proficiency higher than what I am at, making it very difficult to understand.

Click to expand...

Spending time understanding is the only way to become more proficient

there are plenty of articles out there. I've given up providing links to general requests for information because what suits one person doesn't suit the next. And I'm not here to be your research assistant. So google your question, if you don't understand the first link, go on to the next one. But google properly - use key words, find things, see it is sometimes called something else, google again. If its about VBA, use VBA in your search string. If it's about Access VBA, use Access as well.

refresh indexes - google something meaningful like 'SQL Server - refresh indexes'

Properly index tables - google something meaningful like 'database indexing' or 'best practice for database indexing'

How to apply criteria before creating the view? I thought this was the purpose of a view - google 'what are sql server views'

How can I filter records without applying a function to each record? - depends what you mean by function but think Galaxioms comment may be slightly out of context. He is referring to things like domain functions (dcount, dsum etc) or functions you have written in vba being used in the query which I don't think you are doing - but if you are - don't.

Another forum that would rather ridicule than provide actual help. Honestly, I'd rather get no response than get the "go figure it out for yourself" response. I've used google, A LOT. I know how to use boolean search engines to get what I want, but sometimes you need a person to help guide you. Two years ago I bought the "SQL for dummies" book, but it has only got me so far. Guess it's back to the trial and error method.

Another forum that would rather ridicule than provide actual help

Click to expand...

sorry you feel that way but you've had guidance. I would point you in the direction of the MS articles as they are what I find helpful but you say you don't so not much point me pointing you that way. You've also been provided with a none MS link and provided no information that you have looked at other threads, so no idea of your googling skills.

What you are asking is not new and has been asked many times before so you will find threads on other forums. This is an access forum and although it covers sql server to some extent you might be better served going to a sql server forum.

good luck with your project

Sensei said:

-Properly index tables. A good article would be helpful.

Click to expand...

Indexes create overheads when records are inserted or updated so avoid excessive use. Index any field where query criteria are applied.

-How to apply criteria before creating the view? I thought this was the purpose of a view

Click to expand...

Views do not support parameters. A Stored Procedure can be used to filter but these are not able to be linked to Access like a view or table. However they can be Executed using a Pass Through query or return a recordset to Access using an ADO Command.

With the PassThrough query you need to edit the SQL of the query to change the parameters. Note that PassTrough queries must use the SQL Server syntax.

Access will pass a query on linked tables to SQL Server if it it can. Just make sure all the tables in the query are on the server and the functions are standard SQL. Don't join a local table to a server table in a query or use a user defined function on a field before it can select.

-How can I filter records without applying a function to each record? Some reports may have functions on 10 or more fields

Click to expand...

In the case of dates, use a field Between criteria. It is fine to apply functions to the criteria. Functions are fine if you are applying them to values in records that have already been selected, just don't select based on the return values of a function applied to records.

Let's see if I'm following:
Currently, I'm using views that call custom functions(which they use parameters) to look up values in a settings table to filter directly in the view (original post). More data, more stacked functions, worse performance.

Are you suggesting that I create procedures for each view, that will re-write the view using "hard" filters (such as "Florida" instead of dbo.uSetting('LOCATION')), then execute the view? That is a ton of work(currently have 194 views), and a paradigm shift, that I am totally willing to do if that's the right way. This makes a lot of sense to me, as the db doesn't have to pause of each record to run multiple functions, over and over again. Am I understanding this correctly? Appreciate the support - sorry for short response earlier. I am at my wits end!

I have just read your original post so I now have better perspective on your question and see you have quite a bit of experience in SQL Server.

So the query you posted is the Filter on the Location column of the view?

That is correct. It's just a portion of the actual view SQL for simplicity sake. So, there are two possible outcomes from this CASE statement:

Location=SameLocation (non-filtering to show all results, just like 1=1 is always true)
-or-
Location=SelectedLocation (exact matches only).

While this method works, performance is not optimal, especially when I have multiple filters using the CASE statement (which in turn calls a stored procedure), resulting in multiple lookups for every record.

Fundamentally, I just don't understand the "right" way to go about this. My previous post may be an option, but then again re-writing Views may become a conflict issue when 2 or more users are trying to access the same report at the same time. I just don't know.