How to handle large amounts of data without timing out sql server or web app

I have a marketing application that has section where a user can build a targeted marketing list by performing a search based on selected criteria. In this instance the user has people in groups and is creating a list of all people in all groups. The query to get this list has several joins to pull back the desired information. it contains a WHERE IN that has the ID's for each group. Once a user has 50 or more groups selected and runs the search the application times out on the sql server box.

I need to know if there is a method to performing searches that will get large amounts of data and not time out on the sql server or the web app.

I have attached a txt file with the query for reference. Lets just say in each ID in the WHERE IN section there are at least 100 people records in each group.

Thanks in advance for any help or suggestions.
marketing-query.txt
RCairns54Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Do you have indexes on:
People: PeopleID
PeopleCenters: PeopleID
                           AddressID
                           CEID, CenterID
Address: AddressID
                StateID
                CountryID
States: StateID
Countries: CountryID
Centers: CEID, CenterID
LeagueMembers: PeopleID
                               LeagueID
Leagues: LeagueID
Anthony PerkinsCommented:
That should have read:
People: PeopleID
              AddressID
PeopleCenters: PeopleID
                           CEID, CenterID

Have you considered wrapping up those LeagueIDs into a temporary table and joining against that?
What is your front-end language?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RCairns54Author Commented:
Yes, all the indexes are there.

I haven't tried using a temp table to join against.

The application is in C# and Silverlight. It communicates with a WCF service that talks to sql server 2008. Even running the query through the management studio never finishes. The sql box has 32gb ram and plenty of processing speed....
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Anthony PerkinsCommented:
Yes, all the indexes are there.
...
Even running the query through the management studio never finishes

This does not make any sense.  How many rows are your returning?  Have you tried loading the LeagueIDs into a well indexed temp table and then JOINing against it in SSMS?
RCairns54Author Commented:
The problem is the query , Its searching 8 million records , Too many criterias I guess.
Anthony PerkinsCommented:
No. Your query is not optimized.  8 million rows is nothing when using tables that are indexed appropriately and a query that is written efficiently.
RCairns54Author Commented:
Turns out I removed one of the joins and the query started working much better.

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.