MS-SQL Search by keyword performance

Hello experts,
I'm a newbie here and do not know if this is the right place to ask this question, or if there are some one else already solving this from elsewhere. if so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system would need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or productname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the database is a huge db which might contains mililion of records. If i doing a standard SQL join to perform the SQL selection, i would afraid about the performance of the whole syste
m.
Is Full-text index search could be applied for this or is there any other solution?
Thanks for your helps!
Doan
Message posted via http://www.sqlmonster.com
This is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQLMonster.c om...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.sqlmonster.com
|||Thanks Wayne for your speedy suggest.
by the way:

>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tables? (Product & Category), or need i consider some special skill here?
Thanks
Doan
Message posted via http://www.sqlmonster.com

MS-SQL Search by keyword performance

Hello experts,
I'm a newbie here and do not know if this is the right place to ask this que
stion, or if there are some one else already solving this from elsewhere. if
so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with
the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system wou
ld need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or produ
ctname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the d
atabase is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the per
formance of the whole syste
m.
Is Full-text index search could be applied for this or is there any other so
lution?
Thanks for your helps!
Doan
Message posted via http://www.droptable.comThis is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via droptable.com" <forum@.droptable.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQ
droptable.com...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname

contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.droptable.com|||Thanks Wayne for your speedy suggest.
by the way:

>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tab
les? (Product & Category), or need i consider some special skill here?
Thanks
Doan
Message posted via http://www.droptable.com

MS-SQL Search by keyword performance

Hello experts,
I'm a newbie here and do not know if this is the right place to ask this question, or if there are some one else already solving this from elsewhere. if so, please accept my appologies.
My problem is that, i do not know what is the right solution to dealing with the search module which will need to be implemented in my application.
e.g: i have a master table is Order with the following fields
(Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
Product(Product_id, Category_Id, ProductName, Price)
Category(Category_id, Description)
My Search support for user to enter a string, once hit on search, system would need to returned all matched Order for the search string.
example: if i enter [Toy], then system will return all Order which:
- The Cus_name contains [toy] or Cus_address contains [toy] or productname contain [toy] or category description contains [toy]
actually, the real senarios might be more complex than this sample and the database is a huge db which might contains mililion of records. If i doing a standard SQL join to perform the SQL selection, i would afraid about the performance of the whole system.
Is Full-text index search could be applied for this or is there any other solution?
Thanks for your helps!
Doan
--
Message posted via http://www.sqlmonster.comThis is exactly what full-text search is intended to do... Setting it up is
documented in books on line... after you have indexed all of the fields, you
can to a multi column search ie..
select * from Orders where contains(*,'Toy')
The * in the contains clause says to search ALL indexed text columns
have fun
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Doan Ly via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cb1e2d67657544229ad2c2b16d7513ea@.SQLMonster.com...
> Hello experts,
> I'm a newbie here and do not know if this is the right place to ask this
question, or if there are some one else already solving this from elsewhere.
if so, please accept my appologies.
> My problem is that, i do not know what is the right solution to dealing
with the search module which will need to be implemented in my application.
> e.g: i have a master table is Order with the following fields
> (Order_id, Product_Id, Order_Number, Cus_name, Cus_address)
> Product(Product_id, Category_Id, ProductName, Price)
> Category(Category_id, Description)
> My Search support for user to enter a string, once hit on search, system
would need to returned all matched Order for the search string.
> example: if i enter [Toy], then system will return all Order which:
> - The Cus_name contains [toy] or Cus_address contains [toy] or productname
contain [toy] or category description contains [toy]
> actually, the real senarios might be more complex than this sample and the
database is a huge db which might contains mililion of records. If i doing a
standard SQL join to perform the SQL selection, i would afraid about the
performance of the whole system.
> Is Full-text index search could be applied for this or is there any other
solution?
> Thanks for your helps!
> Doan
> --
> Message posted via http://www.sqlmonster.com|||Thanks Wayne for your speedy suggest.
by the way:
>select * from Orders where contains(*,'Toy')
>The * in the contains clause says to search ALL indexed text columns
Could it also look for the matched full-text indexed fields in its child tables? (Product & Category), or need i consider some special skill here?
Thanks
Doan
--
Message posted via http://www.sqlmonster.com

MSSQL Profiler

Hi everyone,

Please help me, I would like to monitor my database performance using MSSQL Profiler, what would be the ideal parameter that I would take in considerations and what are the baselines for the values.

Thanks a lot.I am not sure what your question is.
I assume you are basically asking on how to use profiler.
I don't think it is a permanent thing. Profiler usually used to investigate some problem areas like excessive CPU usage or prolonged run time for some procedures. This way you narrow down problem areas and work on them to minimize resources.

I dont put any parameters in for general history if I dont know what I am looking for exactly because I want to see full picture on what is going on. But if I need to limit for example to one user and see what he is doing I could do that too but when I have specific interest.

Hope I pointed you in right direction.|||

Quote:

Originally Posted by iburyak

I am not sure what your question is.
I assume you are basically asking on how to use profiler.
I don't think it is a permanent thing. Profiler usually used to investigate some problem areas like excessive CPU usage or prolonged run time for some procedures. This way you narrow down problem areas and work on them to minimize resources.

I dont put any parameters in for general history if I dont know what I am looking for exactly because I want to see full picture on what is going on. But if I need to limit for example to one user and see what he is doing I could do that too but when I have specific interest.

Hope I pointed you in right direction.

Hello,

Thanks for the input. I have one more question, let's say that my database has the history of having a long run time and the problem was identified in 1 stored procedure but I want to make sure that I will filter all aspects that causes the long run time; is Profiler capable of activating itself whenever it reaches a certain threshold of long run time or is there something I could use for this kind of situation?

Many thanks.|||If you know exactly which procedure is having a long run already, not sure profiler is where you go after that.
Showplan is where you'll get your hints on places for improvements.
For example you can see where table scan used and put indexes in place, which step is taking longer time.

If you interested in how Profiler works. Just start a new trace and see what information you get back and how helpful it is for you.|||The only think I can think of with a long running procedure and Profiler is when you suspect that it runs faster at different circumstances or even different times of day.
If you see slowness consistently it means you have to rework this procedure differently to make it faster.
It happened with me multiple times where in time I had to redo existing objects to make environment healthier. I am sure while long running procedure is in progress you are having blocks and all users can feel slowness or even no responses from the Server.|||

Quote:

Originally Posted by iburyak

The only think I can think of with a long running procedure and Profiler is when you suspect that it runs faster at different circumstances or even different times of day.
If you see slowness consistently it means you have to rework this procedure differently to make it faster.
It happened with me multiple times where in time I had to redo existing objects to make environment healthier. I am sure while long running procedure is in progress you are having blocks and all users can feel slowness or even no responses from the Server.

Hi,

Thanks a lot. You've been a great help.

MSSQL ODBC vs. ADO

We have in our applications written in VC++ access also to MSSQL but only over ODBC. Is there any performance difference (is ADO faster) between ODBC and ADO access? And if yes how much it could be ?
Thanks for your answer.Yes, ADO is faster. How much faster depends on a lot of things but the generally accepted figures range from three to ten times faster.

-PatP