Solved

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

Posted on 2012-04-05
7
249 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
The problem is the query , Its searching 8 million records , Too many criterias I guess.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
Turns out I removed one of the joins and the query started working much better.

Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

772 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

10 Experts available now in Live!

Get 1:1 Help Now