Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-06-15 : 08:32:17
|
| Steve writes "I am currently writing an application that needed a very comprehensive, but easy to use distribution list system. The job of the system is to pull out ContactID's based on any criteria on the system. It's a very large system, which stores a very broad range of data about contacts (about 70 tables total), so the critera for selecting the contacts has to be just as broad. On the software side of things, I have written a filter system which basically uses a set of plugins to handle different criteria. Each plugin handles its interface (so the user can select the desired criteria) and handles it's processing and generates it's results and stores them in one of the tables in the database. Up to that point, everything is working fine. The plugins are getting the criteria from the user, and getting the correct results and storing them individually in the database.The problem comes when I try and pull the final list of contacts out of the database by combining the results of each plugin. Each plugin has to be dealt with seperately, as the user can select whether to AND, OR or NOT the data returned by each plugin together. Currently with 8 plugins specified for a list it is taking over a minute to execute the SQL I am generating, which I am very sure is not very efficient. The question is how can I re-write a statement like the one below so that it can still be generated by the plugin information stored in the database but it will execute on the server faster.SELECT DISTINCT ContactID FROM crmContact WHERE ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 93) AND ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 98) OR ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 99) OR ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 100) OR ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 101) OR ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 102) OR ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 103) AND NOT ContactID IN(SELECT ContactID FROM lstRunFilter WHERE RunID = 100 AND ListFilterID = 94)Essentially each IN statement subquery fetches the result for one 'run' (execution) for an individual 'filter' (plugin). Each of those subqueries are AND'd, OR'd, or AND NOT'd together and finally DISTINCT'd. If you could provide any kind of insight into how I can speed that statement up I'd be very grateful. If you require any further information please feel free to ask. Thanks!" |
|
|
vallis
Starting Member
12 Posts |
|
|
|
|
|
|
|
Plugin based search system getting bogged down 