We have some fast growing tables in our SQL Server 2000 db
which content 13 million records already. So, we going to
periodically archive them base on datetime field and allow
user to retrieve archive data via our application by
checking corresponding checkbox. The problem is that, we
do not know what to do with FK's. How to re-create FK's
after archiving to restore referal integrity.
Is there some standard solution?
Any comments will be appreciated.
Merry chistmas and happy new year to everybody.Vitalik
http://vyaskn.tripod.com/sql_archive_data.htm
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:08ee01c3c964$5faa1340$a401280a@.phx.gbl...
> We have some fast growing tables in our SQL Server 2000 db
> which content 13 million records already. So, we going to
> periodically archive them base on datetime field and allow
> user to retrieve archive data via our application by
> checking corresponding checkbox. The problem is that, we
> do not know what to do with FK's. How to re-create FK's
> after archiving to restore referal integrity.
> Is there some standard solution?
> Any comments will be appreciated.
> Merry chistmas and happy new year to everybody.
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Tuesday, March 27, 2012
Archiving Large table
Hi,
I am having problems with archiving. Any help will be greatly appreciated.
I have a table with 12 million records, and it needs to be archived to another table.
Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards.
Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!!
Does anyone have a better idea of doing this? I am really stuck...RE: I am having problems with archiving. Any help will be greatly appreciated. I have a table with 12 million records, and it needs to be archived to another table. Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards. Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!! Does anyone have a better idea of doing this? I am really stuck...
Q1 [It used to take 1 second to process 1000 records. Now, it takes 2 minutes to 14 minutes. Why?]
A1 There may be many different issues, (insufficient information to suggest a reasonably good guess and / or answer).
Q2 [Does anyone have a better, i.e.(FASTER?) idea of doing this?]
A2 Archiving may be accomplished efficiently. What is "Better" really depends on existing overall constraints and designs, available resources, and the details of the circumstances. An example that should be fairly quick (but not overly 'user friendly') would be Archiving a Test table in a Demo DB to an ArchiveDB database table named ArchiveTest.
Demo..Test To ArchiveDB..ArchiveTest
Use Demo
Go
INSERT INTO
[ArchiveDB].[dbo].[ArchiveTest]
([Parent], [Child])
SELECT
[Parent], [Child]
FROM
[Demo].[dbo].[Test]
GO
Alter Database Demo
Set Restricted_User
With
RollBack Immediate
Go
Alter Database Demo
Set Single_User
With
RollBack Immediate
Go
Alter Database Demo
Set Recovery Simple
With
RollBack Immediate
Go
-- drop and recreate, or truncate, delete, etc.
Drop TABLE [Test]
Go
CREATE TABLE [Test] (
[Parent] [varchar] (50) NOT NULL ,
[Child] [varchar] (50) NOT NULL)
Alter Database Demo
Set Recovery Full
With
RollBack Immediate
Go
Alter Database Demo
Set Multi_User
With
RollBack Immediate
Go|||how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.|||Originally posted by Paul Young
how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.
Thank you for your replies,
Actually, there is only primary index with identity on. That's it.
The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table.
Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table.
Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.|||RE: Thank you for your replies, Actually, there is only primary index with identity on. That's it. The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table. Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table. Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.
Q1 [I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse.]
A1 You are probably not updating your indexes at a suitable interval (to ensure optimal performance).
Q2 What can I do to keep the old record + new records sorted at all times?
A2 Cluster both TABLES on the desired column.|||my first inclination is that you have a corrupted index. the overall sorting should not change (aside from changes in data) due to inserting, updateing or deleting data.
During a one week prieod I rebuilt all my index once if not twice on very dynamic tables. Are you doing this?
If your primary index is clustered, you are reordering some part of your data everytime you insert, update or delete. This can lead to slow performance at times. If you must have this index then you just live with it, if you don't need it then change to non-clustered.|||Thank you, Paul Young.
Actually, I never rebuilt indexes on any of the tables. My bad...
What do I have to do to rebuild indexes? I tried DBCC DBREINDEX, and it didn't improve the performance of the archiving.
Can you guide me step-by-step what has to be done?
Thank you again.|||Generally I use maintinance plans to rebuild indexes and statistics along with other things however to answer your question DBCC DBREINDEX will do the trick.
Even if you haven't EVER rebuilt your index(s) they still should produce a result set correctly sorted. Again My hunch is that you have a corrupt index. To fix this you will need to drop the index an re-create it. You can do this while other are using the system but I would NOT advise it.|||One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.|||Thank you, Paul.
I tried to rebuild the index using 'DROP EXISTING'. It took about 5 minutes, and I opened the table, and it still looks messy.
But now, the index seems to be functioning faster. The problem is that, I don't use the primary key as query condition. Usually, my query condition is the 'CreationTime' which gets filled with default values getdate().
Basically, I query all the data created during a time period.
Should I create another index on CreationTime?
Thank you,|||If this table is used in an OLTP environment you want to keep the number of indexes to a minimum because evryting you inset/update/delete a row you also have to update ALL indexes. In your case you only have one index so adding one more shouldn't cause you a noticable slowdown and will GREATLY improve the prformance of your select.
before adding any indexes drop your select statement into Query Analyzer, turn on Show Execution Plan, Show Server Trace and Show Client Statistics and execute your select. Look at the "Execution Plan" tab and you will get a diagram representing what your select is doing.
Next click on Index Tunning Wizard and let SQL server suggest indexes to be built. Concider the suggestions and implament whatever you tinks looks good. Now rerun your select and look at the differences on the "Execution Plan" tab.
All of this is covered in Books Online, an excelent source of info once you know what to look up.|||Q1 Usually, my query condition is the 'CreationTime' Should I create another index on CreationTime?
A1 If you are looking for good performance, Yes. Generally, one wants a (well maintained) index available for the query parser to take advantage of for any column that is frequently queried.
I am having problems with archiving. Any help will be greatly appreciated.
I have a table with 12 million records, and it needs to be archived to another table.
Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards.
Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!!
Does anyone have a better idea of doing this? I am really stuck...RE: I am having problems with archiving. Any help will be greatly appreciated. I have a table with 12 million records, and it needs to be archived to another table. Since there is a primary key(let's say, SessionID), so I tried to copy 1000 records at a time, and delete those records afterwards. Problem is, it used to take 1 second to process 1000 records. Now, it takes anywhere from 2 minutes to 14 minutes!!! Does anyone have a better idea of doing this? I am really stuck...
Q1 [It used to take 1 second to process 1000 records. Now, it takes 2 minutes to 14 minutes. Why?]
A1 There may be many different issues, (insufficient information to suggest a reasonably good guess and / or answer).
Q2 [Does anyone have a better, i.e.(FASTER?) idea of doing this?]
A2 Archiving may be accomplished efficiently. What is "Better" really depends on existing overall constraints and designs, available resources, and the details of the circumstances. An example that should be fairly quick (but not overly 'user friendly') would be Archiving a Test table in a Demo DB to an ArchiveDB database table named ArchiveTest.
Demo..Test To ArchiveDB..ArchiveTest
Use Demo
Go
INSERT INTO
[ArchiveDB].[dbo].[ArchiveTest]
([Parent], [Child])
SELECT
[Parent], [Child]
FROM
[Demo].[dbo].[Test]
GO
Alter Database Demo
Set Restricted_User
With
RollBack Immediate
Go
Alter Database Demo
Set Single_User
With
RollBack Immediate
Go
Alter Database Demo
Set Recovery Simple
With
RollBack Immediate
Go
-- drop and recreate, or truncate, delete, etc.
Drop TABLE [Test]
Go
CREATE TABLE [Test] (
[Parent] [varchar] (50) NOT NULL ,
[Child] [varchar] (50) NOT NULL)
Alter Database Demo
Set Recovery Full
With
RollBack Immediate
Go
Alter Database Demo
Set Multi_User
With
RollBack Immediate
Go|||how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.|||Originally posted by Paul Young
how many indexs do you have on this table? Are any of them clustered?
I would suggest
1. copying all data to your archive table
2. script out all indexes and then drop them
3. build one non clustered index that would allow you to join to the archive table.
4. begin a transaction, delete a few thousand records, commit the transaction.
5. adjust the number of deleted records for best performance
6. restore indexes from step 2.
Thank you for your replies,
Actually, there is only primary index with identity on. That's it.
The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table.
Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table.
Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.|||RE: Thank you for your replies, Actually, there is only primary index with identity on. That's it. The only problem is that, this table should be on-line all the time. i cannot restrict the access to this table. Somehow, the records don't seem to be sorted at all when I open the table. I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse. If I open the table again, it is again a mess. I don't see sorted order in this table. Once it is properly sorted, the performance is great. What can I do to keep the old record + new records sorted at all times? I cannot manually sort the table, and this process hurts the server badly.
Q1 [I tried to add sort(desc) option on the table, and it seemed to be working. However, after a couple of archiving procedure run, the performance gets worse.]
A1 You are probably not updating your indexes at a suitable interval (to ensure optimal performance).
Q2 What can I do to keep the old record + new records sorted at all times?
A2 Cluster both TABLES on the desired column.|||my first inclination is that you have a corrupted index. the overall sorting should not change (aside from changes in data) due to inserting, updateing or deleting data.
During a one week prieod I rebuilt all my index once if not twice on very dynamic tables. Are you doing this?
If your primary index is clustered, you are reordering some part of your data everytime you insert, update or delete. This can lead to slow performance at times. If you must have this index then you just live with it, if you don't need it then change to non-clustered.|||Thank you, Paul Young.
Actually, I never rebuilt indexes on any of the tables. My bad...
What do I have to do to rebuild indexes? I tried DBCC DBREINDEX, and it didn't improve the performance of the archiving.
Can you guide me step-by-step what has to be done?
Thank you again.|||Generally I use maintinance plans to rebuild indexes and statistics along with other things however to answer your question DBCC DBREINDEX will do the trick.
Even if you haven't EVER rebuilt your index(s) they still should produce a result set correctly sorted. Again My hunch is that you have a corrupt index. To fix this you will need to drop the index an re-create it. You can do this while other are using the system but I would NOT advise it.|||One more thing, once you rebuild your index you probably want to update statistics so the correct optimization plans will be used.|||Thank you, Paul.
I tried to rebuild the index using 'DROP EXISTING'. It took about 5 minutes, and I opened the table, and it still looks messy.
But now, the index seems to be functioning faster. The problem is that, I don't use the primary key as query condition. Usually, my query condition is the 'CreationTime' which gets filled with default values getdate().
Basically, I query all the data created during a time period.
Should I create another index on CreationTime?
Thank you,|||If this table is used in an OLTP environment you want to keep the number of indexes to a minimum because evryting you inset/update/delete a row you also have to update ALL indexes. In your case you only have one index so adding one more shouldn't cause you a noticable slowdown and will GREATLY improve the prformance of your select.
before adding any indexes drop your select statement into Query Analyzer, turn on Show Execution Plan, Show Server Trace and Show Client Statistics and execute your select. Look at the "Execution Plan" tab and you will get a diagram representing what your select is doing.
Next click on Index Tunning Wizard and let SQL server suggest indexes to be built. Concider the suggestions and implament whatever you tinks looks good. Now rerun your select and look at the differences on the "Execution Plan" tab.
All of this is covered in Books Online, an excelent source of info once you know what to look up.|||Q1 Usually, my query condition is the 'CreationTime' Should I create another index on CreationTime?
A1 If you are looking for good performance, Yes. Generally, one wants a (well maintained) index available for the query parser to take advantage of for any column that is frequently queried.
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon
"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>
|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be remove
d
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> Yeah, deletions can take time.
>
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the da
ta
> in a format you can use.
> Then delete it.
> That should solve your problems.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
archiving data
Hi all hope this is in the right forum.
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql
Ive got a SQL database which accumulates about 4 million records each year.
This amount causes my PC to slow down when searching, and my hard drive is
getting fuller.
To overcome this problem I have to delete half the records each year (2
million) which takes quite some time.
My question is - is it possible to archive some of the SQL database each
year onto another hard drive or DVD. The archived data should them be removed
from the original SQL data base.
I Would also need to be able to view, sort and search this archived data on
another machine in excell or something.
Hope someone can help.
Thanks
Jon"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> Hi all hope this is in the right forum.
> Ive got a SQL database which accumulates about 4 million records each
> year.
> This amount causes my PC to slow down when searching, and my hard drive is
> getting fuller.
>
4 million a YEAR slows you down? Do you have indexes?
Anyway...
> To overcome this problem I have to delete half the records each year (2
> million) which takes quite some time.
>
Yeah, deletions can take time.
> My question is - is it possible to archive some of the SQL database each
> year onto another hard drive or DVD. The archived data should them be
> removed
> from the original SQL data base.
I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
in a format you can use.
Then delete it.
That should solve your problems.
> I Would also need to be able to view, sort and search this archived data
> on
> another machine in excell or something.
> Hope someone can help.
> Thanks
> Jon
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Thanks for the reply.
Yes we have clustered indexes on the primary keys.
Sorry but what is DTS.
Can the process be automated on a particular date?
Thanks
"Greg D. Moore (Strider)" wrote:
> "jsw" <jsw@.discussions.microsoft.com> wrote in message
> news:E55D02B1-FD8D-468A-A716-9CF9A1717363@.microsoft.com...
> >
> > Hi all hope this is in the right forum.
> >
> > Ive got a SQL database which accumulates about 4 million records each
> > year.
> > This amount causes my PC to slow down when searching, and my hard drive is
> > getting fuller.
> >
> 4 million a YEAR slows you down? Do you have indexes?
> Anyway...
>
> > To overcome this problem I have to delete half the records each year (2
> > million) which takes quite some time.
> >
> Yeah, deletions can take time.
>
> > My question is - is it possible to archive some of the SQL database each
> > year onto another hard drive or DVD. The archived data should them be
> > removed
> > from the original SQL data base.
> I'd probably use DTS (assuming SQL 2000, SSIS for 2005) to copy out the data
> in a format you can use.
> Then delete it.
> That should solve your problems.
> >
> > I Would also need to be able to view, sort and search this archived data
> > on
> > another machine in excell or something.
> >
> > Hope someone can help.
> >
> > Thanks
> >
> > Jon
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
>|||"jsw" <jsw@.discussions.microsoft.com> wrote in message
news:AF4D2F53-9995-4745-973D-5E3DC04B1EC3@.microsoft.com...
> Hi,
> Thanks for the reply.
> Yes we have clustered indexes on the primary keys.
> Sorry but what is DTS.
Data Transformation Services.
Look for it under Enterprise Manager.
> Can the process be automated on a particular date?
Yes.
Create a DTS package, and then you can schedule it.
> Thanks
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.comsql
Sunday, February 19, 2012
Appending data to a table but not duplicates
Hiya everyone,
I have two tables in SQL 2000. I would like to append the contents of
TableA to TableB.
Table A has around 1.1 Million Records.
Table B has around 1 Million Reocords.
Basically TableA has all of the data held in TableB plus 100,000
additional records. I would only like to import or append these new
additional records. I have a unique index already setup on Table B.
Any ideas pretty pretty please?
Paul.
Ps. (Have been messing around with DTS but get a unique violation error
- Which is kinda what I want I guess, but would like SQL to ignore the
error and only copy the new data - if only)<paul@.domainscanners.com> wrote in message
news:1119371481.949023.149970@.g43g2000cwa.googlegr oups.com...
> Hiya everyone,
> I have two tables in SQL 2000. I would like to append the contents of
> TableA to TableB.
> Table A has around 1.1 Million Records.
> Table B has around 1 Million Reocords.
> Basically TableA has all of the data held in TableB plus 100,000
> additional records. I would only like to import or append these new
> additional records. I have a unique index already setup on Table B.
> Any ideas pretty pretty please?
> Paul.
> Ps. (Have been messing around with DTS but get a unique violation error
> - Which is kinda what I want I guess, but would like SQL to ignore the
> error and only copy the new data - if only)
insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol = b.keycol)
Simon|||Simon,
how is your sql different from
insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where keycol in (select keycol from dbo.tableb)
??
TIA
Rob|||rcamarda (rcamarda@.cablespeed.com) writes:
> Simon,
> how is your sql different from
> insert into dbo.TableB
> (col1, col2, col3,...)
> select col1, col2, col3...
> from dbo.TableA a
> where keycol in (select keycol from dbo.tableb)
That's one hell of a difference - you are inserting the duplicates only. :-)
But, OK, put in the NOT, and your query is the same as Simon's. In SQL 6.5
there was a difference in performance, NOT IN usually executed slower. I
think that in SQL 2000, the optimizer rewrites the query internally.
Anyway, there is still an advantage with the style that Simon used.
Consider this query:
insert into dbo.TableB
(col1, col2, col3,...)
select col1, col2, col3...
from dbo.TableA a
where not exists (
select *
from dbo.TableB b
where a.keycol1 = b.keycol1
and a.keycol2 = B.keycol2)
That is not easily recast to NOT IN.
So NOT EXISTS is simply an operation you need to master.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||er. sorry. missed the not. I'm interesting in "exists" vs. in (select
... )
so. are you saying that the advantage is when you are looking for
something that does not exist? Otherwise "..exists (select .." is the
same as "in (select ..." ?|||rcamarda (rcamarda@.cablespeed.com) writes:
> er. sorry. missed the not. I'm interesting in "exists" vs. in (select
> ... )
> so. are you saying that the advantage is when you are looking for
> something that does not exist? Otherwise "..exists (select .." is the
> same as "in (select ..." ?
Same thing there, EXISTS is the only that works when your condition
is more than a single column. There is also a gotcha there are NULL
values involved.
It's partly a matter of style, but I use (NOT) EXISTS far more often
then (NOT) IN. (With subqueries, that is. (NOT) IN a list of values
is another matter.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes - for a NOT IN condition, it's almost always better to use NOT
EXISTS. The main issue, as Erland pointed out, is the possibility of a
NULL in the subquery. Consider this simplified example:
IF 1 IN (1, 2, 3, NULL) PRINT 'True'
Obviously the condition is TRUE, but now consider this:
IF 1 NOT IN (2, 3, NULL) PRINT 'True'
Now we don't know if the condition is TRUE or not - the NULL has an
unknown value, so in principle it could be a 1, and therefore the whole
condition evaluates to UNKNOWN. So in the case of a correlated
subquery, any NULLs in the subquery mean that the whole query returns
no rows. Using NOT EXISTS avoids this trap.
Admittedly, you often use primary key columns in the correlation, so
there could never be a NULL in the subquery, but I think it's better to
have a 'safer' habit of using NOT EXISTS. And as Erland also mentioned,
there is some personal taste involved - I find that EXISTS/NOT EXISTS
expresses the intention of the query more clearly, especially when
someone is quickly looking through the code.
Simon|||Thanks Guys!|||Hi Guys,
Thanks very much for your answers to my questions. I ran the query you
supplied and it worked fine although I now have another problem and was
wondering if you would be able to helpo me out again?
Basically I now have a table containing all the data I need but the new
data has left a gap in the Identity column that im using.
Basically the original data's identity column went up to 1,000,000. I
was hoping that the new data that was appended would be inserted as
1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
However the new appended data went in as 1,254,324 then 1,254,325 etc.
Is there a command I can run to resnycronise my identity column? so
that the ID's run smoothly from 0 through to 1,1000,000?
Hope you can help me out again,
Paul.|||(paul@.domainscanners.com) writes:
> Basically the original data's identity column went up to 1,000,000. I
> was hoping that the new data that was appended would be inserted as
> 1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
> However the new appended data went in as 1,254,324 then 1,254,325 etc.
> Is there a command I can run to resnycronise my identity column? so
> that the ID's run smoothly from 0 through to 1,1000,000?
If you want contiguous ids, or at least control over them, don't
use the IDENTITY property. When you attempt to insert a row into
a table with the IDENTITY property, you consume one number, even if
the INSERT fails. This may seem stupid, but it is actually a feature,
because it speeds up concurrency. If the number would be reused in
case of failure, SQL Server would need to lock the number, and no
other process had been able to insert until the INSERT have completed.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, February 12, 2012
Anyway to quick ntext Search
Helloo..
I there anyway to make ntext search faster'
I have a table of legal documents with 2 million text record, While no full
text index & search on text fields searching with "Like is very very slow".
I need anyway to make it faster using any techniques..
Do i need to use cursor or how can i solve this issue'
I can't use nvarchar coz of size limititionIslamegy wrote on Wed, 23 Nov 2005 14:18:28 +0200:
> Helloo..
> I there anyway to make ntext search faster'
> I have a table of legal documents with 2 million text record, While no
> full text index & search on text fields searching with "Like is very very
> slow". I need anyway to make it faster using any techniques..
> Do i need to use cursor or how can i solve this issue'
> I can't use nvarchar coz of size limitition
Any particular reason why you can't use Full Text Search?
Dan|||Because Full Text index dosn't apply of Text Fields of type "text",
"ntext"!!!
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ujyy8%23C8FHA.956@.TK2MSFTNGP10.phx.gbl...
> Islamegy wrote on Wed, 23 Nov 2005 14:18:28 +0200:
>
> Any particular reason why you can't use Full Text Search?
> Dan
>|||Islamegy wrote on Wed, 23 Nov 2005 15:27:02 +0200:
> Because Full Text index dosn't apply of Text Fields of type "text",
> "ntext"!!!
Yes it does. I'm using it on a text column in one of my databases!
Dan|||Yes, performing a non-indexed search on the text of 2 million documents is
going to be slow. I'm surprised that it's just optimization you are
concerned with and the actual feasability.
Of course, consider using the Full-Text feature.
If you must perform LIKE comparisons on the rows, perhaps it would be
possible to run a process that parses the text of each document and then
stores key words in something like a 120 length varchar column.
For example:
Jacobs;California;McNeal;disability;insu
rance;claim
Once done, this column could be used for LIKE searches.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OoYHFgC8FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Helloo..
> I there anyway to make ntext search faster'
> I have a table of legal documents with 2 million text record, While no
> full text index & search on text fields searching with "Like is very very
> slow".
> I need anyway to make it faster using any techniques..
> Do i need to use cursor or how can i solve this issue'
> I can't use nvarchar coz of size limitition
>|||Why would you think that? It does in fact work on text and ntext columns.
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:evIRZGD8FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Because Full Text index dosn't apply of Text Fields of type "text",
> "ntext"!!!
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ujyy8%23C8FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||How do u do it' When i try to use "Contain(Text column,"some words").. it
don't accept my query!!
As i know from my research it dosn't work on text..
Anyway my database is Arabic, so i thought ntext is ok but when i converted
it to text it became 20% faster.
Also finally i discovered that collation is work as a magic. When i switched
it from Arabic_CI_AI to Arabic_BIN this cut the search time to the half. for
now this is really so great success, if you have anyone have ideas for more
improvment plz let me now..
thanx
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23wC2$MD8FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Islamegy wrote on Wed, 23 Nov 2005 15:27:02 +0200:
>
> Yes it does. I'm using it on a text column in one of my databases!
> Dan
>|||Islamegy wrote on Wed, 23 Nov 2005 15:48:54 +0200:
> How do u do it' When i try to use "Contain(Text column,"some words").. it
> don't accept my query!!
> As i know from my research it dosn't work on text..
I'm guessing you haven't created the FTS index yet. Read BOL on how to do
this.
> Anyway my database is Arabic, so i thought ntext is ok but when i
> converted it to text it became 20% faster.
> Also finally i discovered that collation is work as a magic. When i
> switched it from Arabic_CI_AI to Arabic_BIN this cut the search time to
> the half. for now this is really so great success, if you have anyone have
> ideas for more improvment plz let me now..
Using FTS you might see a significant improvement. You'd do well to post
questions about it microsoft.public.sqlserver.fulltext
Dan|||Keep in mind that text stores text in ANSI (1 byte per character) and ntext
stores the text in Unicode (2 bytes per character) format. This is why ntext
will require more disk storage and operations on the data will be somewhat
slower. If the character set is Arabic, then you have no other choice but
the use Unicode.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OretoSD8FHA.1248@.TK2MSFTNGP14.phx.gbl...
> How do u do it' When i try to use "Contain(Text column,"some words").. it
> don't accept my query!!
> As i know from my research it dosn't work on text..
> Anyway my database is Arabic, so i thought ntext is ok but when i
> converted it to text it became 20% faster.
> Also finally i discovered that collation is work as a magic. When i
> switched it from Arabic_CI_AI to Arabic_BIN this cut the search time to
> the half. for now this is really so great success, if you have anyone have
> ideas for more improvment plz let me now..
> thanx
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23wC2$MD8FHA.2676@.TK2MSFTNGP15.phx.gbl...
>
I there anyway to make ntext search faster'
I have a table of legal documents with 2 million text record, While no full
text index & search on text fields searching with "Like is very very slow".
I need anyway to make it faster using any techniques..
Do i need to use cursor or how can i solve this issue'
I can't use nvarchar coz of size limititionIslamegy wrote on Wed, 23 Nov 2005 14:18:28 +0200:
> Helloo..
> I there anyway to make ntext search faster'
> I have a table of legal documents with 2 million text record, While no
> full text index & search on text fields searching with "Like is very very
> slow". I need anyway to make it faster using any techniques..
> Do i need to use cursor or how can i solve this issue'
> I can't use nvarchar coz of size limitition
Any particular reason why you can't use Full Text Search?
Dan|||Because Full Text index dosn't apply of Text Fields of type "text",
"ntext"!!!
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ujyy8%23C8FHA.956@.TK2MSFTNGP10.phx.gbl...
> Islamegy wrote on Wed, 23 Nov 2005 14:18:28 +0200:
>
> Any particular reason why you can't use Full Text Search?
> Dan
>|||Islamegy wrote on Wed, 23 Nov 2005 15:27:02 +0200:
> Because Full Text index dosn't apply of Text Fields of type "text",
> "ntext"!!!
Yes it does. I'm using it on a text column in one of my databases!
Dan|||Yes, performing a non-indexed search on the text of 2 million documents is
going to be slow. I'm surprised that it's just optimization you are
concerned with and the actual feasability.
Of course, consider using the Full-Text feature.
If you must perform LIKE comparisons on the rows, perhaps it would be
possible to run a process that parses the text of each document and then
stores key words in something like a 120 length varchar column.
For example:
Jacobs;California;McNeal;disability;insu
rance;claim
Once done, this column could be used for LIKE searches.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OoYHFgC8FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Helloo..
> I there anyway to make ntext search faster'
> I have a table of legal documents with 2 million text record, While no
> full text index & search on text fields searching with "Like is very very
> slow".
> I need anyway to make it faster using any techniques..
> Do i need to use cursor or how can i solve this issue'
> I can't use nvarchar coz of size limitition
>|||Why would you think that? It does in fact work on text and ntext columns.
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.masterado.net
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:evIRZGD8FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Because Full Text index dosn't apply of Text Fields of type "text",
> "ntext"!!!
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ujyy8%23C8FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||How do u do it' When i try to use "Contain(Text column,"some words").. it
don't accept my query!!
As i know from my research it dosn't work on text..
Anyway my database is Arabic, so i thought ntext is ok but when i converted
it to text it became 20% faster.
Also finally i discovered that collation is work as a magic. When i switched
it from Arabic_CI_AI to Arabic_BIN this cut the search time to the half. for
now this is really so great success, if you have anyone have ideas for more
improvment plz let me now..
thanx
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23wC2$MD8FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Islamegy wrote on Wed, 23 Nov 2005 15:27:02 +0200:
>
> Yes it does. I'm using it on a text column in one of my databases!
> Dan
>|||Islamegy wrote on Wed, 23 Nov 2005 15:48:54 +0200:
> How do u do it' When i try to use "Contain(Text column,"some words").. it
> don't accept my query!!
> As i know from my research it dosn't work on text..
I'm guessing you haven't created the FTS index yet. Read BOL on how to do
this.
> Anyway my database is Arabic, so i thought ntext is ok but when i
> converted it to text it became 20% faster.
> Also finally i discovered that collation is work as a magic. When i
> switched it from Arabic_CI_AI to Arabic_BIN this cut the search time to
> the half. for now this is really so great success, if you have anyone have
> ideas for more improvment plz let me now..
Using FTS you might see a significant improvement. You'd do well to post
questions about it microsoft.public.sqlserver.fulltext
Dan|||Keep in mind that text stores text in ANSI (1 byte per character) and ntext
stores the text in Unicode (2 bytes per character) format. This is why ntext
will require more disk storage and operations on the data will be somewhat
slower. If the character set is Arabic, then you have no other choice but
the use Unicode.
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:OretoSD8FHA.1248@.TK2MSFTNGP14.phx.gbl...
> How do u do it' When i try to use "Contain(Text column,"some words").. it
> don't accept my query!!
> As i know from my research it dosn't work on text..
> Anyway my database is Arabic, so i thought ntext is ok but when i
> converted it to text it became 20% faster.
> Also finally i discovered that collation is work as a magic. When i
> switched it from Arabic_CI_AI to Arabic_BIN this cut the search time to
> the half. for now this is really so great success, if you have anyone have
> ideas for more improvment plz let me now..
> thanx
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:%23wC2$MD8FHA.2676@.TK2MSFTNGP15.phx.gbl...
>
Subscribe to:
Posts (Atom)