Query performance n Index needed?

Question

Query performance n Index needed?

BeginnerBug

Hall of Fame

Points: 3539
More actions
March 14, 2011 at 3:08 am

#389038

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....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 2

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 3

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

WHEREtempTable.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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BeginnerBug Hall of Fame Points: 3539 More actions · Answer 5

thanks jeff.... but some other transaction may use this table..so i cant use truncate cmd...

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

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