Solved

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

Posted on 2012-04-05
7
271 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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