SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Narrow search
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/06/2000 :  09:27:34  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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
15655 Posts

Posted - 12/06/2000 :  13:15:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000