Full Text search performance tuning.

Hi Everyone,
I have a performance issue with a SQL Server query and i need some
assistance with it. It would be great if you could offer your inputs on
it.
We have a SQL Server database that is of a rather large size [20GB +].
The most significant table "Order_Detail" has a large number of rows [a
few million rows]. We need a query that will retrieve a keyword search
result in the shortest span of time from this table. The resultset we
need is dependent on a few other tables. The query we use has joins on
these tables. These other tables are also of similar sizes.
The problem is that the query takes too long a time to execute to be
practical.Could anyone take a look at the query and let me know how to
optimise it best ?
FTSI is not implemented on the Order_Details table.
The tables - Order, Order_Detail, Order_Tracker are indexed on the
"order_id" column.
-- SQL query --
SELECT o.order_id as "Order ID", o.date AS 'Received date',
od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
'Email Text'
FROM Order o, Order_Details od, Order_Tracker ot, Customer c
WHERE od.order_ID = o.order_ID
AND od.customerID = c.customerID
AND od.poID = ot.poID
AND o.date >= ** X date **
AND o.date <= ** Y date **
AND ot.Email=c.Email
AND ot.Msg like '%<Keyword to be searched for>%'
ORDER BY o.order_ID, o.date, o.customerID
-- End of query --
*Note->The query is modified to give you a sense of what i am trying to
do. It is not the exact same query i use here.
We tried to use FTSI and "CONTAINS" to search for the keyword. It gives
better performance but only marginally. What are your thoughts on using
FTSI when the query will end up using complex joins and large volumes
of data ? If this approach will solve our performance issue, can
someone direct me on how best to do it ?
Our only constraints are that the database structure cannot be
modified. However the SQL query or the approach can be.
Thank you for your inputs in advance!
Regards,
Venky
The short answer is that other than full-text indexing an indexed view in
sql 2005 or partitioning by date there is no easy way to increase your
performance. I suspect you may also have a lazy spool. Could you post the
execution plan here so we can evaluate it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<rv.venky@.gmail.com> wrote in message
news:1159890834.003818.162910@.m73g2000cwd.googlegr oups.com...
> Hi Everyone,
> I have a performance issue with a SQL Server query and i need some
> assistance with it. It would be great if you could offer your inputs on
> it.
> We have a SQL Server database that is of a rather large size [20GB +].
> The most significant table "Order_Detail" has a large number of rows [a
> few million rows]. We need a query that will retrieve a keyword search
> result in the shortest span of time from this table. The resultset we
> need is dependent on a few other tables. The query we use has joins on
> these tables. These other tables are also of similar sizes.
> The problem is that the query takes too long a time to execute to be
> practical.Could anyone take a look at the query and let me know how to
> optimise it best ?
> FTSI is not implemented on the Order_Details table.
> The tables - Order, Order_Detail, Order_Tracker are indexed on the
> "order_id" column.
> -- SQL query --
> SELECT o.order_id as "Order ID", o.date AS 'Received date',
> od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
> 'Email Text'
> FROM Order o, Order_Details od, Order_Tracker ot, Customer c
> WHERE od.order_ID = o.order_ID
> AND od.customerID = c.customerID
> AND od.poID = ot.poID
> AND o.date >= ** X date **
> AND o.date <= ** Y date **
> AND ot.Email=c.Email
> AND ot.Msg like '%<Keyword to be searched for>%'
> ORDER BY o.order_ID, o.date, o.customerID
> -- End of query --
> *Note->The query is modified to give you a sense of what i am trying to
> do. It is not the exact same query i use here.
>
> We tried to use FTSI and "CONTAINS" to search for the keyword. It gives
> better performance but only marginally. What are your thoughts on using
> FTSI when the query will end up using complex joins and large volumes
> of data ? If this approach will solve our performance issue, can
> someone direct me on how best to do it ?
> Our only constraints are that the database structure cannot be
> modified. However the SQL query or the approach can be.
> Thank you for your inputs in advance!
> Regards,
> Venky
>
|||Hello Venky
Please post the scripts for your tables. This is essential to work out what
needs to be indexed.
Why are you not joining Order_tracker to Order by OrderId?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi Everyone,
> I have a performance issue with a SQL Server query and i need some
> assistance with it. It would be great if you could offer your inputs
> on
> it.
> We have a SQL Server database that is of a rather large size [20GB +].
> The most significant table "Order_Detail" has a large number of rows
> [a few million rows]. We need a query that will retrieve a keyword
> search result in the shortest span of time from this table. The
> resultset we need is dependent on a few other tables. The query we use
> has joins on these tables. These other tables are also of similar
> sizes.
> The problem is that the query takes too long a time to execute to be
> practical.Could anyone take a look at the query and let me know how to
> optimise it best ?
> FTSI is not implemented on the Order_Details table.
> The tables - Order, Order_Detail, Order_Tracker are indexed on the
> "order_id" column.
> -- SQL query --
> SELECT o.order_id as "Order ID", o.date AS 'Received date',
> od.po_amount as "Amount", c.EmailAdress AS 'Email_address', ot.msg as
> 'Email Text'
> FROM Order o, Order_Details od, Order_Tracker ot, Customer c
> WHERE od.order_ID = o.order_ID
> AND od.customerID = c.customerID
> AND od.poID = ot.poID
> AND o.date >= ** X date **
> AND o.date <= ** Y date **
> AND ot.Email=c.Email
> AND ot.Msg like '%<Keyword to be searched for>%'
> ORDER BY o.order_ID, o.date, o.customerID
> -- End of query --
> *Note->The query is modified to give you a sense of what i am trying
> to do. It is not the exact same query i use here.
> We tried to use FTSI and "CONTAINS" to search for the keyword. It
> gives
> better performance but only marginally. What are your thoughts on
> using
> FTSI when the query will end up using complex joins and large volumes
> of data ? If this approach will solve our performance issue, can
> someone direct me on how best to do it ?
> Our only constraints are that the database structure cannot be
> modified. However the SQL query or the approach can be.
> Thank you for your inputs in advance!
> Regards,
> Venky