March 14, 2011 at 3:08 am
Consider a table cleardaytrans having following columns
prim_id int identity(1,1) primary key
temp_id int ,
session bigint,
unique_id bigint,
col1,
col2,
.
.
.
col25
data in this table used as a temporary storage. so i insert and delete on frequent basis.
this table plays a major role to keep live data temporarily. once i finished transaction i move the data from this table to some other table and delete the records from this table.
i have following doubts
1. Should i create clustered index on this column? or need not?
2. while fetching records , in where condition i am giving prim_id,temp_id ,
session,unique_id. will it slow down the performance?.
any help/suggestion to make it better query performance?.
Thanks in Advance....
March 14, 2011 at 3:30 am
BeginnerBug (3/14/2011)
1. Should i create clustered index on this column? or need not?
A clustered index on the primary key is usually a good idea. An identity column is usually a good clustered key (small, unique, ever increasing).
BeginnerBug (3/14/2011)
2. while fetching records , in where condition i am giving prim_id,temp_id ,session,unique_id. will it slow down the performance?.
You could create a nonclustered index on all the search fields, but, depending on the columns you retrieve, could be used or not. If you are retrieving a lot of columns, key lookups could get expensive and you would end up scanning the clustered index anyway.
BeginnerBug (3/14/2011)
any help/suggestion to make it better query performance?.
It really depends on the queries you issue. Do you have any examples?
You could find useful reading this article on how to post performance problems[/url].
Hope this helps
Gianluca
-- Gianluca Sartori
March 14, 2011 at 6:31 am
If the most frequently used columns to retrieve the data is all three that you listed, that might make a better choice for the clustered index. Testing is the key here.
Is the identity column needed? Do the other columns uniquely identify the records? For this type of temporary storage, you might be better off not using the identity column. Any reduction in processing overhead is a win.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 14, 2011 at 10:37 am
INSERT INTO SA_HR_LINES( PUB_ID,PD_NAME,
FROM_DATE,TO_DATE,
QT_NUM,STRD_YEAR,
ADVANCE_PID,STATUS,
TRN,SESSION_ID,
CREATED_BY, CREATED_DATE,
UPDATED_BY, UPDATED_DATE)
SELECT @PVC_PUB_ID,
ATTRIBUTE2,
CONVERT(VARCHAR(11), LTRIM(RTRIM(ATTRIBUTE3)), 106),
CONVERT(VARCHAR(11), LTRIM(RTRIM(ATTRIBUTE4)), 106),
ATTRIBUTE5,ATTRIBUTE6,
ATTRIBUTE7,1,
TRN,SESSION_ID,
@PVC_LOGIN_USER,GETDATE(),
@PVC_LOGIN_USER,GETDATE()
FROM TAB_TEMP WHERE ACTION_FLAG='I' AND
SESSION_ID=@PBI_SESSION_ID AND TRN=@PBI_TRN
UPDATE pub_lines SET
PUB_ID = tempTable.ATTRIBUTE1,
PD_NAME = tempTable.ATTRIBUTE2, ,
FROM_DATE = CONVERT(VARCHAR(11),LTRIM(RTRIM(tempTable.ATTRIBUTE3)), 106),
TO_DATE = CONVERT(VARCHAR(11),LTRIM(RTRIM(tempTable.ATTRIBUTE4)), 106),
QT_NUM = tempTable.ATTRIBUTE5,
STRD_YEAR = tempTable.ATTRIBUTE6,
ADVANCE_PID = tempTable.ATTRIBUTE7,
TRN = @PBI_TRN,
STATUS = 1,
SESSION_ID = @PBI_SESSION_ID
FROM SA_HR_LINES pub_lines
INNER JOIN TAB_TEMP tempTable
ON tempTable.ACTUAL_LINE_ID =pub_lines.PV_LINE_ID
WHERE tempTable.ACTION_FLAG = 'U'
AND tempTable.SESSION_ID = @PBI_SESSION_ID
AND tempTable.TRN = @PBI_TRN
AND tempTable.ATTRIBUTE1 = @PVC_PUB_ID
DELETE pub_lines
FROM SA_HR_LINES pub_lines
INNER JOIN TAB_TEMP tempTable
ON tempTable.ACTUAL_LINE_ID =pub_lines.PV_LINE_ID
WHERE tempTable.ACTION_FLAG = 'D'
AND tempTable.SESSION_ID = @PBI_SESSION_ID
AND tempTable.TRN=@PBI_TRN
AND pub_lines.PUB_ID=tempTable.ATTRIBUTE1
DELETE FROM TAB_TEMP WHERE SESSION_ID=@PBI_SESSION_ID AND TRN=@PBI_TRN
Thanks Grant n Sartori......
1. The identity column is mandatory in my case to find the rows uniquely.
2. This table is used in most scenario(almost every edit scenario).
for ex.. user edit 10 records in a transaction screen.. i keep the edited values in this table and when he press the SAVE button. records will be updated/inserted into the respective actual tables..
Above given code is a peice from save procedure where i insert ,update and delete and finally delete the records from the tab_temp table...
3. once transaction complete(i mean data moved to respective table) i delete the entries. so the logic is there is no transaction ,no data in this table.
so if i create clustered index/non clustered index, these deletion and insertion will impact performance hugely. am i right?
4. if i have identity column in this where condition would that be enough?. or should i give session value too?...
if i give more condition in where clause would it speed up the performance or slow down?
thanks a lot for ur valuble reply and time
March 14, 2011 at 11:15 am
I'll add that if you're doing DELETEs rather than TRUNCATE as part of the data move, the table could end up having a fair bit of "white space" in it over time. Without a clustered index, you can't "rebuild" the table by rebuilding the clustered index and you could be wasting a fair bit of disk space over time. You could avoid most of that just by using TRUNCATE if there's no data to be left in the table at the end of the run.
--Jeff Moden
March 14, 2011 at 1:00 pm
thanks jeff.... but some other transaction may use this table..so i cant use truncate cmd...
March 15, 2011 at 12:41 pm
BeginnerBug,
As Jeff pointed out, with no clustered index you will end up with a lot of white space in the table with forward pointers. This in itself is a burden for SQL Server when traversing the table looking for the records you want.
Since you're doing quite a bit of inserting and then deleting, I would use the Identity column as the clustered index. It doesn't have to be the primary key, which could be a natural key. (There could be candidates for other non-clustered indexes as well.) The ever increasing Identity will insert all new records at the end of the table.
This should keep the table more compact and faster to scan, if the optimizer figured that it had to do a table scan. Other indexes would, of course, help prevent table scans if selective enough. However, with a small amount of data that is very temporary a table scan of a compact table may be the best way to access the data. You would have to try out a few indexes and see what happens.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply