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
