Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Parent-Child Package Configurations

Hi All,

May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help

Thanks

Have your read the topic in Books Online?
Are you getting errors or it just seems to do nothing?
Basic flow to keep in mind is the Child 'pulls' variables\values from the parent. package. So the parent knows nothing, and the 'configuration' is done in the child package.
I know one tricky thing is you need to get the case correct when you type the name of the 'Parent Variable' in the configuration.

|||

Hi

I am having problems assigning connection value to my variable, I have defined a variable in the child package, defined another variable in the parent package with the same name, however I am having problem when I am assinging this variable the value for the connection, I am not sure if I am following the procedure properly on top of that there is no proper documentation on MSDN on how to assign value to this variable.

Thanks

|||

Hi ,

Can someone point me to some documentation or an article that explains the procedure?

Thanks

|||

So it sounds like your issue is using a variable with a connection manager and not really a parent-child configuration itself?

In general you would use the property expressions feature for something dynmic like this. To build a connection string or the fily or even say a dynamic subject of an email pushed out by the send mail task.

I suggest reading the topic

"Using Property Expressions in Packages " overall and in particular there is a subtopic of "Property Expression for the ConnectionString Property of a Flat File Connection Manager"

There have been several updates to Books Online since SQL 2005 shipped, I have the latest and I cannot recall how much these topics have changed since release.

http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en