Solved

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

Posted on 2012-04-05
7
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 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