May 2, 2016 at 9:12 pm
Hi,
I have a procedure which has below query,
Select a.column,count(distinct(c.column))
from table1 a with (nolock)
inner join table2 b with(nolock) on a.id=b.id
inner join table3 c with (nolock) on charindex(c.colum,a.column)>0
group by a.column
table1 has almost 36,000 rows and query takes almost 40 seconds to load resultset.
a.column has fts index installed on it and it is xml datatype.
I tried to split the joins but results vary due to grouping.
How can I rewrite this query to make it more fast???
May 2, 2016 at 9:44 pm
The first thing that jumps out at me is the
INNER JOIN table3 c ON CHARINDEX(c.Column,a.Column)>0
Joining on a calculated value is just terrible for performance, because you can't index the column. Any chance you could post the table definitions and maybe some sample data (doesnt need to be real, but representative).
May 2, 2016 at 9:58 pm
Hi,
I'm using sys.dm_fts_parser to get values to table3.column. User provides the text to search and parser finds diffrnet combination of words.
XML datatype is used in charindex comparision from table1 and it has 2 more columns in table. One is primary key which is identity column and other has foreign key.
XML has <Product>
<Product_name>details</Product_Name>
<Product_tag>details</Product_tag>
<Product_Description>details </Product_Description>
......it goes on for some more.....................
May 2, 2016 at 10:21 pm
What if you shred the XML to a table, and index it? Then the join can use the index for the subsequent query.
May 2, 2016 at 10:27 pm
Hi,
I have already tried creating primary and secondary(value) xml indexes on the table1.column but have no impact.
May 3, 2016 at 7:54 am
a.column has fts index installed on it and it is xml datatype.
1. Full Text Indexing isn't going to help you here
2. returning XML data in a SQL query is generally slow.
3. Grouping by an XML value is going to completely slow you down.
inner join table3 c with (nolock) on charindex(c.colum,a.column)>0
You're essentially saying WHERE c.colum is LIKE %<XML Value>% - this is killing you too.
count(distinct(c.column))
You should post some DDL and explain exactly what you are trying to do. There is really no way to speed this up without understanding that. This query needs to be re-written.
-- Itzik Ben-Gan 2001
May 3, 2016 at 7:57 am
Does this help a bit?
Select a.mycolumn,count(distinct(c.mycolumn))
from table1 a with (nolock)
inner join table2 b with(nolock) on a.id=b.id
inner join table3 c with (nolock) on a.mycolumn LIKE '%' + c.mycolumn + '%'
group by a.mycolumn
If you're using FTS, you should be using FTS functions instead of normal string functions.
May 3, 2016 at 9:23 am
HI,
Thanks for your response.
I'll explain in more detail. We are trying to provide custom rank to a search.
Table2 and table3 are temp tables and we are using FTS functions to populate it with the words provided by user.
that INNER JOIN is as below,
INNER JOIN @TEMP2 ON CHARINDEX(COLUMN1,CONVERT(NVARCHAR(MAX),XML_COLUMN))>0
LIKE also doesn't work as it slows down due to nvarchar(max).
May 3, 2016 at 9:38 am
HI,
Thanks for your response.
I'll explain in more detail. We are trying to provide custom rank to a search.
Table2 and table3 are temp tables and we are using FTS functions to populate it with the words provided by user.
SELECT a.productid,count(distinct(b.rank),max(rank/a.productid)
from table1 a with (nolock)
inner join table2 b on a.productid=b.productid
inner join table3 c on charindex(c.productname,convert(nvarchar(max),productxml))>0
group by a.productid
LIKE also doesn't work as it slows down due to nvarchar(max).
May 3, 2016 at 10:18 am
Please, read these articles and come back.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply