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!
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!