August 3, 2009 at 6:07 am
Hi ALL ,
We have huge table that maintains the products info data like Stock ,price and after each 15 min its data is updated in huge amount by delete and insert operation, this table is used heavily as ins searches operation in our applications , it has no primary key, so that we want to add id column as Identity, to make that column as primary key and later on create index on that column so that we can index view but i am afraid of crashing of table due identity column because of huge delete and insert operation , is that any way so that we can maintain identity column to avoid flush of data or crash of table .
Thanks
August 3, 2009 at 6:27 am
i would guess that you are looking at it wrong; an identity, if you are not searching by that now, would not help performance in the slightest.
I'd like to see the table definition and a couple of rows of sample data, but I'm thinking two things: i would guess that the stock symbol is the main search column; that should have a clustered index on it. that column is proabably whgat you want for the primary key as well, but we'd need more info from you to make better suggestions.
in 2005, I'm pretty sure you can create any index, even the clustered index, to INCLUDE columns that would typically be in your search, that would imporve performance as well.
second, since you insert and delete a lot, you'd want to use a fillfactor for the clustered index that leaves room for inserts, say fill factor 70 or so.
Lowell
August 3, 2009 at 6:36 am
While I'm thinking about it, can you give us some metrics as well? how many total rows in the table? how many rows are affected in that 15 minute update?
how is the update performed? if it is a cursor, or programatic update of data on a row-by-row basis, we could offer better update solutions as well.
15 minutes is a long time; i've bulk inserted billions of rows in that amount of time before, so there might be alternatives.
Lowell
August 3, 2009 at 6:48 am
Thanks for ur reply,
My Situation is that data in table is reduntant or duplicate . So We Need to maintain the Identity Column in Table to maintain uniqunes and Already explain we are searching data through view not table , bcsaz addirtional funcationality is added in view , so we need to index view and view need the unique index first then go for other, i .e situation
--Structure of table
CREATE TABLE [dbo].[Products] (
[ProductCode] [varchar](255) NULL,
[description] [varchar](255) NULL,
[supplier] [varchar](255) NULL,
[cost price] [money] NULL,
[retail price] [money] NULL,
[qty last checked] [varchar](255) NULL,
[Rtnstock] [int] NOT NULL,
[UpdatedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
Insert into Products
SELECt 'A','ABC',TEST',300.00,500.00,25,0, getdate()
UNION
SELECT 'A','ABC','TEST1',300.00,500.00, 25,0,getdate()
UNION
SELECT 'A', 'ABC','TEST2',300.00,500.00,25,0, getdate()
Thanks
August 3, 2009 at 7:00 am
naginderp (8/3/2009)
Thanks for ur reply,My Situation is that data in table is reduntant or duplicate . So We Need to maintain the Identity Column in Table to maintain uniqunes and Already explain we are searching data through view not table , bcsaz addirtional funcationality is added in view , so we need to index view and view need the unique index first then go for other, i .e situation
--Structure of table
CREATE TABLE [dbo].[Products] (
[ProductCode] [varchar](255) NULL,
[description] [varchar](255) NULL,
[supplier] [varchar](255) NULL,
[cost price] [money] NULL,
[retail price] [money] NULL,
[qty last checked] [varchar](255) NULL,
[Rtnstock] [int] NOT NULL,
[UpdatedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
Insert into Products
SELECt 'A','ABC',TEST',300.00,500.00,25,0, getdate()
UNION
SELECT 'A','ABC','TEST1',300.00,500.00, 25,0,getdate()
UNION
SELECT 'A', 'ABC','TEST2',300.00,500.00,25,0, getdate()
Thanks
When you say duplicate data, not sure what you are looking at precisely. Based just on your three sample records it appears that you have a unique key on ProductCode and Supplier. I may be wrong, but with only three records to look at that is what I see.
August 3, 2009 at 7:36 am
Thanks for ur reply again
There is dupliecate data like same supplier and productcode have been repeated , there are another coplumns that are hidden , i acn not give to you. Ther are 8 million rows in table and half miliion are updated after 15 min with delete and insert scenarios.
thnaks
August 3, 2009 at 8:05 am
Hi Naginder,
to increase preformance of your query ,First you need atleast one primary Key and also you are mention that handing more than 1 million records have insert , update and delete operation. so create one virtual Primary key column in that table and make in Identity column . create index that field and fill factor make in 60% or 70%.
Note : take backup master table atleast once a day. and truncate master table and import into master table because to reset identity column. and rebuild the index again.
Please comment on this reply.
August 3, 2009 at 8:35 am
naginderp (8/3/2009)
Thanks for ur reply againThere is dupliecate data like same supplier and productcode have been repeated , there are another coplumns that are hidden , i acn not give to you. Ther are 8 million rows in table and half miliion are updated after 15 min with delete and insert scenarios.
thnaks
You should always use a natural primary key wherever possible Vs. and "ID". In your case I see a natural primary key on supplier:productcode.
If your table was properly normalized, you would have a suppliers table, each with a supplier ID which would be a nice compact, FAST integer (and would make your products table smaller as well). Then the primary key on your table would be the supplierID:productcode.
The probability of survival is inversely proportional to the angle of arrival.
August 3, 2009 at 8:42 am
in my Case , ProductCode and supplier are not unique , As per sarvanane views it could be fine to add ID idenity column in table and then maintain it taking backup of table then truncating and rebuilding indexes , or their is another alternative way to achive the goal to same way ,. so that i could maintain primary key.
Thanks
August 3, 2009 at 8:51 am
I'm sorry to hear that your data is screwed up like that, probably because you have different prices for different quantities of a product from the same supplier. This is just poor database design.
I would try to fix that situation if at all possible (remove/cleanup/fix duplicates and normalize the database) before just trying to kludge it up to make it work. The introduction of an ID column will serve as an artificial primary key, but you will need a set of linking tables to find rows for the different criteris.
It also negates your ability to cluster the data itself on supplier or maybe part type. Even crappy designs will work in small database environments. When you start getting into millions of rows and/or hundreds of transactions fer second it will not work so well.
The probability of survival is inversely proportional to the angle of arrival.
August 4, 2009 at 1:55 am
Once Again Thanks for Ur Reply
In our Application, this particular table is used for searches and get list data and data in tables is insert in bulk and delete , we are not worry about design of DB , bcsz we need denormalized table for enough select operations. as we know normalized table , it agian we get enough joins reterive data , this is also a prerformance degredations. That's why we are not worried about design . I am worried to about , if i add one Virtual key ID As Idenitiy column to achieve goal to do proper indexing in View that is particular depend on this table . I am afraid of crash of data due bulk operation to handle Virtual key .
My Question is simple now , is it good practice to Add Virtual key in this table and rebuilding indexes on a tbale once in day , so that we can work with updated stats and fragmentation.
OR I Have another idea , if afterevry delete , i couls reseed value with highest index order of Virtual key aND then insert data into that table.
I Want to know which one is best solutuion, that is suggested by one of poster sarvanne and mine below, to ressed identity column after each delete and then insert data .
Thanks
August 4, 2009 at 7:37 am
In the vast majority of cases properly normalized database will always perform better than un-normalized database. Yes, it is true that in special circumstances a certain amount of de-normalization *can* help performance, but in your case you have nothing to de-normalize because your table is not even at the first normal level.
Good luck.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply