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 |
|
biggs
Starting Member
10 Posts |
 Posted - 2006-02-08 : 01:34:44
|
| Hi.I have a classic ASP app that needs to pull back some fields from a big table of some 10 million rows. For the purpose of making this question easier to ask, lets assume I have the following inputs to a query. I’m going to use a car example as it’s simple to follow.1 VehicleID10 PartIDThese inputs come from form input, or sometimes variables, inside the app, and are not in a table. And the trick is that there are not always going to be ten PartIDs. A simplified version of the query is something like: SELECT whatever FROM Table WHERE VehicleID = VehicleID AND (PartID = PartID1 OR PartID = PartID2 …)I don’t want to hit the database more than once. And I would also like benefit of putting my 10 possible PartIDs in a table or some other construct to allow me to just output HTML from a simple loop that does everything.I’ve never had a good reason to use a temp table. That was my plan of approach here. But I’ve also heard there are other options that might be better. I want this thing to be as efficient as possible. Thanks. |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-02-08 : 01:45:43
|
| hi,Use in Clause instead of multiple ORsSELECT whatever FROM Table WHERE VehicleID = VehicleID AND PartID in(PartID1,PartID2,PartID3…) |
 |
|
|
biggs
Starting Member
10 Posts |
Posted - 2006-02-08 : 02:49:49
|
| Hi.I forgot all about IN. But one thing here is that I am looking to get my HTML form post input into the data itself. And I'm not always going to return any data for a given PartID. The output of the query should reflect that.What I am trying to do is group my output. So let's say my input is 'Honda Accord' for a VehicleID, and 'Engine', 'Door', and 'Fender' for PartIDs 1-3. I want an output that's grouped so I can just dump it to HTML or whatever.If I make a temp table with nothing in it but my 10 possible PartIDs, 3 in this example, then I can left join it to my inventory table and output my data inclusive of PartIDs where no inventory matched.I am just wondering if temp tables themselves are the answer. Are there better alternatives to make some other sort of construct or array that can do it. |
|
|
|
biggs
Starting Member
10 Posts |
Posted - 2006-02-08 : 02:52:06
|
| Sorry, and by group, I mean in a logical sense, not in a group by sense. There's no summary aspect to this thing. I just want tabbed output from my one real table, and one set of values that I am debating putting into a temp table. |
|
|
|
|
|
|
|
|
help with temp tables and alternatives