| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 12/06/2000 : 09:27:34
|
Andrzej writes "I am wondering how the narrow search functionality should work for huge database content.
Right now, I have only two solutions in my mind:
1. After each subquery add some filter string to SQL statement
and execute query once again
2. Create temporary table to store information from previous
query and search in the resultset
However, I think that both solutions are wrong, because:
1. This solution causes creating longer query which affects the
same set of rows every time. It is going to take a lot of
time for huge table content.
Besides, SQL Server has limited length of the query string
2. If the first resulstet is saved then the huge amount of rows
may be added to temporary table... It can take forever...
I would very appreciate any response if you have an idea, how to do the efficient narrow searching for big tables.
Thanks,
Andrew" |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 12/06/2000 : 13:15:07
|
It sounds like you are opening a table using ADO, applying a filter or filters, and reopening the new recordset, and then applying more filters...Yes?
If yes, use a SELECT statement and WHERE clause instead. You can add as many search conditions as you like:
>> SELECT * FROM myTable WHERE field1=value1 AND field2=value2...AND fieldN=valueN
If you are already using SELECT, and it's still slow, you can add indexes to the most commonly searched fields. Don't add more than you need (unless the table is not updated frequently) as it will slow update, delete, and insert performance. If the data updates frequently, you can use "DBCC DBREINDEX" or "UPDATE STATISTICS" on the affected tables to update the indexes.
|
 |
|
| |
Topic |
|
|
|
Narrow search
