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 |
|
Nick
Posting Yak Master
155 Posts |
 Posted - 2003-04-15 : 21:50:37
|
| OK-I know the way that I am doing this just isn't right. It appears to work (although it may only work under some circumstances), but definitely isn't optimized.I need someone to point at me and call me a fool, so I definitively know that it sucks.Here is what I'm trying to do. I have a table with contacts (tblContacts). Right now it is small but over the next 6-8 months it will probably grow to about 200,000 rows. Each contact can be associated with zero or more other properties. (country, city, jurisdiction, day, sponsor, facility, contact type, or point of interest) I have a page in ASP where the user can select any of those above properties, as well as define a search parameter.I want to pull out all of the contacts that match all of the criteria the user entered. Linked is the code I do it with now. Basically I do a query for each property, and then join them all at the end. This already proves to be slow, so I'm thinking there is a better/faster way to do it. Anyone care to point me in the right direction?[url]http://www.publicconsumption.com/temp/sql.txt[/url] |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-04-16 : 02:39:12
|
| Nick - Looking at your db design, I can only say OMG! Do you really havea table for each property, repeating all the information (thats my understanding anyway)? Could you make a properties table that would list all applicable properties for a contact? I suggest you read this thread http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25429Once you have the database design issue sorted out, a query like this shouldnt be too difficult.OS |
 |
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2003-04-16 : 09:54:53
|
Well it is like that for a bunch of reasons. They are more than just properties. For instance a contact can be associated with a particular point of interest (let's say the Empire State Building.) The contacts table relates to the point of interest table in a 3rd table containing both primary keys. This is because a contact could be related to multiple points of interest, and any point of interest can be related to multiple contacts. The same holds true for all of the properties I talked about before.Each point of interest is it's own entity, which is related with other things in other tables. A properties tables (or even calling it a property, which was my bad) I don't think is a good idea. Am I missing the point of something here?quote:
Nick - Looking at your db design, I can only say OMG! Do you really havea table for each property, repeating all the information (thats my understanding anyway)? Could you make a properties table that would list all applicable properties for a contact? I suggest you read this thread http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25429Once you have the database design issue sorted out, a query like this shouldnt be too difficult.OS
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 10:10:11
|
| Well....I looked at your code, and I have no idea what you're trying to accomplish. What's with the Id = -1? Are they identity columns? If so do you seed them at -1?Anyway, I would lose all of those temp tables and use a Table Datatype for starters, that might help with speed.And it looks like your loading all of your base table in to 10 temp tables (why), the using the search critera for every column in to a #search table (using '%somethg%'...which is bad) AND THEN doing a 10 way outer join to yet another base table (?)I go with mohdowais on this. I'd say if you have to do this it's because of 1 or 2 things. An ER Design in need of a lot of help, or you are trying to all things to all users. If the latter is the case, you need to improve your interaction with them, find out part of what they're looking for, and then reduce your result sets....I'd try and simplify things, personally.Good LuckBrett8-) |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2003-04-16 : 12:51:08
|
| Well I'm passing -1 to the sproc if they didn't choose to narrow their search down on that criteria. In which case it loads all of the contacts into that temp table, and not the narrowed down selection.It's a mess. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 13:59:26
|
Do you know what dynamic sql is? It's something to (EDIT Here); NOTadvocate, but in your case (since I see no other way) it might be more helpful.First, STOP moving all of that data around for no reason. Just go against the base tables. And let me ask, if I as user don't make any selection criteria, then you move all of the data from every table in to temp tables? Everytime you execute the sproc? Sounds like just a little bit of overhead, don't you?So, you're problem is the predicates. The easy part will be to build the select/ from component (because it's static). Soemthing likeDeclare @SQL varchar(4000)Select @SQL = 'SELECT col1, col2, col3, ect'+ ' FROM Table1 a RIGHT JOIN Table2 b ON a.col1 = b.col2 AND ect'+ ' RIGHT JOIN Table3 ON...ect'-- Make sure you get the spacing correct-- , I always end the quout against the string-- , and start a string with a space-- , that way I can see it easier-- Now the Predicate, no too complicated really, but something like:Select @SQL = @SQL + ' WHERE ' + '1=1' -- I'm cheating with '1=1' part. It allows me 1). not to have to worry about not have any criteria-- and 2). I don't have to worry about the AND part of the predicateIf @countryID IS NOT NULL Select @SQl = @SQL + ' AND a.Col1 = @countryId'If @cityID IS NOT NULL SELECT @SQL = @SQL + ' AND a.Col2 = @cityID'If @dayID IS NOT NULL SELECT @SQL = @SQL + ' AND a.Col3 = @dayID'--....ect Hope this helps, but the more I look at this, the more I thing you need a data model review.Good LuckBrett8-)Edited by - x002548 on 04/16/2003 15:01:35 |
|
|
|
Nick
Posting Yak Master
155 Posts |
Posted - 2003-04-16 : 14:58:44
|
| Thanks for your help. I'll play around with it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 15:00:06
|
| Oh, forgot to add, when your done build the sql string, to execute it you need:Exec(@SQL)Good luckBrett8-) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-04-16 : 15:08:45
|
| since you are playing with the code, i just wanted to mention that instead of using 10 temp tables, you could use dynamic SQL like brett suggested and UNION ALL (as opposed to the RIGHT JOINS) the results into one single result-set onto which you could apply other filters.OS |
|
|
|
|
|
|
|
|
Searching Multiple Tables on Multiple Parameters