Monday, March 26, 2012
Performance question
Our main product catalog is approx. 3.1 million rows, with a full-text index
on 3 (varchar) columns. For the past year as our catalog has grown, we have
experienced continuing performance degredation, to the point that we are
looking at biting the bullet and migrating this application to Oracle Text,
which from our initial testing is several orders of magnitude faster (we're
going to stick with SQL Server for everything else). Obviously we'd like to
avoid that due to cost issues. However, this application is very important
and if we've reached the limit of SQL Server then so be it. Our problem is
that when a customer searches our catalog, we sort the search results based
on their sales rank which does not allow us to use the "top_n" parameter of
containstable or freetexttable. For example, say a customer searches our
catalog for a relatively common word that results in around 72,000 results
(takes approx 11 sec on subsequent runs...over 1 min on first run, which is
the most important statistic). If we were to use (say) n=2000 for the top_n
parameter, our best selling products would not be returned from the FTS
engine. We need to return all the results so we can sort them by sales rank
and display them to customers.
The server is running Windows 2003, with 6GB RAM, 16 x 15,000K RPM SCSI
drive in a RAID 10, in a dual opteron configuration the with transaction log
on a seperate RAID volume. SQL is SQL Server 2000, SP4 (I've included the
output of @.@.version below). Perfmon shows that the server isn't sweating at
all during these queries from a disk, memory, or CPU standpoint, so that
leaves SQL as the performance bottleneck. Our most recent population was
around a month ago, so the catalog is relatively up to date.
We've fooled around with increasing the memory available to FTS, but that
did not seem to make a difference. Perhaps we did not do it right since
mssearch.exe is still only showing about 49,000K in memory--but since we
are using AWE this could be distorted.
We're going to make one last gasp at improving the performance here before
dumping SQL Server and moving to Oracle. Help!
John
@.@.Version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
Sample query:
SELECT distinct <field list>
FROM containstable(<ft-table>, <ft-field>,'"<common term>"') as ct
JOIN <ft-table> t with (nolock) on t.<PK>=ct.[key] //tables are 100%
readonly except during monthly updates, hence the nolocks
join salesRank sr with (nolock) on sr.<PK>=ct.[key]
order by sr.SalesRank
The approach you take for problems like this is to partition your tables,
perhaps in your case by sales rank. For instance you might want to break
your tables into 10 sub tables. One from 1-10, another from 11-20, etc.
Then limit each results set to 100 and union the results. This might end up
more expensive than what you are currently experiencing.
Hilary Cotter
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
"John" <john36356@.community.nospam> wrote in message
news:ebYiMcaWFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Hello,
> Our main product catalog is approx. 3.1 million rows, with a full-text
index
> on 3 (varchar) columns. For the past year as our catalog has grown, we
have
> experienced continuing performance degredation, to the point that we are
> looking at biting the bullet and migrating this application to Oracle
Text,
> which from our initial testing is several orders of magnitude faster
(we're
> going to stick with SQL Server for everything else). Obviously we'd like
to
> avoid that due to cost issues. However, this application is very
important
> and if we've reached the limit of SQL Server then so be it. Our problem
is
> that when a customer searches our catalog, we sort the search results
based
> on their sales rank which does not allow us to use the "top_n" parameter
of
> containstable or freetexttable. For example, say a customer searches our
> catalog for a relatively common word that results in around 72,000 results
> (takes approx 11 sec on subsequent runs...over 1 min on first run, which
is
> the most important statistic). If we were to use (say) n=2000 for the
top_n
> parameter, our best selling products would not be returned from the FTS
> engine. We need to return all the results so we can sort them by sales
rank
> and display them to customers.
> The server is running Windows 2003, with 6GB RAM, 16 x 15,000K RPM SCSI
> drive in a RAID 10, in a dual opteron configuration the with transaction
log
> on a seperate RAID volume. SQL is SQL Server 2000, SP4 (I've included the
> output of @.@.version below). Perfmon shows that the server isn't sweating
at
> all during these queries from a disk, memory, or CPU standpoint, so that
> leaves SQL as the performance bottleneck. Our most recent population was
> around a month ago, so the catalog is relatively up to date.
> We've fooled around with increasing the memory available to FTS, but that
> did not seem to make a difference. Perhaps we did not do it right since
> mssearch.exe is still only showing about 49,000K in memory--but since we
> are using AWE this could be distorted.
> We're going to make one last gasp at improving the performance here before
> dumping SQL Server and moving to Oracle. Help!
> John
> @.@.Version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.2 (Build 3790: )
> Sample query:
> SELECT distinct <field list>
> FROM containstable(<ft-table>, <ft-field>,'"<common term>"') as ct
> JOIN <ft-table> t with (nolock) on t.<PK>=ct.[key] //tables are 100%
> readonly except during monthly updates, hence the nolocks
> join salesRank sr with (nolock) on sr.<PK>=ct.[key]
> order by sr.SalesRank
>
Tuesday, March 20, 2012
Performance problem ?
Hi all,
I have a table "Customer"
And field names call ForeName varchar(30),Surname varchar(30)
When i select
SELECT c.SURNAME + ', ' + c.FORENAME as ID, c.SURNAME + ', ' + c.FORENAME as label
FROM Customer c
where c.SURNAME + ', ' + c.FORENAME='Perera, N'
It will give me the result writhing a second.
Now the problem is in our company we have moved to a different database table structure from last week.
now my "Customer" table has got a same field name but different data type like bellow.
ForeName nvarchar(Max),Surname nvarchar(Max)
So when I give the same select sql statement like previously mentioned, it takes more than 8 seconds time?
How come this happens? Because we have the same amount of data from old database structure and new structure?
Any Idea ?
Regards
sujithf
If you use nvarchar(max) field you couldn't index it. May be in old structure you had indexes.|||
Thaks for your response ,
How do i know wheater my table field indexed or not ,
i have no idea abt indexing ?
can you please explain it bit more ?
i am bit new to sql server 2005
regards
sujithf
|||
You could review indexes using following sp:
sp_helpindex '<your table name>'
But if you already have nvarchar(max) fields, if don't have index, because:
Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.
Also I think, that it's bad idea to use nvarchar(max) as field data type for Surname, Forename. Is it really possible, that surname or forename could be greater than 4000 characters? nvarchar(max) data type designed for very large amount of data and stored outside of table.
|||
Hi.
many thanks for your valuable comments ,
i checked using your command, it says object does not have any indexes,
Yes i am also agree with you,because its wasting space by specifying Nvarchar(max) for surname+forename
but our company hired a third party company to migrate from old database structure to new structure,
i think we have to contact third party company for this matter.
so do you think , this sql query 7 seconds times slow in a new structure because of the datatype nvarchar(max) ?
what is your sugestions ?
regards
sujith
|||I don't have large table, but It make test on table Person.Contact from AdventureWorks sample Database. This table has ~20,000 rows. I ran following query:
select FirstName+', '+MiddleName from Person.Contact where FirstName+', '+MiddleName = 'Frances, B.'
If both fields are nvarchar(50) without indexes, query time ~0,06s
If both feidsl are nvarchar(max), query time ~0,2s
So, i think, we have negative influence of max data type.
Also, I think, that better use following query:
select FirstName+', '+MiddleName from Person.Contact where FirstName='Frances' and MiddleName = 'B.'
and create indexes
|||thank you so much for your effort to sort out my problem,
and finally can you provide me a example, that how to index the table ?
or can you provide me a articles to learn how to index tables and field ?
cos i really like to learn new stuff in sql server 2005
thank you again
sujithf
|||CREATE INDEX article: http://msdn2.microsoft.com/en-us/library/ms188783.aspx. At the end of this article, you could find good examples
I suggest you to download latest version of BOL: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
|||I think the problem is that if you are using max then by default the value is not stored on the data page but rather a 16-byte root pointer instead. To compare to the value it has to follow this pointer and load the text itself (more IO therefore slower).
It is possible to change this by using sp_tableoption to set the "large value types out of row" option to 0 but then all the values can only add up to a size of 8000 bytes (minus I would suspect the other data in the row). Sort of destroys the reason for not just using the smaller 1 to 4000 value (4000 nvarchar is 8000 bytes).
The problem with the max data types is that they are a new feature and everyone wants to use them without understanding the impact. They are really suposed to store large data that you do not generally expect to directly search (documents, free form commentary, images). That is what Full Text Search and the new xml indexes are for (to build search indices on some of the data types stored in this format).
As mentioned earlier there is no reason for expanding the fields in the way they have unless they can argue that people are going to have names of that length. I am particularly intrigued by the fact that you seem to only be storing the first initial in this enourmous first name field.
I would probably also be tempted to compare the fields individually rather than concatonating them if you are sure the break will always be (otherwise you have comma insertions issues anyway).
|||Thank you both of you Dhericean + Konstantin Kosinsky
for your valuable time to consider my problem.
regards
sujithf
Friday, March 9, 2012
performance on the join
What if I cast the fields to integer within the join clause,
ex. JOIN CAST(tableA.field AS int) = CAST(tableB.field AS int)
would this speed up my query?
Thanks,
CulamNot very likely, since -- even if there are indexes on these columns -- an
explicit conversion pretty much guarantees a scan.
Did you try it? Did you compare execution plans (both real and estimated),
cpu/reads/statistics i/o, etc?
If all the data is integer, why is the column still defined as varchar?
"culam" <culam@.discussions.microsoft.com> wrote in message
news:0D47D119-344A-419E-A5EB-EA44B06F1F88@.microsoft.com...
>I know that join on an integer field is faster than join on varchar field.
> What if I cast the fields to integer within the join clause,
> ex. JOIN CAST(tableA.field AS int) = CAST(tableB.field AS int)
> would this speed up my query?
> Thanks,
> Culam
>
performance of select
Table a
sri int, PK, Clustered
num varchar(7) Nonclustered
.
.
.
p uniqueidentifier Nonclustered
Table s
num varchar(7), PK,Clustered
ssi tinyint --can have either 1 or 2
IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1 )
PRINT 'YES'
--uses index s
IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
PRINT 'YES'
--uses index scan on num, takes around 8-25 seconds. Have any thoughts why
it takes so long, how to make this use an index s
GROUP BY ssi
How many rows have an ssi value of 2, compared to an ssi value of 1? If
there are a lot more rows that have ssi = 2 than 1, then the optimizer will
choose an index scan, because it would be the better method to find matching
rows through the select statement.
"S" wrote:
> Here is DDL in my way. Other information is masked.
> Table a
> sri int, PK, Clustered
> num varchar(7) Nonclustered
> .
> .
> .
> p uniqueidentifier Nonclustered
> Table s
> num varchar(7), PK,Clustered
> ssi tinyint --can have either 1 or 2
>
> IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1 )
> PRINT 'YES'
> --uses index s
>
> IF EXISTS ( SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
> PRINT 'YES'
> --uses index scan on num, takes around 8-25 seconds. Have any thoughts why
> it takes so long, how to make this use an index s
2 - few hundreds
1 - millions. Is that what causing the daly to find the records.
"Mark Williams" wrote:
> SELECT ssi, COUNT(*) FROM s
> GROUP BY ssi
> How many rows have an ssi value of 2, compared to an ssi value of 1? If
> there are a lot more rows that have ssi = 2 than 1, then the optimizer wil
l
> choose an index scan, because it would be the better method to find matchi
ng
> rows through the select statement.
>
> --
> "S" wrote:
>|||Could be...
You can update the statistics of table "a", preferably WITH FULL_SCAN,
and see if that makes any difference.
Gert-Jan
S wrote:
> Ratio is few hundreds to millions
> 2 - few hundreds
> 1 - millions. Is that what causing the daly to find the records.
> "Mark Williams" wrote:
>|||Try modifying your queries:
IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1 )
PRINT 'YES'
uses index s
IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
PRINT 'YES'
Since your are using the query in the context of EXISTS, you can use SELECT
1 instead of SELECT sri (credit to Jim Underwood, as seen in a posting
earlier today). Specifying sri in the select list may be affecting which
indexes are chosen.
Other than that, run the statements below and post the text execution plans
here.
SET SHOWNPLAN_TEXT ON
GO
SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 1
SELECT sri FROM a WITH (NOLOCK)
INNER JOIN s WITH (NOLOCK)
ON a.num = s.num
WHERE a.p IS NULL AND s.ssi = 2 )
-
"S" wrote:
> Ratio is few hundreds to millions
> 2 - few hundreds
> 1 - millions. Is that what causing the daly to find the records.
>|||Table s has 8 million records but only a few hundred have ssi=2, remaining
records
have ssi= 1
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Filter(WHERE:([a].[p]=NULL))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([DB_NAME].[dbo].[a] ))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([s].[num]))
|--Clustered Index
Scan(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), WHERE:([s].[ssi]=2))
|--Index
S
ORDERED FORWARD)
for ssi= 2
|--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
|--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE]))
|--Constant Scan
|--Nested Loops(Inner Join, OUTER REFERENCES:([a].[num]) WITH
PREFETCH)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([DB_NAME].[dbo].[a] ))
| |--Index S
SEEK:([a].[p]=NULL) ORDERED FORWARD)
|--Clustered Index
S
WHERE:([s].[ssi]=1) ORDERED FORWARD)
"Mark Williams" wrote:
> Try modifying your queries:
> IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1 )
> PRINT 'YES'
> uses index s
>
> IF EXISTS ( SELECT 1 FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
> PRINT 'YES'
> Since your are using the query in the context of EXISTS, you can use SELEC
T
> 1 instead of SELECT sri (credit to Jim Underwood, as seen in a posting
> earlier today). Specifying sri in the select list may be affecting which
> indexes are chosen.
> Other than that, run the statements below and post the text execution plan
s
> here.
> SET SHOWNPLAN_TEXT ON
> GO
> SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 1
> SELECT sri FROM a WITH (NOLOCK)
> INNER JOIN s WITH (NOLOCK)
> ON a.num = s.num
> WHERE a.p IS NULL AND s.ssi = 2 )
>
> -
>
> "S" wrote:
>
>|||Which order are the plans in? Is the ssi = 1 query first?
"S" wrote:
> Table s has 8 million records but only a few hundred have ssi=2, remaining
> records
> have ssi= 1
> |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE])
)
> |--Constant Scan
> |--Filter(WHERE:([a].[p]=NULL))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([DB_NAME].[dbo].[a] ))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([s].[num]))
> |--Clustered Index
> Scan(OBJECT:([DB_NAME].[dbo].[s].[PK_s]), WHERE:([s].[ssi]=2))
> |--Index
> S
> ORDERED FORWARD)
>
> for ssi= 2
> |--Compute Scalar(DEFINE:([Expr1003]=If [Expr1004] then 1 else 0))
> |--Nested Loops(Left Semi Join, DEFINE:([Expr1004] = [PROBE VALUE])
)
> |--Constant Scan
> |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[num]) WITH
> PREFETCH)
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([DB_NAME].[dbo].[a] ))
> | |--Index S
> SEEK:([a].[p]=NULL) ORDERED FORWARD)
> |--Clustered Index
> S
> WHERE:([s].[ssi]=1) ORDERED FORWARD)
>
> "Mark Williams" wrote:
>|||Yes.
"Mark Williams" wrote:
> Which order are the plans in? Is the ssi = 1 query first?
> --
> "S" wrote:
>|||I am sorry. I think I gave the incorrect order of execution plans.
ssi =1 uses index s
ssi=2 uses index s
"S" wrote:
> Yes.
> "Mark Williams" wrote:
>|||I tried replicating your data set on a much smaller scale. I created and
populated table s with about 100,000 rows with the same distribution of ssi
=
1 and ssi = 2 rows. Created and populated table a with an IDENTITY column fo
r
the PK, foreign key relationship to s on the num column, and nonclustered
indexex on p and num.
Ran both the queries and they had identical execution plans. Don't know why
you are getting different ones.
"S" wrote:
> I am sorry. I think I gave the incorrect order of execution plans.
> ssi =1 uses index s
> ssi=2 uses index s
> "S" wrote:
>