.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Optmization in multiple search scenario

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

I have a situation where the tables have more than 500 columns and users have privileges to construct dynamic query using an application. 

we need to optimize search in this scenario. I can't create indexes on all columns or even a single index for all columns. also we need to do this with out changing the database schema and application.

Cheers!!! SqlFrenzy

View Complete Post

More Related Resource Links

search multiple words into textbox and populate all records in gridview related those keywords


I have a textbox and a gridview which is bound to a table

what I want to achieve is a solution where lets say there are two records - e.g. "1. Book and 2. Red", i want a user to type in those words and the gridview shows the results as followed;

1. Book

2. Red

I have set up the sql data source and everything and my like operator is this...

LIKE '%' + @record + '%'

how can I solve this problem?

Filter search over multiple text columns


Hi everyone,

I'm working on a library that stores information on various cases. The case table has several columns that contain descriptive strings on what happened in the case. I would like to create a single filter that returns all cases where the search string is found in any of the columns. I've looked at Dynamic Data Filtering, but as far as I can tell, the "Contains" filter only allows you to connect to each column individually. Has anyone tried this?



search on multiple lists

Team, I have a situation where i have to do a search on multiple lists at once. List A - Field A, Field B List A - Field A, Field C List A - Field A, Field D When a user searches by Field A i want to pull all records from all lists. How do i present it after u have done the search. What web parts should i use? Is is possible to search on multiple lists. How to do it through designer? Do i need to a roll up view and search on it. If its possible.. Should i do join of lists and try to filter it based on FIELD A? Can we filter on joined lists? Any ideas on this .... Thanks a lot in advance.      

Search Multiple Data Base

Hi,I am working on a Large Database application .  I have 5 Product catalog database   with atleast 5+ million product information on each .  database is deployed in multiple servers. . when the user performs a search it should search in 5 different database and combine the  results to show in the search results.product row has a priority key and based on the priority i need to show the results. In case if same results is found  in all the five  database then we need to group the result and shows the count in result column.For exampleIf Product1  is exist inn all the five database database  then i need to show Product1 - count=5and also we have priority index for showing the result . default search should be sort by priority  .Can any body let me know what will be the best approach to do the search on this multiple  database and  process this data to find the duplicates and sort accurding to the sort index..(we are also planing to restrict the max result from each data base) . Thanks

Search across multiple site collections


I am working on a MOSS 2007 farm and like to know what would be a best way to setup Search so that I can search across multiple Site Collections? Basically, our SharePoint farm use to have only one site collection with lots of subsites. We reorganized and  promoted a lot of those subsites to site collections. After the promotion, I noticed that on each of the new site collection under the search dropdown, the only search scope that was available was “This Site”. I was able to fix this by changing search settings and creating a custom scope by providing the link to the Search Center site that had already existed in the root site collection. However, when I try to search from any site collection using the "All Sites" search scope, no results are displayed and I get a blank page. Any idea what I might be doing wrong? Thanks in advance for your help.

Search query with multiple search feilds



I have a search page with four fields. Each field belongs to columns in the database. None of these fields are mandatory, so user can choose which field/feilds they want to enter. I want the search results to be more specific as they enter values in more than one field.

For example.

First name, Last name, Pin code and Mobile are the 4 search fields.


Any help will be appreciated.

Thank you


Search in (multiple value) Parameter (for usage in WHERE-clause)


Hi there,

I'd like to give you a little bit of a background story first in order to explain my problem better:

I have created a report, where there is a parameter with multiple values - in this case country codes like DE, DK, FR, GB etc. .
These values are selected with the help of a query out of a MS SQL 2008 database (Navision).

Now the problem is, that in the customer-database where the single lines contain the information of the country code, there is a country code for each foreign customer, but not for the domestic customers. These are simply blank.
Apparently this can not be changed easily and I have to cope with it.

Example of the problem:

Let's say that the domestic country is DK. So there is no actual value for customers of DK, just a blank 'value'.
The parameterlist contains DK because it is in the country database, where I get the information from.

So if the reportingservices user selects something in the parameter like "DK, GB", I could query something like:

SELECT bla, blub, [Country Code]
FROM    Customer
WHERE  [Country Code] IN (@Country_Paramater, '')

The , '' because I also want to have the empty country codes.  This works fine... until I decide NOT to show domestic data in the parameters.
Example: Reportingservices

Search multiple document library in Query


How to search multiple document library in a sitecollection. these document library has same named : DocLib1, DocLib2, DocLib3 ... they all in a same sitecollection.

How to search document via FullTextSqlQuery or other Search Objects?

If there is another sitecollection structured similiary with this one, how to search all these document library? Can we search all sites documents then use url for filter ?

Thanks a lot.


Search Query From Multiple Tables



I have a order master table in which orderId is PK.

Now i have 5 Diffrent tables in which OrderId is FK. Column Name in each table is same.

I want to find all columns by orderId from all 5 tables. that means If i pass the orderid = 5 then query should check for each table and find the orderId = 5 and get all records from that table.

If you think any logic for solve this problem please write. I really thankfull if i got any clue for this.

Thanks & regards

Shwetamber Chourey


Search DB, Multiple File Groups & Sizing Guidelines?



The following two articles cover the use of a second file group for the Search database however there's no indication of sizing. Are there any guidelines on how large each file group will end-up?

For example, if my Search database is 100GB and I split this out into two file groups will each file group end-up as 50GB each, 60GB in one, 40GB in another, etc...any ideas?





Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.

Binding the multiple drop down boxes in GridView using JQuery in ASP.NET

Here is the code for binding the multiple drop down boxes in GridView using JQuery in ASP.NET

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Upload Multiple File using JQuery in asp.net

In this article I have explained how to upload multiple files using file upload control. I have used jQuery plugin for uploading multiple files.

I have also explained how to check for file type, file maximum size limit using jQuery & also using server side validation code.

Adding Multiple Columns and Rows in GridView without using a Database

This article describes on how to add multiple columns and rows in GridView without using a database. Basically the GridView will be populated with data based on the values entered in each TextBoxes on Button Click and retain the GridView data on post back.

GridView Multiple Filter AJAX Control

This AJAX control enables the user to filter data within any column inside a GridView. The user can add multiple filters and can delete existing ones too.

SharePoint Gets Search Analytics

Microsoft's SharePoint Portal Server 2003 was sold into a large number of organizations based solely on the strength of the search tool. Organizations hungered for a way to find the data they had generated.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend