VergeResources
Programmer
I have a table with well over 10 million rows that contains statewide data. When users access this table, they should be restricted to their own agencies' data. This has been accomplished by creating a view that links their user id to a "security" table and then returns only records that match their assigned agency.
This view works, but the performance is terrible when I start using further restrictions. For instance, if I were to run a query against the true table and ask for a specific agency and date range, it takes about 5 seconds. If I use the view and ask for a date range (in a WHERE clause), it takes almost 2 minutes!
When you apply a WHERE clause to a view, don't the indexes get picked up by the SQL statement hidden in the view?
Anyone have alternate suggestions? Thanks in advance!
This view works, but the performance is terrible when I start using further restrictions. For instance, if I were to run a query against the true table and ask for a specific agency and date range, it takes about 5 seconds. If I use the view and ask for a date range (in a WHERE clause), it takes almost 2 minutes!
When you apply a WHERE clause to a view, don't the indexes get picked up by the SQL statement hidden in the view?
Anyone have alternate suggestions? Thanks in advance!
