July 16, 2014 at 4:49 am
I start working on a very huge database.I need to know which steps are very important so that i can do an intellegent search in less processing speed.
Do i need Indexing on each table?
Do i need to define StoreProcedures or functions or view?
Do i need to write algorithem for this search?If yes then what type of algorithm do i need.
Example, I have db for properties.Now if some one search 2 bedroom apartment for sale in 'ABC' location.Now there are more than thousand results.I want to show them only
Best result.I will consider other parameters like 2 bedrooms with parking etc etc
Please help me to build a good app
July 16, 2014 at 5:04 am
That's a giant topic and it's kind of hard to give you too much guidance in a forum post. Yes, most tables should have at least a clustered index. In fact, deciding on the clustered index is a fundamental part of the database design. Whether or not you need additional indexes on the tables really depends on the queries being run against the table. Views are used for obfuscation either to deal with some complexity or to apply a level of security to the data. Whether or not you use them is impossible to say based on the information provided. Stored procedures provide a data access layer. Many, most, queries probably don't need to be in stored procedures, just defined as parameterized queries in the code (not ad hoc queries, those are dangerous because of SQL Injection). But some code will benefit from being in a stored procedure where tuning by a DBA can be performed. Yes, additional search algorithms are common additions to business intelligence and understanding around the types of information being requested. Without knowing the business in detail I can't recommend any particular approach. It's commonly expected that some degree of filtering is applied to results in order to show the most likely matches.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2014 at 5:09 am
Grant Fritchey (7/16/2014)
That's a giant topic and it's kind of hard to give you too much guidance in a forum post. Yes, most tables should have at least a clustered index. In fact, deciding on the clustered index is a fundamental part of the database design. Whether or not you need additional indexes on the tables really depends on the queries being run against the table. Views are used for obfuscation either to deal with some complexity or to apply a level of security to the data. Whether or not you use them is impossible to say based on the information provided. Stored procedures provide a data access layer. Many, most, queries probably don't need to be in stored procedures, just defined as parameterized queries in the code (not ad hoc queries, those are dangerous because of SQL Injection). But some code will benefit from being in a stored procedure where tuning by a DBA can be performed. Yes, additional search algorithms are common additions to business intelligence and understanding around the types of information being requested. Without knowing the business in detail I can't recommend any particular approach. It's commonly expected that some degree of filtering is applied to results in order to show the most likely matches.
Agree with Grant here. It's a complex topic but good starting ideas here. You might also download Grant's Execution Plan book (link on the left), and read it to gain more ideas on how to tune things.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply