Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Multiple Filters in Dynamic SQL = Big Ouch

    Hi All

    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).
    Then
    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •