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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-03-06 : 23:54:57
|
| I have a search function that is currently just executed from ASP. I am trying to improve performance on it, but am a bit weary on which way the more articles I read.I am searching on about 1-10 conditions, most against a column of datatype INT. Should I use control/flow statements, dynamic SQL, I even read and started programming using the COALESCE style of doing it but read that is was bad because it doesnt make good use of indexes. ANy personal opions? My main concern is performance.Thanks Mike |
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-03-07 : 00:09:48
|
| Well, IMHO SQL does a pretty good job of "searches" via basic select statements. I get good performance from million rows tables, just by using "where id=@id" or "where name like @name". The trick is of course to ensure there is an appropriate index. For ranked searches, Graz has a several articles. HTH. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-03-07 : 00:14:13
|
| I think we probably need more detail to be able to help.I'll take a stab at it, the way I'm reading the question:- There's a single INT column that you're querying against- There are 1-10 potential values that you are looking for in that columnIf that's the case, it sounds like it may be a case for dynamic SQL plus the IN clause. The ASP page could build the comma delimited list of 1-10 values that it's looking for and pass that to a stored procedure which generates and executes dynamic SQL searching against the IN clause which contains the comma delimited list. Is that even close to what you're looking for?Cheers-b |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-03-07 : 03:30:23
|
| sorry aiken my mistake I worded it poorlyI should have saidI am searching against 1-10 different columns , most with a datatype INT.So LOU are you saying don't even bother putting it in a stored procedure?Thanks Mike |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-07 : 03:51:54
|
| You should encapsulate the select (as recommended by Lou) in a stored procedure. It will speed up your processing (Re-uses the Chache Plan).-------------------------------------------------------------- |
|
|
|
|
|
|
Dynamic SQL