Help with a more efficient Search query

[dsmaj]

Alright, I don't want to hear any laughing about this one I realize that this query isn't likely the most efficient way of achieving my desired results, however it is the only way I can get it to work the way I need it to. Modifying my table structure is not an option, as A) I've written too much table dependent code to modify it now, and B) I don't think there's anything wrong with my table structure, and I'm happy with the way it works for me in general. Anyway, I'm going to post the general query that the search is modeled after, and then the code that builds that SQL to give an idea of how my search needs to work. It's fairly basic, but here's a rundown of my table layout anyway...

For this particular search I've got a main table that I'm searching (tbl_Initiative), which is linked via many to one relationships to 5 other tables. As far as I can tell, this part is fine--I've joined all of the above mentioned tables in my query (since even if I'm not searching on their values, I still need to return their values...) In addition to these many to one relationships, I have two complex one to many relationships to tbl_InitiativeBuildingBlock and tbl_InitiativeFundingSource. These two tables each contain a InitiativeID field as a primary key through which they are linked to tbl_Initiative, and then a BuildingBlockID or FundingSourceID field respectively which links (many-to-one) to either tbl_BuildingBlock or tbl_FundingSource. These two tables contain just a few records each which serve as data entry options (like in combo boxes and stuff) I have things setup this way because I have other tbl_********BuildingBlock/FundingSource tables which use the same tbl_BuildingBlock/FundingSource values. In the interest of Normalization, I have split tbl_BuildingBlock and tbl_FundingSource off into more generic multi-purpose tables, and just link the more specific tbl_InitiativeBuildingBlock and tbl_InitiativeFundingSource tables to them. I hope this is making sense so far...if not, the code will probably clear things up a bit (or make things more confusing!). Anyhow, I cannot join these two tables, since that would result in multiple duplicate Initiatives being returned when there are multiple tbl_InitiativeBuildingBlock and tbl_InitiativeFundingSource entries associated with a tbl_Initiative entry. I've worked around this by building two subqueries to search both tbl_initiativebuildingblock and tbl_initiativefundingsource, both of which join their respective supporting tables (tbl_BuildingBlock and tbl_FundingSource), and then I use the resulting recordset to search for Initiatives that are strictly associated with tbl_InitiativeBuildingBlock/InitiativeFundingSource records.

From an interface perspective I've dealt with the many to one relationships in the obvious fashion (textboxes/comboboxes), and have used two multiselect-listboxes to return the full contents of both tbl_BuildingBlock and tbl_FundingSource, which the user can select from, and then my contorted query uses the selections to restrict the returned initiatives...

Without further ado, here's the SQL Query that I'm basing all of this on:

SELECT tbl_Initiative.InitiativeID, tbl_Initiative.InitiativeName, tbl_Initiative.InitiativeStartDate, tbl_Initiative.InitiativeEndDate
FROM tbl_ActivityType RIGHT JOIN (tbl_Jurisdiction RIGHT JOIN (tbl_InitiativeType RIGHT JOIN (tbl_SponsorOrganization RIGHT JOIN tbl_Initiative ON tbl_SponsorOrganization.SponsorOrganizationID = tbl_Initiative.SponsorOrganizationID) ON tbl_InitiativeType.InitiativeTypeID = tbl_Initiative.InitiativeTypeID) ON tbl_Jurisdiction.JurisdictionID = tbl_Initiative.JurisdictionID) ON tbl_ActivityType.ActivityTypeID = tbl_Initiative.ActivityTypeID

WHERE tbl_Initiative.InitiativeID IN (SELECT tbl_InitiativeBuildingBlock.InitiativeID
                                                           FROM tbl_BuildingBlock INNER JOIN tbl_InitiativeBuildingBlock ON tbl_BuildingBlock.BuildingBlockID = tbl_InitiativeBuildingBlock.BuildingBlockID
                                                           WHERE tbl_BuildingBlock.BuildingBlockID = 1)

AND tbl_Initiative.InitiativeID in (SELECT tbl_InitiativeFundingSource.InitiativeID
                                                                  FROM tbl_FundingSource INNER JOIN tbl_InitiativeFundingSource ON tbl_FundingSource.FundingSourceID = tbl_InitiativeFundingSource.FundingSourceID
                                                                  WHERE tbl_FundingSource.FundingSourceID = 2)

ORDER BY tbl_Initiative.InitiativeID;

The '1' and '2' values in there are just for testing purposes...

I'll post the code in a reply to this post...the forum is telling me my post is too long...(It's a sign )

 

[dsmaj]

And the code....

' *****************************
' **** BUILD SQL SEARCH STRING ****
' *****************************
    ' Select fields and join supporting tables...
    strSQLFields = "SELECT tbl_Initiative.InitiativeID, tbl_Initiative.FutureFundingPriorityID, tbl_Initiative.JurisdictionID, tbl_Initiative.SponsorOrganizationID, " & _
                        "           tbl_Initiative.ActivityTypeID, tbl_Initiative.InitiativeTypeID, tbl_Initiative.InitiativeName, tbl_Initiative.InitiativeDescription, " & _
                        "           tbl_Initiative.InitiativeStartDate, tbl_Initiative.InitiativeEndDate, tbl_Initiative.InitiativeContactPhoneNumber, " & _
                        "           tbl_Initiative.InitiativeContactPhoneExtension, tbl_Initiative.InitiativeContactMailingAddress, tbl_Initiative.InitiativeContactWebsite " & _
                        "FROM tbl_ActivityType RIGHT JOIN (tbl_Jurisdiction RIGHT JOIN " & _
                        "           (tbl_InitiativeType RIGHT JOIN (tbl_SponsorOrganization RIGHT JOIN tbl_Initiative " & _
                        "           ON tbl_SponsorOrganization.SponsorOrganizationID = tbl_Initiative.SponsorOrganizationID) " & _
                        "           ON tbl_InitiativeType.InitiativeTypeID = tbl_Initiative.InitiativeTypeID) " & _
                        "           ON tbl_Jurisdiction.JurisdictionID = tbl_Initiative.JurisdictionID) ON tbl_ActivityType.ActivityTypeID = tbl_Initiative.ActivityTypeID "
    
    ' Build a WHERE statement for the search criteria
    strSQLWhere = "WHERE "
    
    ' Initiative Name
    If Not IsNull(Me!txtName.Value) And Not IsEmpty(Me!txtName.Value) And Me!txtName.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.InitiativeName LIKE '" & Me!txtName.Value & "' AND "
    End If
    
    ' Initiative Start Date (Date Range)
    If Not IsNull(Me!txtStartDate.Value) And Not IsEmpty(Me!txtStartDate.Value) And Me!txtStartDate.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.InitiativeStartDate > #" & Me!txtStartDate.Value & "# AND "
    End If
    
    ' Initiative End Date (Date Range)
    If Not IsNull(Me!txtEndDate.Value) And Not IsEmpty(Me!txtEndDate.Value) And Me!txtEndDate.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.InitiativeEndDate < #" & Me!txtEndDate.Value & "# AND "
    End If
    
    ' Initiative Type
    If Not IsNull(Me!cmbInitiativeType.Value) And Not IsEmpty(Me!cmbInitiativeType.Value) And Me!cmbInitiativeType.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.InitiativeTypeID = " & Me!cmbInitiativeType.Column(1) & " AND "
    End If
    
    ' Activity Type
    If Not IsNull(Me!cmbActivityType.Value) And Not IsEmpty(Me!cmbActivityType.Value) And Me!cmbActivityType.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.ActivityTypeID = " & Me!cmbActivityType.Column(1) & " AND "
    End If
    
    ' Jurisdiction
    If Not IsNull(Me!txtJurisdiction.Value) And Not IsEmpty(Me!txtJurisdiction.Value) And Me!txtJurisdiction.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.JurisdictionID = " & Me!txtJurisdictionID.Value & " AND "
    End If
    
    ' Sponsor Organization
    If Not IsNull(Me!txtSponsorOrganization.Value) And Not IsEmpty(Me!txtSponsorOrganization.Value) And Me!txtSponsorOrganization.Value <> "" Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.SponsorOrganizationID = " & Me!txtOrganizationID.Value & " AND "
    End If
    
    ' Determine which BuildingBlocks were selected as search criteria
    If lstBuildingBlocks.ItemsSelected.Count > 0 Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.InitiativeID IN (SELECT tbl_InitiativeBuildingBlock.InitiativeID " & _
                                                   "FROM tbl_BuildingBlock INNER JOIN tbl_InitiativeBuildingBlock ON " & _
                                                   "tbl_BuildingBlock.BuildingBlockID = tbl_InitiativeBuildingBlock.BuildingBlockID " & _
                                                   "WHERE tbl_BuildingBlock.BuildingBlockID = "

        For i = 0 To lstBuildingBlocks.ListCount - 1
            ' Add the selected BuildingBlocks to the search criteria
            If lstBuildingBlocks.Selected(i) Then
                strSQLWhere = strSQLWhere & lstBuildingBlocks.Column(1, i) & " AND tbl_BuildingBlock.BuildingBlockID = "
            End If
        Next i
        strSQLWhere = Mid(strSQLWhere, 1, (Len(strSQLWhere) - Len(" AND tbl_BuildingBlock.BuildingBlockID = ")))
        strSQLWhere = strSQLWhere & ") AND "
    End If
    
    ' Determine which FundingSources were selected as search criteria
    If lstFundingSources.ItemsSelected.Count > 0 Then
        strSQLWhere = strSQLWhere & "tbl_Initiative.InitiativeID in (SELECT tbl_InitiativeFundingSource.InitiativeID " & _
                                                   "FROM tbl_FundingSource INNER JOIN tbl_InitiativeFundingSource ON " & _
                                                   "tbl_FundingSource.FundingSourceID = tbl_InitiativeFundingSource.FundingSourceID " & _
                                                   "WHERE tbl_FundingSource.FundingSourceID = "
                                                   
        For i = 0 To lstFundingSources.ListCount - 1
            ' Add the selected FundingSources to the search criteria
            If lstFundingSources.Selected(i) Then
                strSQLWhere = strSQLWhere & lstFundingSources.Column(1, i) & " AND tbl_FundingSource.FundingSourceID = "
            End If
        Next i
        strSQLWhere = Mid(strSQLWhere, 1, (Len(strSQLWhere) - Len(" AND tbl_FundingSource.FundingSourceID = ")))
        strSQLWhere = strSQLWhere & ") AND "
    End If
    
    ' If No Search Criteria was added, remove the WHERE alltogether
    If strSQLWhere = "WHERE " Then
        strSQLWhere = ""
    Else    ' otherwise, remove the trailing " AND "
        strSQLWhere = Mid(strSQLWhere, 1, Len(strSQLWhere) - Len(" AND "))
    End If
    
    ' Terminate the SQL string
    strSQLWhere = strSQLWhere & ";"

As you can see, I've only conditionally included the WHERE elements when needed, in the hopes of speeding things up a bit. In any event though, the query work exactly the way that I want it to...it's just a bit slow, and looks pretty ugly. Does anyone have any tips on how I might streamline this (without modification to my table structure, of course)?

Thanks in advance,
Sam

 

[The_Doc_Man]

Rather than building your queries on the fly, consider the possibility of making them PARAMETER queries, which pre-compiles them.

ALSO: Assure you have an index for every field you can include in a WHERE clause for your search criteria.

ALSO: Consider the possibility of doing at least some of the search in a prior step that involves a smaller recordset and doing the JOIN later.

By the latter, I mean this: If you search a query that is the JOIN of 5 tables and have data from all these tables, this is inherently SLOWER than JOINING 5 tables to the recordset that results from searching the main table by itself, particularly if you don't need the extra data for the search. (You yourself said you didn't search the 5 JOINed tables, you only reported data from them.)

Basically, the fewer characters in the record being searched, the more records can fit into a single buffer. The more records in the buffer, the faster the search runs because it imples fewer disk operations to fill buffers, which means fewer disk operations in total.

And from a hypergeometric viewpoint, a search of (A JOIN (B,C,D,E,F)) should be the same in final effect as (a search of A) JOIN (B,C,D,E,F).

Does this make sense?

 

[dsmaj]

Rather than building your queries on the fly, consider the possibility of making them PARAMETER queries, which pre-compiles them.

ALSO: Assure you have an index for every field you can include in a WHERE clause for your search criteria.

ALSO: Consider the possibility of doing at least some of the search in a prior step that involves a smaller recordset and doing the JOIN later.

By the latter, I mean this: If you search a query that is the JOIN of 5 tables and have data from all these tables, this is inherently SLOWER than JOINING 5 tables to the recordset that results from searching the main table by itself, particularly if you don't need the extra data for the search. (You yourself said you didn't search the 5 JOINed tables, you only reported data from them.)

Basically, the fewer characters in the record being searched, the more records can fit into a single buffer. The more records in the buffer, the faster the search runs because it imples fewer disk operations to fill buffers, which means fewer disk operations in total.

And from a hypergeometric viewpoint, a search of (A JOIN (B,C,D,E,F)) should be the same in final effect as (a search of A) JOIN (B,C,D,E,F).

Does this make sense?

Thanks for the reply Doc_Man,
As for Parameter queries...I did consider this, and while I have a lot of stored querydefs with parameters for much of the other functionality of my database, doing so for my search queries would require that I store a whole bunch of different permutations of the query (since I can't modify structural query elements on the fly). If performance becomes enough of an issues, and can't be resolved through streamlining the query, this may be something I'll just have to do anyway.

All of my WHERE criteria are indexed--although in the early stages of getting this search working it was running reaaaaaaaaaaaally slowly, and it ended up just being one of the search fields was inadvertently unindexed (definitely something to look out for, for others reading this...)

With regards to the rest of your post...could I achieve what you're talking about by just doing a subquery subsequent to the JOINing within my main query? I would rather do this all in one query, rather than pass queries and recordsets back and forth over the network in order to do it in code...If I understand what you're saying however, it's basically that I should try (wherever possible) to slim down my resultant recordset before joining tables with data that I need, but that aren't part of the search...is this basically correct? To do this though, I would need to make my query even more dynamic than it already is, since in SOME cases those tables that I'm not necessarily using for searching, are in fact used for searching. It completely depends on what the user decides to search on. I'm going to try doing this just to test anyway though....it does sound like it would be much quicker...Thanks for the info

Sam