SQL Server :: Best Way To Speed Up Searching?
Feb 1, 2011
I have a web application running on ASP.NET 3.5 which basically stores, retrieves, and allows users to manipulate data from a SQL Server 2005 database. One of the features allows users to search through the records in the database. When the system was first launched last year, there were no records, so searches were lightning fast. However, over the past year, about 100,000 records have been entered, so most searches now take over 15 seconds to perform. I'd like to speed up searches, but there are problems with both of the conventional approaches I can think of:
Caching - The database does all the heavy lifting in this application - the ASP.NET web application is little more than a UI that passes user input to the correct stored procedure, and displays the returned output. The stored procedures are responsible for interpreting user input, applying the business rules, and transforming the underlying tables and views into a context-sensitive result set which the application just displays as is. Unless if there's a way to apply a stored procedure to a C# object/collection (the cached records), and get another C# object/collection back, effectively bypassing the database, I don't think this will work.Stored procedure optimization - There's some optimization potential here, but even a simple SELECT * FROM TheView statement on the underlying view currently takes 11 seconds according to SSMS, and I expect that the amount of time taken will continue to increase as more records are entered.Are there are techniques out there that I'm not aware of, which could be used to bring searching down to a reasonable amount of time (5 seconds or less per search)?
View 4 Replies
Similar Messages:
Sep 14, 2010
We have a database with a lot of information about Persons. I won't post the entire database structure because it is too big, but it looks something like this:
Person
ID
Name
Street
City
State
Country
Language
LangCode
Language
Interest
ID
LastChangedBy
LastChangedOn
LocalizedInterest
InterestID
LangCode
Description
PersonInterest
PersonID
InterestID
Now, this is just a small example. In our database, we have about 8-9 localized tables (like Interest) a Person can be linked to. A Person can have multiple Interests, a Person can have multiple Jobs, a Person can have multiple Educations, a Person can have multiple Experiences, I have to build a search function. Let's say you enter "tom" as the search term. This should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables.
If you enter multiple words to search for (eg. "tom php"), it should give a list of all Persons with "tom" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables AND "php" in their Name, Street, City, State, Country, in one of their Interests, in one of their Jobs, in one of their Educations, in one of their Experiences or in one of the other linked tables. At the moment there are about 4,500 records in the Person table and if I do an outer join of Person with all the tables I have to search, there are about 1,300,000 records and 40-50 fields to search in.
How should I approach this problem so that performance will be acceptable? The client expects "something like Google" in terms of speed and ease of use. We're using MS SQL Server 2000 and ASP.NET 2.0. The search functionality has to be added to an existing application and changing the technology or database structure is not an option.
View 1 Replies
Mar 23, 2011
suppose i have column values:
3
6
3,6
36
363
i want to display column which contain "6" means
6 and 3,6 not 36 and 363
so i am using full text search:
use PMS
go
EXEC sp_fulltext_database 'enable'
go
CREATE FULLTEXT CATALOG pmscatalog
go
CREATE FULLTEXT INDEX ON PMS.dbo.emp_project
(
empid
Language 0X0
)
KEY INDEX PK_emp_project ON pmscatalog
WITH CHANGE_TRACKING AUTO
SELECT empid
FROM emp_project
WHERE CONTAINS(empid, '"6"')
but it is not returning anything...
View 1 Replies
Sep 16, 2010
I want to query an SQL table using some Web controls to provide the parameters for filtering the records in the table. Results are displayed in a GridView.
I have a TextBox with an ID of "keyword" where the user may enter one or more keywords. I'd like to search 2 columns for instances of these keywords.
I also have a DropDownList named "category" that lists categories contained in a "category" column of the table.
I have a dataset with a TableAdapter for the table I want to search. Got it working fine with the DropDown List but not sure how to proceed with the TextBox and keywords.
What SQL query should I use? The user may not enter anything into the textbox, they may enter one word, or multiple words.
This is where I am now:
SELECT * FROM tablename WHERE category = @category AND ??
View 7 Replies
Mar 4, 2011
I have two datetime2(1) fields in my db. I will calculate the time in hours and minutes between them. That I have done this way:
[Code]....
This works. But how can I calculate the avgerage speed if I have drived 665 kilometers in this time? I know how to do it with only houurs but not with the minutes to. I have tried this and I think it works. Is it correct?
[Code]....
View 2 Replies
Feb 9, 2011
My web server and database server are currently both in the same room. However, the web server might be changed to a different server in another building about a mile down the street. I want to make sure this doesn't have a negative impact on the connection speed between the database and web server. Is there a way to test the connection speed from both web servers to the database?
View 3 Replies
Mar 26, 2011
I was searching server software to build a Multiplayer Online Game. All of the servers I found up to now use Java in the server side (ie smartfoxserver)
Today I found this one, Photon, that seems interesting because it uses .NET, you can see it here:
www.exitgames.com
View 4 Replies
Mar 27, 2011
Inspecting om my PLESK 8.2 on APACHE2 with PHP 5.2.3 I saw this. Being a person who loves peed, would I gain anything by disabling all the stuff except PHP? which is the only thing I use really? I'll do anything to speed up my site.
View 1 Replies
Feb 4, 2011
Csharp Code: ..........
I wrote a console app that displays information from a local file on my desktop and displays the content within the file. I was wondering how can I create a function that can search for keywords and only display the words, I want it to search for?I'm trying to display this content in a database and search for the content I want it to display.
* ZIP Codes
* Area Codes
* City Name
* State Name
* Two digit State Code
* City Type
* City Alias Abbrev.
* County Name
* State FIPS
* County FIPS
* Time Zone
* Daylight Savings Indicator
* Number of Businesses
* Q1 Payroll
* Annual Payroll
* Number of Employees
* Employment Flag
* County Growth Rate
I tried something like this - And it didn't work.
Csharp Code:
var keywords = new string[] {"some", "keywords"}; var foundKeywords = File.ReadAllLines("filename"). SelectMany(line => keywords.Where(keyword => line.Contains(keyword))). Distinct();
View 4 Replies
Aug 7, 2010
string First = "Hello World!"; string Second = "llo";
How can I check (true or false) if Second if within First ?
View 2 Replies
Dec 4, 2010
I am writing an 3-tier enterprise asp.net web application which needs to be very lightweigt to lower the bandwidth usage. I want to speed up ASP.NET web page loading, I have minified all js and css, used AJAX, used less images in page, used paging for grids but still the overall response page size goes to nearly 1Mb because of viewstate hidden fields.
View 6 Replies
Apr 15, 2010
im developing some backends in ASP.NET 2.0 and i have put all the images in Cache, GZIPED my CSS, JS files and everything to speedup the load of each options.
the performance its good and i have no problems with the clients but i want "MORE" fast loads and im looking for some recomendations.
Is important to mention that those websites are using only in intranets so im thinking to implement my next projects using IFRAME for content that way (i think) the options will be loading faster because they not have to load the entire site.
EDIT:
im not using viewstates i wrote HTML (code behing) generated controls and use as less as possible ASP.NET Rich controls, all my static contents are in Static vars (Header, Menu, Footer) but im just looking for speed up a litter more.
After compile my website i compress all the HTML, CSS, JS and use less of those files as possible.
but i was seeying others Backend that loads faster than my and the difference is the IFrame control that they use (i think), we check code, design, etc and we use the same (they use #INCLUDES for static HTML instead STATIC VARS with the HTML)
View 5 Replies
Jan 26, 2010
First off I'm a week into asp so apologies in advance for silly questions! I am looking to connect to a SQL database table and allow the user to filter the table based on a search box on the asp page. I have written a sql query with a parameter in the where clause, a bit like this:
select col1, col2
from table
where col1 like @my_parameter
Then I pointed the parameter to a textbox on my asp page, and this seems to work quite well. However, I would now like to add an additional search facility which would be a dropdown list. This would be sourcing all the unique values from the col2 in my table abve (there are only about 10 possible values here). But I don't want users to be able to use both of these searches at the same time.
So the bottom line is I would like to offer two methods of filtering the same data table, which search on different columns of the data table. Then the results should populate the same results table, but the user must only be able to use one search method or the other.
I have done a bit of research but couldn't find anything along these lines (I think it is becuase I don't know the words of the things I should be searching for!). I did find information on a standard postback event or cross page posting - not sure which of these (if any) is the best to use for a beginner?
View 2 Replies
Mar 24, 2010
I have tried to reproduce the drilldown datagrid searching in asp.net in Visual Studio 9 using MS.Net 3.5 and I'm running into problems. I copied the code from the site but I'm getting an error that says "name MyDataGrid is not declared" I get the same error for subsrch, msg, count and all other text boxes. I tried to recreate the datagrid and text boxes by using the VS tools and renaming them but that didn't work either. I still got the same errors with the new names. Any idea why this would happen?
View 1 Replies
Dec 22, 2010
I have a webform that uses a gridview to display a list of records from a table. I also have a search box that allows the users to input their search text and next to it a Find button which will take whatever that is in the search box and match it against each record in the datbase using a simple select query.
Whenever this search is executed it takes up to 20 seconds to display the returned result in the grid. The search query uses the SQl Data connector class through SQL ADO.NET.
However, when I perform the same query in the SQL management studio the search takes a total of 2 seconds. I'm wondering why there is a big difference in timing. I know that the query searches the table of about 4,000 records. I read up on paging but I am new to this concept.
My code in ASP is:
[Code]....
Code behind is:
[Code]....
View 11 Replies
Feb 13, 2010
provide me code or free tools for searching procedure within the site.
I want to search a particular topic by putting keyword in a textbox.
View 2 Replies
Jul 13, 2010
I am having a little trouble in working out the best way in which to query my database. What i am looking to do is to search a using a textbox, the problem is that there is not only one field or one table I want to search for. For instance I have a table of parts that contains the part number, part name, the associated project number, searching for these would be relatively straight forward, I also want to be able to search based on the project name (this is stored in a seperate table) I have thought about using a drop box to allow the user to specify which column they want to search in however this didnt work very well. Ideally i will be able to add more search functions onto this after.
View 6 Replies
Aug 12, 2010
I have a couple tables that are kind of unrelated - id like to search through both of them and create a type that i can sift through later
something like this doesnt work
[Code]....
I basically want to create a list of "AnimalSearchResults" that contains all dogs and all cats that have that name Whats the best way to do something like this?
View 6 Replies
Feb 7, 2011
I'm trying to write a LINQ to SQL for searching my database. What's the best method or LINQ to SQL comparing condition?
View 2 Replies
Apr 22, 2010
I want to search an ASP.net form for all types of validation controls and programmatically add some attributes to them such as ForeColor. Can someone point me in the right direction on this?
View 2 Replies
Feb 1, 2011
i have a,
1) textbox - used for search staffs in school
2) dropdownlist - used to search for department
i need a validation that when both fields are empty, a error message of some sort will appear both of the field can search independently.
View 6 Replies
Sep 13, 2010
Is there a way to measure the time elapsed for a web page to execute?
And if so, is it safe to use that figure to estimate the speed of the web page as the number of users grow and the database grows. Ie: if the page currently takes 1 second to process through x amount of rows in the database, to estimate it would take 2 seconds with 2x rows?
View 2 Replies
Jan 20, 2011
When I browse the asp.net pages on local host, only IE renders pages very fast. In every other browser I have a delay of 1 second or so. Why is that happening and is there any way to speed up the response time in other browsers (web.config, IIS setting)?
View 2 Replies
Jul 8, 2010
how to increase the downlaod speed in IE8. is there any performance tuning or any settings.
View 3 Replies
Jul 1, 2010
For this applications, I will have a table that has a list of users who are "moderators" and they are able to see a part of the website that regular users cannot. I will NOT have a login or anything like that - I have a table that lists all the users who are moderators and if the user who is logged in is a moderator they simply see more of the website.
Right now I am able to get the username of the logged user (these are users who log into a Windows machine, and I simply get their login using a method from System.Security).
The thing I have no idea how to do is actually compare the username to the username in the database that I have. can anyone point me to a tutorial or something that searches through a database to compare the given value and see if there is an identical one in a specific database? I don't want the user to see the database, this is all done "behind the scenes".
View 11 Replies
