Search or data mining in SQL server

Question

Search or data mining in SQL server

MuhammadShafiq

SSCrazy

Points: 2509
More actions
August 4, 2014 at 5:37 am

#291675

I am using sql server 2008. I want to make my search query faster and accurate.
DO i need to use any algorithem in SQL?
If yes then which one?
My bussiness model is, i have all properties/ land information and people want to search inside that.
Thanks in advanc

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's a pretty vague question. Could you be any more specific?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 2

I have huge data in my database. It is related to properties information(like property title,price,NoofBeds,NoofBaths,Location,Tower,Comession etc etc) and people searching for propery.

I need to make my search faster and more accurate.Do i need to use any algorithem like

Microsoft Association Algorithm

Microsoft Clustering Algorithm

Microsoft Decision Trees Algorithm

Microsoft Linear Regression Algorithm

Microsoft Logistic Regression Algorithm

Microsoft Naive Bayes Algorithm

Microsoft Neural Network Algorithm

Microsoft Sequence Clustering Algorithm

Microsoft Time Series Algorithm

OR

Only Storeprocedure with search query will work?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Do you understand what data mining is used for? If not, please do a bit of reading on it.

The question is still too vague to be answerable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 4

I am not using data mining here and i do not want to.

Actually i want to search fruitfully in my db.For example if some one search for '2 bedrooms' in 'XYZ' location so there are more than 1000 results and i only want to show then best 10.

Now i have other factores which will define which one is better than other.

For example in above example i will show those who has 'parking' also. then i will see if some have shoping mall near by.

Then i will see who has less price. etc etc

I need to know the way to implement senario like this

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

MuhammadShafiq (8/4/2014)
I am not using data mining here and i do not want to.

Then why are you asking about all the data mining algorithms? That's what all the algorithms you listed are, for data mining.

We can't help you if you're going to continue to be vague about what you're trying to do. I don't know your requirements, I can't see your screen, I can't read your mind.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

You're describing the WHERE clause of a query. You can combine different criteria in the WHERE clause using AND/OR logic to arrive at filtered record sets. There's nothing you must do within a programming or data mining set of functionality. It's simply a matter of writing the T-SQL in a clear and consistent manner that takes advantage of indexes and avoids common code smells. I'd suggest getting a copy of Itzik Ben Gan's book on T-SQL querying in order to arrive at a solid foundation on the basics.

"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

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 7

I am sorry for the ambiguity,let me make things simple,please ignore my previous posting.

I want to search in a huge data where there is a lot of IF & ELSE conditions.What is the good programing practice to do like it?

For example, I have below table

Property(Id,Title,Beds,Baths,Price,Location,Parking)

And i have two passing parameters Beds=2 AND Location=XYZ

This query will give me 1000000 Records.I want to show best ten.Here is how i will define best 10 out of 1000000 ?

1- Check if result has Baths,So more baths will be best

2- Check if result has Less Price

3- Check if result has Parking,so more parking is best

Now also check if i can have all of above options...

Now above will have more IF ELSE statements.

ALSO "Avoid SQL Server functions in the WHERE clause for Performance"

I need to know a professional way

Hope you understand now.

MuhammadShafiq SSCrazy Points: 2509 More actions · Answer 8

Grant Fritchey (8/4/2014)
You're describing the WHERE clause of a query. You can combine different criteria in the WHERE clause using AND/OR logic to arrive at filtered record sets. There's nothing you must do within a programming or data mining set of functionality. It's simply a matter of writing the T-SQL in a clear and consistent manner that takes advantage of indexes and avoids common code smells. I'd suggest getting a copy of Itzik Ben Gan's book on T-SQL querying in order to arrive at a solid foundation on the basics.

Yes, I want to avoid more logical statments in WHERE clause.There are alot of factores which should be taken care.

Is there anyway to reduce WHERE clause?

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

Then you need to order the results and select the top X amount

SELECT

*

FROM

Property

WHERE

Beds = 2

AND

Location = 'XYZ'

ORDER BY

Baths DESC, -- more baths the better

Price ASC, -- less price the better

Parking DESC -- more parking the better

But sounds like you want to do some dynamic SQL of building the where clause up depending on what parameters have been provided.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

anthony.green (8/4/2014)
Then you need to order the results and select the top X amount
SELECT
*
FROM
Property
WHERE
Beds = 2
AND
Location = 'XYZ'
ORDER BY
Baths DESC, -- more baths the better
Price ASC, -- less price the better
Parking DESC -- more parking the better
But sounds like you want to do some dynamic SQL of building the where clause up depending on what parameters have been provided.

And it that is true, then there is a really good post on catch-all queries.

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 11

MuhammadShafiq (8/4/2014)
Grant Fritchey (8/4/2014)
You're describing the WHERE clause of a query. You can combine different criteria in the WHERE clause using AND/OR logic to arrive at filtered record sets. There's nothing you must do within a programming or data mining set of functionality. It's simply a matter of writing the T-SQL in a clear and consistent manner that takes advantage of indexes and avoids common code smells. I'd suggest getting a copy of Itzik Ben Gan's book on T-SQL querying in order to arrive at a solid foundation on the basics.
Yes, I want to avoid more logical statments in WHERE clause.There are alot of factores which should be taken care.
Is there anyway to reduce WHERE clause?

We're still talking about filtering result sets. That's what it always comes down to. And no, there's not a magic way to get that done. You need to provide mechanisms for limiting the result set and for obtaining the appropriate ordering on the result set. Yes, if you have lots of criteria, this makes this more difficult, but then the answer is to change the way the criteria is applied. Some of those mechanisms might include using a Common Table Expression to define the initial filtering criteria and then referencing that within a T-SQL statement with additional criteria. You might build a temporary table out of an initial result set and then provide further filtering there. You might change the way your data is stored so that you're partitioning it by region (or something) so that you're addressing smaller data sets from the beginning. Maybe storing your data in a star schema or some other data retrieval friendly format will work better than a standard OLTP format. Maybe you can even store the data in cubes in Analysis Services allowing for completely different querying mechanisms. But it all comes back to building out an appropriate set of filters. I don't have a trick for getting past that fundamental requirement. Further, based on your generic examples, I can only offer generic solutions.

"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