10-09-10, 05:11 #1Registered User
- Join Date
- Jan 2003
- Nottinghamshire, UK
Unanswered: Multiple Filters in Dynamic SQL = Big Ouch
I've Just finished a bit of Basic SQL Locking & Concurrency Training for a Client but their main problems are coming from the application design.
They are being forced to display over 20 Filters on the Html front end page (some of them Drop Down List Multi Choice).
They are being forced to display filtered data from over 15 large complex tables (far too much but the Boss insists on it).
They are generating complex Dynamic SQL within the app and running sp_executesql against an equally complex View which joins all the possible tables to cope with all the possible filters.
Question is : Has anyone found an elegant alternative way of doing this that will improve performance and possibly reduce complexity etc?
I'm thinking of things like creating a #temp table with the filter criteria and joining to it etc... I'm sure many others have faced this type of problem before and I want to help them out with architectural approaches.
GW"Everything should be made as simple as possible, but not simpler." - Albert Einstein
"Everything should be made as complex as possible, so I look Cleverer." - Application Developer
10-09-10, 10:13 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Create OOP classes for the display attributes that can be chosen and the filters that can be applied. Create a query class that can contain the display item members, filter members, and integrate them into a dynamic SQL query and execute it. Each display item knows what table it needs, and what column(s) it needs. Each filter item knows what table it needs, and how to apply its filter to that table.
Every time the query object gets retrieved, it should record all the information you need to track in order to optimize your indexing. What columns are used, what tables get used, what filters are applied, and when the query occured are great candidates.
The query object can generate the dynamic SQL from the display and filter objects.
At least in the few cases that I've built, this is remarkably efficient and the information it provides about the queries generated leads to some really great insights into how your users actually use data, and how you need to index and optimize to improve that use. Seeing which objects factor into queries can also lead to some great opportunities for code optimization too. Seeing objects that are rarely or never used can lead to application optimization too.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.