Solved

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

Posted on 2012-04-05
7
267 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:RCairns54
  • 4
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37814579
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
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37814590
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?
0
 

Author Comment

by:RCairns54
ID: 37825678
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....
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37825861
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?
0
 

Author Comment

by:RCairns54
ID: 37844986
The problem is the query , Its searching 8 million records , Too many criterias I guess.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37845048
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.
0
 

Author Comment

by:RCairns54
ID: 37872636
Turns out I removed one of the joins and the query started working much better.

Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now